Look for chars '*' or '#' in column range - excel vba

[Nicha]

I need to build a procedure in vba that looks in a column range - (example $B$2:$B$8) - and check if any of those cells contain the chars '*' OR '#', in order to get the Row where the first match/partial match happens.
I've adapted a code presented on this site by Rick Rothstein, where the purpose was to check for the values {2,3,4,5,6,7,8,9,10}, using

cells(Evaluate(Replace("MIN(IF( @={2,3,4,5,6,7,8,9,10},ROW(@)))", "@", Addr)), Found6.Column)

.

Suppose we have data in range [B2:B8] under the header "Total". I've adapted the Rick's Solution like this:

Sub FindFirstOcurr_ValueIn_Rng()
'Find First Occurrence of Value in a Column

  Dim LastRow As Long, Found6 As Range, FirstNumber As Range, Addr As String
  Dim DataRow As Integer
  
  'Look for column "Total", where values 2 to 10 are to be found
  Set Found6 = cells.Find("Total", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  
  If Found6 Is Nothing Then GoTo Skiddle
  
  LastRow = cells(Rows.Count, Found6.Column).End(xlUp).row
  Addr = cells(2, Found6.Column).Resize(LastRow - 1).Address
  
  'Devolve a 1ª linha
  'Set FirstNumber = cells(Evaluate(Replace("MIN(IF( @={2,3,4,5,6,7,8,9,10},ROW(@)))", "@", Addr)), Found6.Column)
  
  DataRow = Switch(IsNumeric(Evaluate("MATCH(""~*""," & Addr & ",0)")), Evaluate("MATCH(""~*""," & Addr & ",0)"), True, 9999)
     
  
Skiddle:
End Sub

The code below just works on the value in cell [B5]. In my case it's correct because is the first ocurrence. But, if the value on [B5] was '24*', that didn't work.
Can anyone help please?

 

[Fluff]

This line If Not rng Is Nothing Then is redundant as if there are no text values, or the range contains formulae, the code will crash on the previous line.

 

[Fluff]

Many thank's Fluff. That is perfect solution.

You're welcome & thanks for the feedback.

 

[Nicha]

If, as per your comments, the 1st text-value would equal the 1st occurence of one of these characters, then maybe try:

Sub Test()

Dim DataRow As Long
Dim rng As Range

Set rng = Range("B2:B8").SpecialCells(xlCellTypeConstants, xlTextValues) 'Long syntax for .SpecialCells(2, 2)
If Not rng Is Nothing Then
    DataRow = rng.Row - 1
End If

End Sub

Hi, JvdV. Thank you too. Your code works well when exists text values. But if the range only contains Numeric values, that give a Run-Time error 1004 - No cells where found.
Thank you very much.

 

[JvdV]

Yeah, I suppose you want to now account for the fact no hit may occur. So what would you want to return if no single hit occurs? Since 'DataRow' appears to be an integer, maybe:

With Application
    DataRow .IfError(.Evaluate(Replace("=MATCH(TRUE,((LEN(@)>3)+ISTEXT(@))>0,0)", "@", Addr)), 0)
End With

You'll get an 0 if no hit was found.

 

[Nicha]

Yeah, I suppose you want to now account for the fact no hit may occur. So what would you want to return if no single hit occurs? Since 'DataRow' appears to be an integer, maybe:

With Application
    DataRow .IfError(.Evaluate(Replace("=MATCH(TRUE,((LEN(@)>3)+ISTEXT(@))>0,0)", "@", Addr)), 0)
End With

You'll get an 0 if no hit was found.

Thank you JvdV.