Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace | Excel Worksheet Functions | |||
Do Loop | Excel Discussion (Misc queries) | |||
Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |