find function in VBA for excel

ratt2581

Board Regular
Joined
Nov 11, 2006
Messages
100
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):

Code:
 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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
Couldn't you just use Format>Conditional formatting...?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Nov 11, 2006
Messages
100
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

Rich (BB code):
    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

Board Regular
Joined
Nov 11, 2006
Messages
100

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
ratt2581
Does the conditional format allow for relative referencing?
Really depends on what you are actually trying to do.:)
 

ratt2581

Board Regular
Joined
Nov 11, 2006
Messages
100

ADVERTISEMENT

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):


Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Nov 11, 2006
Messages
100
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,528
Messages
5,625,337
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top