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]

Hi & welcome to MrExcel.
How about

DataRow = Evaluate(Replace("min(if(isnumber(find({""*"",""#""},@)),row(@),9999))", "@", Addr))

 

[Nicha]

Hi & welcome to MrExcel.
How about

DataRow = Evaluate(Replace("min(if(isnumber(find({""*"",""#""},@)),row(@),9999))", "@", Addr))

Thank you Fluff. Coul you help me understand how it works, please? That is returning the number 4. Is this a relative position? Because, in that range the first ocurrence is a '#' on line 4, that corresponds at relative line 3 in the table.
Can you help please?

 

[Fluff]

That is returning the number 4. Is this a relative position?

No it's the number of the row the match is in, if you want a relative row to the range use

DataRow = Evaluate(Replace("min(if(isnumber(find({""*"",""#""},@)),row(@)-min(row(@))+1,9999))", "@", Addr))

 

[Nicha]

Thank you Fluff. Coul you help me understand how it works, please? That is returning the number 4. Is this a relative position? Because, in that range the first ocurrence is a '#' on line 4, that corresponds at relative line 3 in the table.
Can you help please?

Brilliant!!. I've moved the range to [$B$12:$B$18], and got the value 14. That corresponds to the value '6#5' on that line. Many Thanks.
One last question; how can I alter your code to check if theres only numeric values in that range?
Thank's in advance.

 

[Fluff]

Do you mean if all cells in the range are numbers?

 

[Nicha]

Do you mean if all cells in the range are numbers?

No. I need to get the first ocurrence in range where the cell value is not numeric. It's practically the same result. Taking again the range [B2:B8], that shoul return the Row 4; caused by '6#5' that is not a numéric value.
Thank you again.

 

[Fluff]

Ok, how about

DataRow = Evaluate(Replace("min(if(istext(@),row(@)-min(row(@))+1,9999))", "@", Addr))

 

[JvdV]

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

 

[Nicha]

Many thank's Fluff. That is perfect solution.
The values in my column only can contain 3 numéric values. I'll try to find a way, using your code, to find first ocurrence of a value that doesn't contain 3 digits only - like '6204, 3#1, #, *, etc..'
Many thanks