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

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?

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

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

--
Gary''s Student - gsnu200849


"chemicals" wrote:

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?

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

I'll give it a shot..thanks

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200849


"chemicals" wrote:

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?

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

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

--
Rick (MVP - Excel)


"chemicals" wrote in message
...
I'll give it a shot..thanks

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200849


"chemicals" wrote:

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?