Home |
Search |
Today's Posts |
#1
|
|||
|
|||
DoUntil VB command
Hi there,
I have recorded a macro that does a search and then copies data from one cell to another. I want it to loop until it finds the last occurrance of the search parameter. I understand that there is a VB command "DoUntil"? What would I put after this please? How do I make it loop? This is my macro: Sheets("Book1").Select Application.Goto Reference:="R1C1" Cells.Find(What:="sub", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(-2, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-2, 6).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(0, -6).Range("A1:G1").Select ActiveCell.Activate Selection.Copy Sheets("Book1NEW").Select Application.Goto Reference:="R60000C1" Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(0, 4).Range("A1:C1").Select Application.CutCopyMode = False Selection.Cut Destination:=ActiveCell.Offset(0, -3).Range("A1:C1") ActiveCell.Offset(0, -3).Range("A1:C1").Select Sheets("Book1").Select End Sub Thanks in advance, Martin |
#2
|
|||
|
|||
DoUntil VB command
One way:
Public Sub CopyStuff() Dim rFound As Range Dim rDest As Range Dim sFirstAddress As String Set rDest = Sheets("Book1NEW").Cells( _ Rows.Count, 1).End(xlUp).Offset(1, 0) With Sheets("Book1") Set rFound = .Cells.Find( _ What:="sub", _ After:=.Cells(1, 1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rFound Is Nothing Then sFirstAddress = rFound.Address Do With rFound If .Row 2 Then .Offset(-2, 0).Copy Destination:=.Cells .Offset(-2, 6).Copy Destination:= _ .Offset(0, 6) .Copy Destination:=rDest .Offset(0, 4).Resize(1, 3).Copy _ Destination:=rDest.Offset(0, 1) Set rDest = rDest.Offset(1, 0) End If End With Set rFound = .Cells.FindNext(after:=rFound) Loop Until rFound Is Nothing End If End With End Sub In article , "Martin" wrote: Hi there, I have recorded a macro that does a search and then copies data from one cell to another. I want it to loop until it finds the last occurrance of the search parameter. I understand that there is a VB command "DoUntil"? What would I put after this please? How do I make it loop? This is my macro: Sheets("Book1").Select Application.Goto Reference:="R1C1" Cells.Find(What:="sub", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(-2, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-2, 6).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(0, -6).Range("A1:G1").Select ActiveCell.Activate Selection.Copy Sheets("Book1NEW").Select Application.Goto Reference:="R60000C1" Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(0, 4).Range("A1:C1").Select Application.CutCopyMode = False Selection.Cut Destination:=ActiveCell.Offset(0, -3).Range("A1:C1") ActiveCell.Offset(0, -3).Range("A1:C1").Select Sheets("Book1").Select End Sub Thanks in advance, Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Command conditional formating | Excel Worksheet Functions | |||
Excel 2003 - Toolbar missing the file command | Excel Discussion (Misc queries) | |||
Extract Command in Excel 1 | Excel Discussion (Misc queries) | |||
Help requested for an Excel Toolbar command | Excel Discussion (Misc queries) | |||
command button in excel will move when print. | Excel Discussion (Misc queries) |