Find non-numeric value in VBA Excel
I am using VBA in Excel to try and write some macros, however I am very new to the process.
I am currently trying to use a for loop to find a column for a non-numeric value. For this I wrote the following:
rwcnt = WorksheetFunction.CountA(Range("A:A"))
Dim i As Integer
For i = 1 To rwcnt
If Cells(i, 1).Value = Not IsNumeric Then
Cells(i, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Exit For
End If
This returns an error saying the argument is not optional and escapes IsNumeric.
I would like to search column A and select the first cell that has non-numeric characters in it outside of my headers. Plus, it's a search through> 100K cells, so if there is a less intense way to do this, the suggestions will be nice too.
Any help would be appreciated and again, I don't know much about this, so if things go wrong, feel free to talk about it.
source to share
The below code should work fine, please note how I used IsNumeric
Sub t()
rwcnt = WorksheetFunction.CountA(Range("A:A"))
Dim i As Integer
For i = 1 To rwcnt
If Not (IsNumeric(Cells(i, 1).Value)) Then
range(Cells(i, 1).address, Cells(i, 1).End(xlDown).address).Select
Exit For
End If
Next
End Sub
Also, you don't need them all to choose, the above achieves the same result
source to share
IsNumeric()
is a function to test an expression, so it should be used like this:
rwcnt = WorksheetFunction.CountA(Range("A:A"))
Dim i As Integer
For i = 1 To rwcnt
If IsNumeric(Cells(i, 1).Value) <> True Then
Cells(i, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Exit For
End If
Next i
When in the VBA editor press F2 to view the Object Explorer to get information about the functions and press F1 to open help about that particular function!;)
source to share