FindFirst

[kirkm]

Is there a quick way to find the first instance of something in a cell
- rather than move through line by line?

Thanks - Kirk

 

[NickHK]

Kirk,
Check out the Find method in the Help.

NickHK

 

[Bob Phillips]

Straight from help

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[kirkm]

Kirk,
Check out the Find method in the Help.

NickHK

Yeah.... I tried that. I seem to have just the Office Assistant which
told me to click on Find.... not much use as I want to use VB.

 

[kirkm]

Straight from help

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Thanks Bob... amazing stuff! Means absolutely nothing to me!
My 'Help' seems to be missing..... I'll keep Googling

Cheers - Kirk

 

[NickHK]

You need the help in the VBA IDE, not Excel help.

Press Alt+F11, then F1.
VBA Help is an option during Office installation, so if it is not available
you will need add it.

NickHK

 

[Bob Phillips]

It is quite straightforward

A1:A500 is the range being looked at

2 is the value being looked for

If Not c Is Nothing means that something was found.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)