find function in VBA for excel

[ratt2581]

I can't seem to wrap my head around the find function in vba - i keep getting errors left and right

What I'm trying to do is cycle through a few rows and if column 5 contains a certain word I would like to highlight that cell otherwise leave it alone

Basically (I know what is below is incorrect but i figured it might give a more basic idea of what I'm hoping to accomplish):

 If .Find("searched", column 5) Then
         ActiveCell.Offset(0, 5).Interior.ColorIndex = 6
         End If

my problem seems to lay somewhere in how I'm using the find function. I've tried messing around with the what:=, where:=, etc's but I cant figure out where I'm losing it and was hoping someone out there could give me a brief explination of the find function for my purpose since i can't seem to tailor any examples i find on the web to work and the help feature also doesn't seem to click in my brain.

Thanks

 

[Norie]

Couldn't you just use Format>Conditional formatting...?

 

[Smitty]

Or take a look at the Find Method example in the helpfile. It deals with using find to loop through a range of cells.

Smitty

 

[ratt2581]

norei: thanks - i thought about conditional formats but the cell isn't always going to be in column 5 - thats why i was trying to use the offset

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=FIND(""timestamp"",L1)"

Does the conditional format allow for relative referencing?

Pennysaver: i tried the help feature (my last paragraph) but for its not clicking in my head - i can't figure out where I'm going wrong

Thanks

 

[ratt2581]

doh - u can see where I tried to bold the L1 where I would like to use something like ActiveCell.Offset(0, variable predetermined which column I wnat to work in)

 

[Norie]

ratt2581

Does the conditional format allow for relative referencing?

Really depends on what you are actually trying to do.

 

[ratt2581]

The way i have the code set up now is to run a for next statement that cycles through the rows. During each row it runs a bunch of different if then and other junk. I'm running into an error with my find statement and notice the two columns I'm trying to check won't always be the same when the macro is run (hence why I defined them as variables BannerImpCk and BannerClkCk below):

For BrowserCheck = 1 To CAFLastRow
        bannerimpck = ActiveCell.Offset(0, BannerImp - 2).Value
        bannerClkCk = ActiveCell.Offset(0, BannerClk - 2).Value

        
       'Looking for timestamp
        '***Here is where I want to find any cells in the bannerimpck or bannerClkCk column that contain "timestamp" and highlight them out
    
   '***What I want***
       if The Cell Value of bannerimpck contains "timestamp" Then
                 highlight this cell
       (if find("timestamp", bannerimpchk) <> 0 Then Highlight)

       if The Cell Value of bannerclkck contains "timestamp" Then
                 highlight this cell
      
       ' the code then goes into a bunch of other if thens and other junk but but at the end you have:

    Range("A" & BrowserCheck + 1).Select
    ' I did that so i move to the next row in my spreadsheet
 Next

Not sure if this makes more or less sense now

 

[Smitty]

You're making things much harder than they need to be with the selecting.

You also haven't shown what the actual range is anywhere.

Here's a start with the find method:

<font face=Courier New><SPAN style="color:#007F00">'   Define the range to work within</SPAN>
<SPAN style="color:#00007F">With</SPAN> Worksheets(1).Range("a1:a500")
    <SPAN style="color:#007F00">'   What to look for</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> c = .Find("timestamp", LookIn:=xlValues)
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        firstAddress = c.Address
        <SPAN style="color:#00007F">Do</SPAN>
                <SPAN style="color:#007F00">'   Add your test condition here</SPAN>
                <SPAN style="color:#00007F">If</SPAN> c.Offset(, 2) = 0 <SPAN style="color:#00007F">Then</SPAN> c.Offset(, 2).Interior.ColorIndex = 3
            <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)
        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> firstAddress
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
</FONT>

Smitty

 

[ratt2581]

Totally right about doing excess with the selections - using what you provided and tweaking it to use cells() instead of a range() I got exactly what I needed

With Cells(1, BannerImpCol).Resize(CAFLastRow, (ClickRedirectCol - BannerImpCol + 1))
    Set c = .Find("timestamp", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.ColorIndex = 6
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

Thanks for all your help