Find & loop in VBA
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" wrote:
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
'====================
---
Regards,
Norman
"Noemi" wrote in message
...
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
|