VBA to find # of cells with a digit in cell string

[cmancu]

xl2000
Been trying for hours - close but can't get it. Weekend VBA'er at best. Help?

I need to count only the # of rows on my sheet in which col I contains a digit within a string of alpha numeric data entered in each cell in "I". Number of total populated rows varies up to about 100.

Column "I" raw data on a typical day: (code snippet below)
2
1
1
1HP
1PC
2Q
4
4C
O
2Z
1J
A
2
4X
1
L
1
3K
3JZ
3
4

Sheet will be sorted by Column "I" PRIOR TO running my code, so I was trying to use a variable to step up through "I" and return the row # of the first cell found with a digit in the string. This number will then be used in further steps of the routine.

My code snippet so far:

    Dim x As Integer
    LR = Sheet1.Cells(Rows.Count, 9).End(xlUp).Row
    
        For c = LR To 1 Step -1
        If Range("I" & c) Like "*#*" Then
        x = Range("I" & c).Row
        End If
        Next c

I'm not sure if the syntax using Like is correct (I'll use anything suggested) and I think my loop is wrong/used incorrectly.

Thanks if any care to help.

Chris

 

[MrKowz]

Will the number always occur BEFORE the first alphabetic character?

 

[cmancu]

Yes, if there is a number present in the string, it will always be first position.

 

[MrKowz]

Try out this function. I used it on the example provided and it returned 18 (the desired number).

Public Function CountNumeric(ByRef sRng As Range) As Long
Dim rng As Range
    
For Each rng In sRng
    If Len(rng.Value) > 0 Then
        If IsNumeric(Left(rng.Value, 1)) Then
            CountNumeric = CountNumeric + 1
        End If
    End If
Next rng
End Function

 

[cmancu]

Great, thank you MrKowz, I will incorporate your code in my routine this eve and try it. I'm sure it will work.

It looks as though now I won't have to worry about sorting, which is a plus!

Thanks again.