Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
Christina,
Post your loop and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
Actually I would need it to Exit when there are no more cells with "Vendor ID"
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 Loop Thanks "Mike H" wrote: Christina, Post your loop and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
Hi,
Try this Sub nn() Dim LastRow As Long Dim MyRange As Range LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row Set MyRange = Range("F1:F" & LastRow) For Each c In MyRange If UCase(c.Value) = "VENDOR ID" Then c.ClearContents Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: Actually I would need it to Exit when there are no more cells with "Vendor ID" 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 Loop Thanks "Mike H" wrote: Christina, Post your loop and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
Thanks. I am sorry I need step by step instruction. This Do Loop is part
of the macro. After the first part is run, then it moves into the LOOP. So where do I put your codes. Do I start with DO, then copy your codes. Sorry for more questions, I am very basic at this. THanks "Mike H" wrote: Hi, Try this Sub nn() Dim LastRow As Long Dim MyRange As Range LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row Set MyRange = Range("F1:F" & LastRow) For Each c In MyRange If UCase(c.Value) = "VENDOR ID" Then c.ClearContents Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: Actually I would need it to Exit when there are no more cells with "Vendor ID" 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 Loop Thanks "Mike H" wrote: Christina, Post your loop and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
Christina.
Copy my code then go to your code and select all the lines from Do to Loop The pres and hold the CTRL key and tap V to paste my code over yours -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: Thanks. I am sorry I need step by step instruction. This Do Loop is part of the macro. After the first part is run, then it moves into the LOOP. So where do I put your codes. Do I start with DO, then copy your codes. Sorry for more questions, I am very basic at this. THanks "Mike H" wrote: Hi, Try this Sub nn() Dim LastRow As Long Dim MyRange As Range LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row Set MyRange = Range("F1:F" & LastRow) For Each c In MyRange If UCase(c.Value) = "VENDOR ID" Then c.ClearContents Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: Actually I would need it to Exit when there are no more cells with "Vendor ID" 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 Loop Thanks "Mike H" wrote: Christina, Post your loop and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
When I copy the code it all comes up in red, ANd when I try to run it it
says Compile Error Expected End Sub Thanks Cristina "Mike H" wrote: Christina. Copy my code then go to your code and select all the lines from Do to Loop The pres and hold the CTRL key and tap V to paste my code over yours -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: Thanks. I am sorry I need step by step instruction. This Do Loop is part of the macro. After the first part is run, then it moves into the LOOP. So where do I put your codes. Do I start with DO, then copy your codes. Sorry for more questions, I am very basic at this. THanks "Mike H" wrote: Hi, Try this Sub nn() Dim LastRow As Long Dim MyRange As Range LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row Set MyRange = Range("F1:F" & LastRow) For Each c In MyRange If UCase(c.Value) = "VENDOR ID" Then c.ClearContents Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: Actually I would need it to Exit when there are no more cells with "Vendor ID" 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 Loop Thanks "Mike H" wrote: Christina, Post your loop and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
Try something like the following:
Sub AAA() Dim R As Range Set R = ActiveCell Do Until R.Value = vbNullString Debug.Print "do something with R" Set R = R(2, 1) ' move down Loop End Sub Note that the Active Cell is not changed within the loop. ActiveCell will point to the first cell during the entire looping process. You use the R variable to access each cell within the loop. If you really need to change which cell is active (it is almost never necessary), use R.Select on a new line just below the Do Until line. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 18 Mar 2010 07:56:06 -0700, Christina wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
Where do I put that , or what do I replace in my loo
My Do starts after some other functions, then DO......LOOP then End Sub for the enture Macro. This is the loop: 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 The Data that needs to be copied is in the cell after the cell with Text ---Vendor Id. Thanks "Chip Pearson" wrote: Try something like the following: Sub AAA() Dim R As Range Set R = ActiveCell Do Until R.Value = vbNullString Debug.Print "do something with R" Set R = R(2, 1) ' move down Loop End Sub Note that the Active Cell is not changed within the loop. ActiveCell will point to the first cell during the entire looping process. You use the R variable to access each cell within the loop. If you really need to change which cell is active (it is almost never necessary), use R.Select on a new line just below the Do Until line. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 18 Mar 2010 07:56:06 -0700, Christina wrote: 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 . |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
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" wrote in message ... 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 . |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
First, I wouldn't use this. I think there are not enough checks to make sure
that the data matches what you expect. And I'm guessing that you expect that "vendor id" header cell, more than one non-empty "detail" cell, a gap before the next header cell and more detail cells. I'd want to check that I had some non-empty details and make sure that there was a gap between the header and the previous details. But I _think_ that this does what your code did. Option Explicit Sub testme() Dim wks As Worksheet Dim FoundCell As Range Dim myCell As Range Dim RngToFill As Range Set wks = Worksheets("Sheet1") With wks Do With .Range("F:f") Set FoundCell = .Cells.Find(What:="Vendor ID", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With If FoundCell Is Nothing Then 'done, get out Exit Do End If Set myCell = FoundCell.Offset(1, 0) Set RngToFill = .Range(myCell, myCell.End(xlDown)) FoundCell.ClearContents myCell.Copy _ Destination:=RngToFill Loop End With End Sub But it still scares me! Christina wrote: Actually I would need it to Exit when there are no more cells with "Vendor ID" 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 Loop Thanks "Mike H" wrote: Christina, Post your loop and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Christina" wrote: 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
Hi Christina,
I ain't figuring out what you want to do. If you want, send me a sample workbook and detailed instructions of what you want to happen, where you want it to happen and the such. You have a lot of .Select in the code offered and you seldom need to select stuff to get stuff done. Remove the & charachters. Regards, Howard "Christina" wrote in message ... Where do I put that , or what do I replace in my loo My Do starts after some other functions, then DO......LOOP then End Sub for the enture Macro. This is the loop: 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 The Data that needs to be copied is in the cell after the cell with Text ---Vendor Id. Thanks "Chip Pearson" wrote: Try something like the following: Sub AAA() Dim R As Range Set R = ActiveCell Do Until R.Value = vbNullString Debug.Print "do something with R" Set R = R(2, 1) ' move down Loop End Sub Note that the Active Cell is not changed within the loop. ActiveCell will point to the first cell during the entire looping process. You use the R variable to access each cell within the loop. If you really need to change which cell is active (it is almost never necessary), use R.Select on a new line just below the Do Until line. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 18 Mar 2010 07:56:06 -0700, Christina wrote: 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 . |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
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" wrote in message ... 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 . |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
See my reply to send me a sample workbook, dated 2/18/201.
Regards, Howard "Christina" wrote in message ... 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" wrote in message ... 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 . |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
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" wrote in message ... 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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
loop
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" wrote in message ... 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" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop | Excel Discussion (Misc queries) | |||
For..Next..Loop | Excel Discussion (Misc queries) | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
DO LOOP in VBA | Excel Discussion (Misc queries) | |||
Loop | Excel Discussion (Misc queries) |