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

Help with Address property in nested loops
 

It needs to read "$A$100" not "a100"

Help with Address property in nested loops
 

Addresses are relative references in VBA (i.e. include $ signs)

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

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"

Help with Address property in nested loops
 

Then Jill you probably dont have your step right on the loop...post
more of your code

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

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...

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

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...

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

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...

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.