software

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 quot;Test No:quot; which once found needs to
copy the text 2 cells to the right and paste that text into the cell next to
the word quot;Test No:quot;

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:=quot;Test No:quot;, 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 = FalseLoop Until ActiveCell.Value lt;gt; quot;quot; (this code is refering to the cell
next to the word quot;Test No:quot;, when not empty it needs to stop looping)

Any help would be great

Thanks
NoemiHi 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(quot;a1:a500quot;)
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 lt;gt; firstAddress
End If
End With
'====================

---
Regards,
Norman
quot;Noemiquot; gt; wrote in message
...
gt; Hi
gt;
gt; I need a code which allows me to locate a word (which is repeated numerous
gt; time which I am not aware how many) and once the word is found I need it
gt; to
gt; perform a task and once the task has been performed I need it to find the
gt; next word (same word as before).
gt;
gt; Any ideas as to how to accomplish this.
gt;
gt; I know how to do the loop side of it but I am having problems with the
gt; find
gt; side.
gt;
gt; My code below need to find the word quot;Test No:quot; which once found needs to
gt; copy the text 2 cells to the right and paste that text into the cell next
gt; to
gt; the word quot;Test No:quot;
gt;
gt; I receive the following error for the code below (Object variable or with
gt; block variable not set), it is refering to the Cells.Find code
gt;
gt; Do
gt;
gt; Cells.Find(What:=quot;Test No:quot;, After:=ActiveCell, LookIn:=xlFormulas,
gt; _
gt; LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
gt; MatchCase:=False).Activate
gt; ActiveCell.Offset(0, 2).Copy
gt; ActiveCell.Offset(0, 1).Select
gt; ActiveSheet.Paste
gt; Application.CutCopyMode = False
gt;
gt;
gt; Loop Until ActiveCell.Value lt;gt; quot;quot; (this code is refering to the cell
gt; next to the word quot;Test No:quot;, when not empty it needs to stop looping)
gt;
gt; Any help would be great
gt;
gt; Thanks
gt; Noemi
gt;
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

quot;Norman Jonesquot; wrote:

gt; Hi Noemi,
gt;
gt; From VBA help on the find method:
gt;
gt; '====================
gt; Find Method Example
gt;
gt; This example finds all cells in the range A1:A500 on worksheet one that
gt; contain the value 2, and then it makes those cells gray.
gt;
gt; With Worksheets(1).Range(quot;a1:a500quot;)
gt; Set c = .Find(2, lookin:=xlValues)
gt; If Not c Is Nothing Then
gt; firstAddress = c.Address
gt; Do
gt; c.Interior.Pattern = xlPatternGray50
gt; Set c = .FindNext(c)
gt; Loop While Not c Is Nothing And c.Address lt;gt; firstAddress
gt; End If
gt; End With
gt; '====================
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt;
gt; quot;Noemiquot; gt; wrote in message
gt; ...
gt; gt; Hi
gt; gt;
gt; gt; I need a code which allows me to locate a word (which is repeated numerous
gt; gt; time which I am not aware how many) and once the word is found I need it
gt; gt; to
gt; gt; perform a task and once the task has been performed I need it to find the
gt; gt; next word (same word as before).
gt; gt;
gt; gt; Any ideas as to how to accomplish this.
gt; gt;
gt; gt; I know how to do the loop side of it but I am having problems with the
gt; gt; find
gt; gt; side.
gt; gt;
gt; gt; My code below need to find the word quot;Test No:quot; which once found needs to
gt; gt; copy the text 2 cells to the right and paste that text into the cell next
gt; gt; to
gt; gt; the word quot;Test No:quot;
gt; gt;
gt; gt; I receive the following error for the code below (Object variable or with
gt; gt; block variable not set), it is refering to the Cells.Find code
gt; gt;
gt; gt; Do
gt; gt;
gt; gt; Cells.Find(What:=quot;Test No:quot;, After:=ActiveCell, LookIn:=xlFormulas,
gt; gt; _
gt; gt; LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
gt; gt; MatchCase:=False).Activate
gt; gt; ActiveCell.Offset(0, 2).Copy
gt; gt; ActiveCell.Offset(0, 1).Select
gt; gt; ActiveSheet.Paste
gt; gt; Application.CutCopyMode = False
gt; gt;
gt; gt;
gt; gt; Loop Until ActiveCell.Value lt;gt; quot;quot; (this code is refering to the cell
gt; gt; next to the word quot;Test No:quot;, when not empty it needs to stop looping)
gt; gt;
gt; gt; Any help would be great
gt; gt;
gt; gt; Thanks
gt; gt; Noemi
gt; gt;
gt;
gt;
gt;

Hi Noemi,

Try this adaptation:

'=============gt;gt;
Public Sub Tester2()
Dim c As Range
Dim firstaddress As String
Const sStr As String = quot;Test No:quot;

With ActiveSheet.Cells
Set c = .Find(What:=sStr, _
After:=Range(quot;A1quot;), _
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 lt;gt; firstaddress
End If
End With

End Sub
'lt;lt;=============

---
Regards,
Normanquot;Noemiquot; gt; wrote in message
...
gt; Hi Norman
gt;
gt; Sorry but that code is no good for me as I have spaces and other
gt; information
gt; prior to the next word.
gt;
gt; It runs across and not down and still doesn't look for the next word.
gt;
gt; Still require help please
gt;
gt; Thanks
gt; Noemi
gt;
gt; quot;Norman Jonesquot; wrote:
gt;
gt;gt; Hi Noemi,
gt;gt;
gt;gt; From VBA help on the find method:
gt;gt;
gt;gt; '====================
gt;gt; Find Method Example
gt;gt;
gt;gt; This example finds all cells in the range A1:A500 on worksheet one that
gt;gt; contain the value 2, and then it makes those cells gray.
gt;gt;
gt;gt; With Worksheets(1).Range(quot;a1:a500quot;)
gt;gt; Set c = .Find(2, lookin:=xlValues)
gt;gt; If Not c Is Nothing Then
gt;gt; firstAddress = c.Address
gt;gt; Do
gt;gt; c.Interior.Pattern = xlPatternGray50
gt;gt; Set c = .FindNext(c)
gt;gt; Loop While Not c Is Nothing And c.Address lt;gt; firstAddress
gt;gt; End If
gt;gt; End With
gt;gt; '====================
gt;gt;
gt;gt; ---
gt;gt; Regards,
gt;gt; Norman
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Noemiquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi
gt;gt; gt;
gt;gt; gt; I need a code which allows me to locate a word (which is repeated
gt;gt; gt; numerous
gt;gt; gt; time which I am not aware how many) and once the word is found I need
gt;gt; gt; it
gt;gt; gt; to
gt;gt; gt; perform a task and once the task has been performed I need it to find
gt;gt; gt; the
gt;gt; gt; next word (same word as before).
gt;gt; gt;
gt;gt; gt; Any ideas as to how to accomplish this.
gt;gt; gt;
gt;gt; gt; I know how to do the loop side of it but I am having problems with the
gt;gt; gt; find
gt;gt; gt; side.
gt;gt; gt;
gt;gt; gt; My code below need to find the word quot;Test No:quot; which once found needs
gt;gt; gt; to
gt;gt; gt; copy the text 2 cells to the right and paste that text into the cell
gt;gt; gt; next
gt;gt; gt; to
gt;gt; gt; the word quot;Test No:quot;
gt;gt; gt;
gt;gt; gt; I receive the following error for the code below (Object variable or
gt;gt; gt; with
gt;gt; gt; block variable not set), it is refering to the Cells.Find code
gt;gt; gt;
gt;gt; gt; Do
gt;gt; gt;
gt;gt; gt; Cells.Find(What:=quot;Test No:quot;, After:=ActiveCell,
gt;gt; gt; LookIn:=xlFormulas,
gt;gt; gt; _
gt;gt; gt; LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
gt;gt; gt; _
gt;gt; gt; MatchCase:=False).Activate
gt;gt; gt; ActiveCell.Offset(0, 2).Copy
gt;gt; gt; ActiveCell.Offset(0, 1).Select
gt;gt; gt; ActiveSheet.Paste
gt;gt; gt; Application.CutCopyMode = False
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; Loop Until ActiveCell.Value lt;gt; quot;quot; (this code is refering to the
gt;gt; gt; cell
gt;gt; gt; next to the word quot;Test No:quot;, when not empty it needs to stop looping)
gt;gt; gt;
gt;gt; gt; Any help would be great
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt; gt; Noemi
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;