Find & loop in VBA

[Noemi]

Hi

I need a code which allows me to locate a word (which is repeated numerous
time which I am not aware how many) and once the word is found I need it to
perform a task and once the task has been performed I need it to find the
next word (same word as before).

Any ideas as to how to accomplish this.

I know how to do the loop side of it but I am having problems with the find
side.

My code below need to find the word "Test No:" which once found needs to
copy the text 2 cells to the right and paste that text into the cell next to
the word "Test No:"

I receive the following error for the code below (Object variable or with
block variable not set), it is refering to the Cells.Find code

Do

Cells.Find(What:="Test No:", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 2).Copy
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False


Loop Until ActiveCell.Value <> "" (this code is refering to the cell
next to the word "Test No:", when not empty it needs to stop looping)

Any help would be great

Thanks
Noemi

 

[Norman Jones]

Hi Noemi,

From VBA help on the find method:

'====================
Find Method Example

This example finds all cells in the range A1:A500 on worksheet one that
contain the value 2, and then it makes those cells gray.

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

 

[Noemi]

Hi Norman

Sorry but that code is no good for me as I have spaces and other information
prior to the next word.

It runs across and not down and still doesn't look for the next word.

Still require help please

Thanks
Noemi

 

[Norman Jones]

Hi Noemi,

Try this adaptation:

'=============>>
Public Sub Tester2()
Dim c As Range
Dim firstaddress As String
Const sStr As String = "Test No:"

With ActiveSheet.Cells
Set c = .Find(What:=sStr, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Offset(0, 1).Value = c.Offset(0, 2).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

End Sub
'<<=============