PDA

View Full Version : Find a word



leal72
07-08-2010, 02:21 PM
Is there a way to replicate the Control F function within VBA?

right now I am using this but it is not very flexible. It looks for the word "Time" in column A, is it possible for it to look in both column A and B?


FindTime = Application.WorksheetFunction.Match("time", RngA, 0)

mdmackillop
07-08-2010, 02:58 PM
Record a macro while doing the search. That will give you the basic code. If you need to modify it further, let us know.

leal72
07-08-2010, 03:01 PM
I have tried that but nothing gets recorded.

mdmackillop
07-08-2010, 03:11 PM
I've not known the recorder to fail with such. Where does it suggest the code is being saved?

Anyway, here is recorded code, and how it might be tidied up


Option Explicit

Sub Macro1()
'
' Macro1 Macro
'
'
Columns("A:B").Select
Selection.Find(What:="Time", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("C25").Select
End Sub


Sub Refined()
Dim Rng as Range
Set Rng = Columns("A:B").Find(What:="Time", LookIn:=xlFormulas, _
LookAt:=xlWhole)
Rng.Interior.ColorIndex = 6
End Sub

leal72
07-12-2010, 08:20 AM
thank you, that worked great.

I've tried on a couple of occassions and nothing was recorded when using Control + F, I just tried it again and it did record. Maybe I goofed on the other occassions, or maybe it was some glitch.