Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
Hi,
I'm trying to create a macro that loops through some code but stops when the cell address is u400, and within it another loop that looks for a blank cell. I can't get the outer loop to recognize the limit of u400. I'm tried using activecell.value instead, but neither seem to recognize the limit. So it looks something like this: Do Until ActiveCell.Address = "a100" Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop When I run this, it does loop back to the beginning but doesn't stop until it reaches the end of the file, thus producing an error. I've tried to return the address using the address property and that was successful, but when I tried to specify the address in the do until statement, I couldn't get it to work...and naturally I can't find any documentation! Please help! Thanks, JillE |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
It needs to read "$A$100" not "a100"
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
Addresses are relative references in VBA (i.e. include $ signs)
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
Do Until ActiveCell.row = 400
Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop hope you have something in the outer loop that will keep incrementing the activeCell -- Regards, Tom Ogilvy "Jill E" wrote in message .. . Hi, I'm trying to create a macro that loops through some code but stops when the cell address is u400, and within it another loop that looks for a blank cell. I can't get the outer loop to recognize the limit of u400. I'm tried using activecell.value instead, but neither seem to recognize the limit. So it looks something like this: Do Until ActiveCell.Address = "a100" Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop When I run this, it does loop back to the beginning but doesn't stop until it reaches the end of the file, thus producing an error. I've tried to return the address using the address property and that was successful, but when I tried to specify the address in the do until statement, I couldn't get it to work...and naturally I can't find any documentation! Please help! Thanks, JillE |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
I tried the $a$100 format, but thanks!
"Chip" wrote in message oups.com... It needs to read "$A$100" not "a100" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
Then Jill you probably dont have your step right on the loop...post
more of your code |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
I tried the suggestion made by Tom using the row property instead, but I
can't get it to work in the context of the loops. Here is the actual code...can you take a quick look? 'goes to starting cell Application.Goto reference:=Worksheets("newdata").Range("newlist") Do Until ActiveCell.Row = 500 'finds the first cell that contains data Do While ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop contents = ActiveCell.Value Location = ActiveCell.Address 'goes to destination location Application.Goto reference:=Worksheets("Comments Results").Range("Q5list") 'finds the first blank cell in column Do Until ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop 'inserts the value and returns to last location to begin again ActiveCell.Value = contents Application.Goto reference:=Worksheets("newdata").Range(Location) Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop Any suggestions would be very welcome! "Tom Ogilvy" wrote in message ... Do Until ActiveCell.row = 400 Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop hope you have something in the outer loop that will keep incrementing the activeCell -- Regards, Tom Ogilvy "Jill E" wrote in message .. . Hi, I'm trying to create a macro that loops through some code but stops when the cell address is u400, and within it another loop that looks for a blank cell. I can't get the outer loop to recognize the limit of u400. I'm tried using activecell.value instead, but neither seem to recognize the limit. So it looks something like this: Do Until ActiveCell.Address = "a100" Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop When I run this, it does loop back to the beginning but doesn't stop until it reaches the end of the file, thus producing an error. I've tried to return the address using the address property and that was successful, but when I tried to specify the address in the do until statement, I couldn't get it to work...and naturally I can't find any documentation! Please help! Thanks, JillE |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
It was a little tricky to figure it out since I didnt know the ranges
of some of your range variables, but here is what I found. When this loop gets going: Do While ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop It wont stop to check if it has reached the 500th row...so here is what I did: 'goes to starting cell Application.Goto reference:=Worksheets("newdata").Range("newlist") Do Until ActiveCell.Row = 500 'finds the first cell that contains data If ActiveCell.Value = "" Then Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Else contents = ActiveCell.Value Location = ActiveCell.Address 'goes to destination location Application.Goto reference:=Worksheets("Comments Results").Range("Q5list") 'finds the first blank cell in column Do Until ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop 'inserts the value and returns to last location to begin again ActiveCell.Value = contents Application.Goto reference:=Worksheets("newdata").Range(Location) Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select End If Loop I changed it to an If, then statement so that the outer loop is run each time that check is done. See if that works, and if not let me know... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
Once you are past all the values in your search area, you are in the inner
loop and it continues to go since the only condition to stop it is if it finds a value. I added a condition so it will jump out if the activecell gets to 500. Sub TesterAA() Application.Goto reference:=Worksheets("newdata").Range("newlist") Do Until ActiveCell.Row = 500 'finds the first cell that contains data Do While ActiveCell.Value = "" And ActiveCell.Row <= 500 Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop if ActiveCell.Value = "" then exit sub contents = ActiveCell.Value Location = ActiveCell.Address 'goes to destination location Application.Goto reference:=Worksheets("Comments Results").Range("Q5list") 'finds the first blank cell in column Do Until ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop 'inserts the value and returns to last location to begin again ActiveCell.Value = contents Application.Goto reference:=Worksheets("newdata").Range(Location) Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop End Sub -- Regards, Tom Ogilvy "Jill E" wrote in message .. . I tried the suggestion made by Tom using the row property instead, but I can't get it to work in the context of the loops. Here is the actual code...can you take a quick look? 'goes to starting cell Application.Goto reference:=Worksheets("newdata").Range("newlist") Do Until ActiveCell.Row = 500 'finds the first cell that contains data Do While ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop contents = ActiveCell.Value Location = ActiveCell.Address 'goes to destination location Application.Goto reference:=Worksheets("Comments Results").Range("Q5list") 'finds the first blank cell in column Do Until ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop 'inserts the value and returns to last location to begin again ActiveCell.Value = contents Application.Goto reference:=Worksheets("newdata").Range(Location) Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop Any suggestions would be very welcome! "Tom Ogilvy" wrote in message ... Do Until ActiveCell.row = 400 Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop hope you have something in the outer loop that will keep incrementing the activeCell -- Regards, Tom Ogilvy "Jill E" wrote in message .. . Hi, I'm trying to create a macro that loops through some code but stops when the cell address is u400, and within it another loop that looks for a blank cell. I can't get the outer loop to recognize the limit of u400. I'm tried using activecell.value instead, but neither seem to recognize the limit. So it looks something like this: Do Until ActiveCell.Address = "a100" Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop When I run this, it does loop back to the beginning but doesn't stop until it reaches the end of the file, thus producing an error. I've tried to return the address using the address property and that was successful, but when I tried to specify the address in the do until statement, I couldn't get it to work...and naturally I can't find any documentation! Please help! Thanks, JillE |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
Thanks both Chip & Tom...you rock!
I had reached the same conclusion but couldn't figure out why it was working this way. Fresh eyes always works! "Chip" wrote in message ups.com... It was a little tricky to figure it out since I didnt know the ranges of some of your range variables, but here is what I found. When this loop gets going: Do While ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop It wont stop to check if it has reached the 500th row...so here is what I did: 'goes to starting cell Application.Goto reference:=Worksheets("newdata").Range("newlist") Do Until ActiveCell.Row = 500 'finds the first cell that contains data If ActiveCell.Value = "" Then Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Else contents = ActiveCell.Value Location = ActiveCell.Address 'goes to destination location Application.Goto reference:=Worksheets("Comments Results").Range("Q5list") 'finds the first blank cell in column Do Until ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop 'inserts the value and returns to last location to begin again ActiveCell.Value = contents Application.Goto reference:=Worksheets("newdata").Range(Location) Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select End If Loop I changed it to an If, then statement so that the outer loop is run each time that check is done. See if that works, and if not let me know... |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
Here is another way to do it:
Sub CopyData() Dim rng As Range, rng1 As Range Dim j As Long, i As Long j = 1 k = 1 Set rng = Worksheets("newdata").Range("newlist")(1) Set rng1 = Worksheets("Comments Results").Range("Q5list")(1) For i = rng.Row To 500 If rng(k).Value < "" Then rng1(j).Value = rng(k).Value j = j + 1 End If k = k + 1 Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Once you are past all the values in your search area, you are in the inner loop and it continues to go since the only condition to stop it is if it finds a value. I added a condition so it will jump out if the activecell gets to 500. Sub TesterAA() Application.Goto reference:=Worksheets("newdata").Range("newlist") Do Until ActiveCell.Row = 500 'finds the first cell that contains data Do While ActiveCell.Value = "" And ActiveCell.Row <= 500 Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop if ActiveCell.Value = "" then exit sub contents = ActiveCell.Value Location = ActiveCell.Address 'goes to destination location Application.Goto reference:=Worksheets("Comments Results").Range("Q5list") 'finds the first blank cell in column Do Until ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop 'inserts the value and returns to last location to begin again ActiveCell.Value = contents Application.Goto reference:=Worksheets("newdata").Range(Location) Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop End Sub -- Regards, Tom Ogilvy "Jill E" wrote in message .. . I tried the suggestion made by Tom using the row property instead, but I can't get it to work in the context of the loops. Here is the actual code...can you take a quick look? 'goes to starting cell Application.Goto reference:=Worksheets("newdata").Range("newlist") Do Until ActiveCell.Row = 500 'finds the first cell that contains data Do While ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop contents = ActiveCell.Value Location = ActiveCell.Address 'goes to destination location Application.Goto reference:=Worksheets("Comments Results").Range("Q5list") 'finds the first blank cell in column Do Until ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop 'inserts the value and returns to last location to begin again ActiveCell.Value = contents Application.Goto reference:=Worksheets("newdata").Range(Location) Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop Any suggestions would be very welcome! "Tom Ogilvy" wrote in message ... Do Until ActiveCell.row = 400 Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop hope you have something in the outer loop that will keep incrementing the activeCell -- Regards, Tom Ogilvy "Jill E" wrote in message .. . Hi, I'm trying to create a macro that loops through some code but stops when the cell address is u400, and within it another loop that looks for a blank cell. I can't get the outer loop to recognize the limit of u400. I'm tried using activecell.value instead, but neither seem to recognize the limit. So it looks something like this: Do Until ActiveCell.Address = "a100" Do whileActiveCell.value = "" ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop bla bla bla Loop When I run this, it does loop back to the beginning but doesn't stop until it reaches the end of the file, thus producing an error. I've tried to return the address using the address property and that was successful, but when I tried to specify the address in the do until statement, I couldn't get it to work...and naturally I can't find any documentation! Please help! Thanks, JillE |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
Chip,
Would you please include the text of the message to which you are responding in your posts. It makes things much easier to follow. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Chip" wrote in message ups.com... It was a little tricky to figure it out since I didnt know the ranges of some of your range variables, but here is what I found. When this loop gets going: Do While ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop It wont stop to check if it has reached the 500th row...so here is what I did: 'goes to starting cell Application.Goto reference:=Worksheets("newdata").Range("newlist") Do Until ActiveCell.Row = 500 'finds the first cell that contains data If ActiveCell.Value = "" Then Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Else contents = ActiveCell.Value Location = ActiveCell.Address 'goes to destination location Application.Goto reference:=Worksheets("Comments Results").Range("Q5list") 'finds the first blank cell in column Do Until ActiveCell.Value = "" Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select Loop 'inserts the value and returns to last location to begin again ActiveCell.Value = contents Application.Goto reference:=Worksheets("newdata").Range(Location) Application.ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select End If Loop I changed it to an If, then statement so that the outer loop is run each time that check is done. See if that works, and if not let me know... |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Address property in nested loops
I, like most people, am not using Google to read the newsgroups.
In a standard newsreader, the quoted text is omitted in your posts. Please include it. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Chip" wrote in message ups.com... From:Chip To:Chip If you click on "Show quoted text" it will show which message I am replying to. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with nested for loops | Excel Discussion (Misc queries) | |||
Help with nested for loops | Excel Worksheet Functions | |||
Help on nested loops | Excel Programming | |||
Nested loops?? | Excel Programming | |||
RANGE & NESTED LOOPS | Excel Programming |