loop
in [General]
|
From: Dave Peterson on 19 Mar 2010 16:16 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
From: Dave Peterson on 19 Mar 2010 16:17 Did you try all the suggestions? Christina wrote: > > PLEASE help. > > I have tried everything, and have not been able to get this to work. I > Would you please send me the codes that I need to replace the ones I have. > The one I have works, does the loop copies all the cells needed, but does not > end. I brings up a dialog box and I have to end it myself. I really would > need to finish this today. > > Thanks > > "Christina" wrote: > > > Please bear with me. I am so basic. Would you please make adjustments to this. > > 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 > > > > > > > > Thanks > > "L. Howard Kittle" wrote: > > > > > At the very end of the code you will need to enter this required item: > > > > > > End Sub > > > > > > Enter it just below the last red line of code. So you will have something > > > like this > > > > > > > > > Sub MyMacro() > > > > > > 'all the code you want including Mike H's. > > > > > > End Sub > > > > > > If you have say only two consecutive lines of code that are red after a copy > > > and paste, then it means the code line has wrapped and you need to have a > > > line break OR put all that line of code on a single line. > > > > > > To fix a red line wrap, go to the end of the upper red line of code and from > > > at very end do a SPACE and then an _ (underscore) then arrow up or down off > > > the line. Usually that will correct it but sometimes the line of code will > > > wrap at a place that will NOT accept a line break. If that happens best to > > > just put it all in one line and live with it being way off the screen, OR > > > you can put it all on one line and try different parts of the line to put > > > the space and underscore and hit enter. If you pick a proper place then the > > > code will compile and all the red will go away. You may have to try a few > > > different spots, when it does not compile go to the end of the upper red > > > line and hit delete to bring the bottom line back in place. (May have to > > > hit delete several times to get rid of the large space created when it > > > wrapped. > > > > > > HTH > > > Regards, > > > Howard > > > > > > "Christina" <Christina(a)discussions.microsoft.com> wrote in message > > > news:5A3C8C0C-DF20-4066-920B-DEE4637B0B9E(a)microsoft.com... > > > >I need help with a loop. After looping through the procedure, when the > > > > active cell is empty I want to exit the loop. Would you please give me > > > > the > > > > codes to do that. > > > > > > > > > > > > Thanks > > > > > > > > > > > > Cristina > > > > > > > > > . > > > -- Dave Peterson
From: Christina on 19 Mar 2010 16:29 This is a sample. F G H I VendorID Brodies Inv5 6/3/2010 55.00 Inv6 6/3/2010 155.00 Inv7 6/3/2010 255.00 VendorID Caladium Inv1 6/3/2010 515.00 Inv2 6/3/2010 525.00 Inv3 6/3/2010 535.00 Thanks I need the vendor ID copied down so I sort the spreadsheet. "L. Howard Kittle" wrote: > See my reply to send me a sample workbook, dated 2/18/201. > > Regards, > Howard > > "Christina" <Christina(a)discussions.microsoft.com> wrote in message > news:F5F1100D-10CF-4660-9C3A-8472042882D3(a)microsoft.com... > > PLEASE help. > > > > I have tried everything, and have not been able to get this to work. I > > Would you please send me the codes that I need to replace the ones I have. > > The one I have works, does the loop copies all the cells needed, but does > > not > > end. I brings up a dialog box and I have to end it myself. I really > > would > > need to finish this today. > > > > > > Thanks > > > > "Christina" wrote: > > > >> Please bear with me. I am so basic. Would you please make adjustments to > >> this. > >> 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 > >> > >> > >> > >> Thanks > >> "L. Howard Kittle" wrote: > >> > >> > At the very end of the code you will need to enter this required item: > >> > > >> > End Sub > >> > > >> > Enter it just below the last red line of code. So you will have > >> > something > >> > like this > >> > > >> > > >> > Sub MyMacro() > >> > > >> > 'all the code you want including Mike H's. > >> > > >> > End Sub > >> > > >> > If you have say only two consecutive lines of code that are red after a > >> > copy > >> > and paste, then it means the code line has wrapped and you need to have > >> > a > >> > line break OR put all that line of code on a single line. > >> > > >> > To fix a red line wrap, go to the end of the upper red line of code and > >> > from > >> > at very end do a SPACE and then an _ (underscore) then arrow up or down > >> > off > >> > the line. Usually that will correct it but sometimes the line of code > >> > will > >> > wrap at a place that will NOT accept a line break. If that happens > >> > best to > >> > just put it all in one line and live with it being way off the screen, > >> > OR > >> > you can put it all on one line and try different parts of the line to > >> > put > >> > the space and underscore and hit enter. If you pick a proper place > >> > then the > >> > code will compile and all the red will go away. You may have to try a > >> > few > >> > different spots, when it does not compile go to the end of the upper > >> > red > >> > line and hit delete to bring the bottom line back in place. (May have > >> > to > >> > hit delete several times to get rid of the large space created when it > >> > wrapped. > >> > > >> > HTH > >> > Regards, > >> > Howard > >> > > >> > "Christina" <Christina(a)discussions.microsoft.com> wrote in message > >> > news:5A3C8C0C-DF20-4066-920B-DEE4637B0B9E(a)microsoft.com... > >> > >I need help with a loop. After looping through the procedure, when > >> > >the > >> > > active cell is empty I want to exit the loop. Would you please give > >> > > me > >> > > the > >> > > codes to do that. > >> > > > >> > > > >> > > Thanks > >> > > > >> > > > >> > > Cristina > >> > > >> > > >> > . > >> > > > > . >
From: Jim Thomlinson on 19 Mar 2010 16:33 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
From: Christina on 19 Mar 2010 18:46 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 > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 5 Prev: pick multiple items in data validation Next: Deleting Duplicates in Excel - Databases |