What is the best way to query a range for a substring?

  • Thread starter Thread starter chemicals
  • Start date Start date
C

chemicals

I can't seem to find a function to find cells with a particular portion of a
string in them (much like a SQL query using LIKE). Is there such a function?

If there is no such function then what would be the most efficient way to do
this?

I need to search a colum for several different values and then move 1 cell
to the right and grab the value there. So fo example:
SAU #1 - test 12
SAU #2 - development 25
SAU #3 - specs 12

So in the example I need the values of 12,25,12 but for the text in the
first column the only known part that will be there is the first part (i.e.
"SAU #1 -") Can Column.Find use a wildcard?
 
Sub kemikals()
Dim s As String, v As String
s = "SAU #1 -"
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, s) > 0 Then
MsgBox (Cells(i, 2).Value)
End If
Next
End Sub
 
I'll give it a shot..thanks

Gary''s Student said:
Sub kemikals()
Dim s As String, v As String
s = "SAU #1 -"
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, s) > 0 Then
MsgBox (Cells(i, 2).Value)
End If
Next
End Sub
Click to expand...
 
And just so you know, VB has a Like operator that allows for wild card
patterns as well (check the help files for Like Operator to see what is
available). Gary''s Student's macro can be rewritten this way to use it...

Sub kemikals()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
If Cells(i, 1).Value Like "SAU #1 -*" Then
MsgBox (Cells(i, 2).Value)
End If
End Sub
 
Back
Top