Find & loop in VBA
 

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

Find & loop in VBA
 

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

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

Find & loop in VBA
 

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
'<<=============

---
Regards,
Norman


"Noemi" wrote in message
...
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