Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext problem when using PasteSpecial
Hi All,
I have a problem with FindNext. In brief, I want to search through a column until I find some text with the word "Total" in. I then want to copy some data to a new workbook, save that book and then carry on and search for the next instance of the word "total" and repeat. I can get the first part to work but then something happens when I want to find the next instance of "total". Here is part of the code. ' Search through column B for total With ActiveSheet.Range("B:B") ' Look in part as part of total row Set FindBA = .Find(What:="Total", LookIn:=xlValues, LookAt:=xlPart) If Not FindBA Is Nothing Then 'Call sub routine that copies & pastes to new sheet 'Find next item Do Set FindBA = .FindNext(FindBA) 'Call sub routine that copies & pastes to new sheet Loop While Not FindBA.Value = "Grand Total" End If End With Stepping through the routine, I can see that when I call the first sub routine, my object FINDBA changes it's value immediately after the paste command in the sub routine. This means that when it comes back to my main routine it is no looking for "Total". When I tested this routine using a message box, it worked OK. It seems to be the Paste command that causes the problem. There are around 6000 rows of data in this column and about 70+ instances of the word Total so I do not want to loop through every single cell which would be my alternative to using FindNext. Any help would be much appreciated. Regards Glyn Baker |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindNext problem when using PasteSpecial
The only thing I see that would change FindBA would be if your copy routine
changes FindBA or you delete the copied rows on the source sheet in your copy routine. If you are deleting the copied rows, then you could have findnext as follows: set FindBA = .findNext(.cells(1,1)) If you are changing it in the copy routine, then don't. anyway, this worked fine for me Sub Tester5() Dim findBa As Range Dim fAddr As String ' Search through column B for total With ActiveSheet.Range("B:B") ' Look in part as part of total row Set findBa = .Find(What:="Total", _ LookIn:=xlValues, LookAt:=xlPart) If Not findBa Is Nothing Then fAddr = findBa.Address Do copyData findBa Set findBa = .FindNext(findBa) Loop While Not findBa.Address = fAddr And _ findBa.Value < "Grand Total" End If End With End Sub Sub copyData(rng As Range) rng.EntireRow.Copy Destination:=Worksheets("sheet1"). _ Cells(Rows.Count, 1).End(xlUp)(2) End Sub -- Regards, Tom Ogilvy Glyn Baker wrote in message om... Hi All, I have a problem with FindNext. In brief, I want to search through a column until I find some text with the word "Total" in. I then want to copy some data to a new workbook, save that book and then carry on and search for the next instance of the word "total" and repeat. I can get the first part to work but then something happens when I want to find the next instance of "total". Here is part of the code. ' Search through column B for total With ActiveSheet.Range("B:B") ' Look in part as part of total row Set FindBA = .Find(What:="Total", LookIn:=xlValues, LookAt:=xlPart) If Not FindBA Is Nothing Then 'Call sub routine that copies & pastes to new sheet 'Find next item Do Set FindBA = .FindNext(FindBA) 'Call sub routine that copies & pastes to new sheet Loop While Not FindBA.Value = "Grand Total" End If End With Stepping through the routine, I can see that when I call the first sub routine, my object FINDBA changes it's value immediately after the paste command in the sub routine. This means that when it comes back to my main routine it is no looking for "Total". When I tested this routine using a message box, it worked OK. It seems to be the Paste command that causes the problem. There are around 6000 rows of data in this column and about 70+ instances of the word Total so I do not want to loop through every single cell which would be my alternative to using FindNext. Any help would be much appreciated. Regards Glyn Baker |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Findnext | Excel Discussion (Misc queries) | |||
vba pastespecial | Excel Programming | |||
PasteSpecial problem | Excel Programming | |||
PasteSpecial problem | Excel Programming | |||
problem with .FindNext | Excel Programming |