vba to evaluate every cell from A49 to the last row and if there's a number formatted as text error, then popup message and end program

[parkerbelt]

I'm looking for the vba code to evaluate every cell from A49 to the last row and if there's a number formatted as text error, for any of the data, then popup a message that says "Bad Data" and end the program.

 

[Rick Rothstein]

I'm looking for the vba code to evaluate every cell from A49 to the last row and if there's a number formatted as text error, for any of the data, then popup a message that says "Bad Data" and end the program.

What exactly do you mean by what I highlighted in red above? Perhaps if you posted an example or two, that would help.

 

[parkerbelt]

For some of my numbers in column A, I'm getting the little green triangle in the upper left corner and when I run my cursor over it, a box pops up with a yellow exclamation point in it and an error message pops up with it that says "The number in this cell is formatted as text or is preceded by an apostrophe". That becomes a problem when I try to do a vlookup off of that number trying to match it with another number that is in fact formatted as a number.

What exactly do you mean by what I highlighted in red above? Perhaps if you posted an example or two, that would help.

 

[MARK858]

Possibly (untested)....

Sub numastxt()
    Dim myCell As Range
    For Each myCell In Range("A49:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If myCell.Errors.Item(xlNumberAsText).Value = True Then
            MsgBox "Cell" & myCell.Address & " is a Number Stored As Text"
            Exit Sub
        End If
    Next
End Sub

 

[parkerbelt]

That worked. Thank you!!

Possibly (untested)....

Sub numastxt()
    Dim myCell As Range
    For Each myCell In Range("A49:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If myCell.Errors.Item(xlNumberAsText).Value = True Then
            MsgBox "Cell" & myCell.Address & " is a Number Stored As Text"
            Exit Sub
        End If
    Next
End Sub

 

[Rick Rothstein]

What about if instead of flagging cells in those cells you simply have the code fix the cells so the numbers in them are real numbers? If you are up for that, I believe this code will do it for you...

Sub MakeTextNumbersIntoRealNumbers()
  With Range("A49", Cells(Rows.Count, "A").End(xlUp))
    .NumberFormat = "General"
    .TextToColumns , xlDelimited, , , False, False, False, False, False
  End With
End Sub

 

[parkerbelt]

That would work in another situation. In this situation, I just want to know if I'm getting bad data. When the data is good, I'm not getting numbers formatted as text, but when the data comes back bad, some of the cells are formatted as text. When that happens, I want to stop the program from running and go re-pull the data and then start my program again. If I don't check if the data is bad, my program will just run through to the end and I may never know, unless I happen to go back and check every row of data for errors. Thanks for your help Rick!!

What about if instead of flagging cells in those cells you simply have the code fix the cells so the numbers in them are real numbers? If you are up for that, I believe this code will do it for you...

Sub MakeTextNumbersIntoRealNumbers()
  With Range("A49", Cells(Rows.Count, "A").End(xlUp))
    .NumberFormat = "General"
    .TextToColumns , xlDelimited, , , False, False, False, False, False
  End With
End Sub