Help with Address property in nested loops

J

Jill E

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
 
T

Tom Ogilvy

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

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
 
J

Jill E

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!
 
C

Chip

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

Tom Ogilvy

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
 
J

Jill E

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!
 
T

Tom Ogilvy

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
 
C

Chip Pearson

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
 
C

Chip

From:Chip
To:Chip

If you click on "Show quoted text" it will show which message I am
replying to.
 
C

Chip Pearson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Userform to increment line item numbers with textboxes-spinbuttons 5
Error Handling with Nested Loops 3
Help with SetSourceData 1
Workbooks.OpenText method help 3
Help with Loop 2
PLEASE help ... 3
help with 'Find' error handling please 2
Help with formula in vb coding 5

Top