Search for string containing
 

Hello,

I have the following in VBA:

If .Cells(X, "K") = "RG" Then
.Cells(X, "Q").Value = "TRC"

How can I modify this formula to look in column K for a string that contains
RG and perform the same function? (i.e., if it contains RG-TRVL or TRVL-RG,
then TRC should automatically be placed in column Q)?

Thank you.

Search for string containing
 

On Thu, 14 Jan 2010 12:25:01 -0800, richzip
wrote:

Hello,

I have the following in VBA:

If .Cells(X, "K") = "RG" Then
.Cells(X, "Q").Value = "TRC"

How can I modify this formula to look in column K for a string that contains
RG and perform the same function? (i.e., if it contains RG-TRVL or TRVL-RG,
then TRC should automatically be placed in column Q)?

Thank you.


Use the InStr function. If it returns a non-zero, your substring is contained
in the string.
--ron

Search for string containing
 

Take a look at Like or InStr in VBA's help

if ucase(.cells(x,"K").value) like "*RG*" then
or
if instr(1,.cells(x,"K").value,"RG",vbTextCompare) 0 then

(InStr has a parm that you can specify to ignore case (if you want).)


richzip wrote:

Hello,

I have the following in VBA:

If .Cells(X, "K") = "RG" Then
.Cells(X, "Q").Value = "TRC"

How can I modify this formula to look in column K for a string that contains
RG and perform the same function? (i.e., if it contains RG-TRVL or TRVL-RG,
then TRC should automatically be placed in column Q)?

Thank you.

--

Dave Peterson

Search for string containing
 

The line

If InStr(.Cells(X, "K"), "RG") 0 Then .Cells(X, "Q").Value = "TRC"

should do the trick
--
If this helps, please remember to click yes.


"richzip" wrote:

Hello,

I have the following in VBA:

If .Cells(X, "K") = "RG" Then
.Cells(X, "Q").Value = "TRC"

How can I modify this formula to look in column K for a string that contains
RG and perform the same function? (i.e., if it contains RG-TRVL or TRVL-RG,
then TRC should automatically be placed in column Q)?

Thank you.

Search for string containing
 

Here's part of code that I'd probably set up to do it:

Dim testRange as Range
Dim anyTestCell as Range

Set testRange = ActiveSheet.Range("K1:" & _
ActiveSheet.Range("K" & Rows.Count).End(xlup).Address)
For Each anyTestCell in testRange
If Instr(anyTestCell,"RG")0 Then
Range("Q" & anyTestCell.Row) = "TRC"
End IF
Next ' end anyTestCell loop
.... more code
'good housekeeping:
Set testRange = Nothing
....
End Sub



"richzip" wrote:

Hello,

I have the following in VBA:

If .Cells(X, "K") = "RG" Then
.Cells(X, "Q").Value = "TRC"

How can I modify this formula to look in column K for a string that contains
RG and perform the same function? (i.e., if it contains RG-TRVL or TRVL-RG,
then TRC should automatically be placed in column Q)?

Thank you.

Search for string containing
 

For you exact statement, I think you could rewrite it this way:

If Instr(.Cells(X, "K"),"RG") 0 Then
.Cells(X,"Q").Value = "TRC"
End If

"richzip" wrote:

Hello,

I have the following in VBA:

If .Cells(X, "K") = "RG" Then
.Cells(X, "Q").Value = "TRC"

How can I modify this formula to look in column K for a string that contains
RG and perform the same function? (i.e., if it contains RG-TRVL or TRVL-RG,
then TRC should automatically be placed in column Q)?

Thank you.