| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Loop
Sorry I have to post a new thread. I have not been able to do it the way I've
been told. I dont know VBA or programming. I make macro by recording steps. I have this macro which ends with a loop as below. It works, but I need it to End when there are no more cells with VENDOR ID. It does the step but ends with the dialog box and I have to click end. Grateful for help Do Range("F:F").Find(What:="Vendor ID", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Loop
Did you try all the suggestions at your other thread--or one of your other
threads? Christina wrote: Sorry I have to post a new thread. I have not been able to do it the way I've been told. I dont know VBA or programming. I make macro by recording steps. I have this macro which ends with a loop as below. It works, but I need it to End when there are no more cells with VENDOR ID. It does the step but ends with the dialog box and I have to click end. Grateful for help Do Range("F:F").Find(What:="Vendor ID", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Loop End Sub -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Loop
This should be close...
Dim rngFound As Range Dim strFirst As Range Dim rngToSearch As Range Set rngToSearch = Range("F:F") Set rngFound = rngToSearch.Find(what:="Vendor ID", _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Nothing Found" Else strFirst = rngFound.Address Do With rngFound .ClearContents .Offset(1, 0).Copy Destination:=Range(.Offset(1, 0), _ .Offset(1, 0).End(xlDown)) End With Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirst End If -- HTH... Jim Thomlinson "Christina" wrote: Sorry I have to post a new thread. I have not been able to do it the way I've been told. I dont know VBA or programming. I make macro by recording steps. I have this macro which ends with a loop as below. It works, but I need it to End when there are no more cells with VENDOR ID. It does the step but ends with the dialog box and I have to click end. Grateful for help Do Range("F:F").Find(What:="Vendor ID", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Loop End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Loop
Yes, I tried all the suggestions, including this recent one.
Thanks "Dave Peterson" wrote: Did you try all the suggestions at your other thread--or one of your other threads? Christina wrote: Sorry I have to post a new thread. I have not been able to do it the way I've been told. I dont know VBA or programming. I make macro by recording steps. I have this macro which ends with a loop as below. It works, but I need it to End when there are no more cells with VENDOR ID. It does the step but ends with the dialog box and I have to click end. Grateful for help Do Range("F:F").Find(What:="Vendor ID", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Loop End Sub -- Dave Peterson . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Loop
I never saw a response--good or bad--to my suggestion.
Christina wrote: Yes, I tried all the suggestions, including this recent one. Thanks "Dave Peterson" wrote: Did you try all the suggestions at your other thread--or one of your other threads? Christina wrote: Sorry I have to post a new thread. I have not been able to do it the way I've been told. I dont know VBA or programming. I make macro by recording steps. I have this macro which ends with a loop as below. It works, but I need it to End when there are no more cells with VENDOR ID. It does the step but ends with the dialog box and I have to click end. Grateful for help Do Range("F:F").Find(What:="Vendor ID", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Loop End Sub -- Dave Peterson . -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Loop
Thanks, seema I overlooked that one. It worked perfectly. Thanks a lot.
Regards. Cristina Seawell "Dave Peterson" wrote: I never saw a response--good or bad--to my suggestion. Christina wrote: Yes, I tried all the suggestions, including this recent one. Thanks "Dave Peterson" wrote: Did you try all the suggestions at your other thread--or one of your other threads? Christina wrote: Sorry I have to post a new thread. I have not been able to do it the way I've been told. I dont know VBA or programming. I make macro by recording steps. I have this macro which ends with a loop as below. It works, but I need it to End when there are no more cells with VENDOR ID. It does the step but ends with the dialog box and I have to click end. Grateful for help Do Range("F:F").Find(What:="Vendor ID", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Loop End Sub -- Dave Peterson . -- Dave Peterson . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Loop
You're welcome.
Christina wrote: Thanks, seema I overlooked that one. It worked perfectly. Thanks a lot. Regards. Cristina Seawell "Dave Peterson" wrote: I never saw a response--good or bad--to my suggestion. Christina wrote: Yes, I tried all the suggestions, including this recent one. Thanks "Dave Peterson" wrote: Did you try all the suggestions at your other thread--or one of your other threads? Christina wrote: Sorry I have to post a new thread. I have not been able to do it the way I've been told. I dont know VBA or programming. I make macro by recording steps. I have this macro which ends with a loop as below. It works, but I need it to End when there are no more cells with VENDOR ID. It does the step but ends with the dialog box and I have to click end. Grateful for help Do Range("F:F").Find(What:="Vendor ID", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Loop End Sub -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| loop | Excel Discussion (Misc queries) | |||
| Do loop | Excel Discussion (Misc queries) | |||
| Find loop doesn't loop | Excel Discussion (Misc queries) | |||
| while loop | Excel Worksheet Functions | |||
| DO LOOP in VBA | Excel Discussion (Misc queries) | |||
Linear Mode
