Is there a way to iterate from bottom to top of range? (1 Viewer)

M_S_Jones

Registered User.
Local time
Today, 07:09
Joined
Jan 4, 2008
Messages
119
Good morning all,

I posted on here last week regarding a loop I've been working with and we touched upon the topic of iterating 'backwards', as it were, through the range. The loop I've been working with looks like this:

Code:
Dim rnArea As Range
Dim rnCell As Range

Set rnArea = Range("D8:D1000")

For Each rnCell In rnArea
     With rnCell
        'Commands will go here
    End With
Next
I've now found a scenario where it really would be useful to be able to iterate through each cell from the bottom of the range to the top of the range. I've tried reversing the range, so that it is specified as D1000 : D8, but this makes no difference.

Thanks in advance for any help you can offer,

Matthew
 

chergh

blah
Local time
Today, 07:09
Joined
Jun 15, 2004
Messages
1,414
Try:

Code:
for i = 1000 to 8 step -1
   with range("D" & i)
      do stuff
   end with
next i
 

M_S_Jones

Registered User.
Local time
Today, 07:09
Joined
Jan 4, 2008
Messages
119
That works perfectly,

Thank you!

Matthew
 

M_S_Jones

Registered User.
Local time
Today, 07:09
Joined
Jan 4, 2008
Messages
119
Thanks again for that code, but it now leads me onto another question: is it possible to incorporate that range,

i = 1000 to 8 step -1
with range("D" & i)

into a While loop? I've just changed my specification a little and has now become necessary for an exit condition to be met.

Any help would be greatly appreciated.

Matthew
 

chergh

blah
Local time
Today, 07:09
Joined
Jun 15, 2004
Messages
1,414
Code:
i = 1000
do while <condition>

   with range("D" & i)
      do stuff
   end with
   i = i -1
loop
 

M_S_Jones

Registered User.
Local time
Today, 07:09
Joined
Jan 4, 2008
Messages
119
Thank you for the code. I'm having problems with it though, it doesn't seem to like the line

"with range("D" & i)"

and is generating a "Method 'Range' of object '_Global' failed" error. Here's the code slightly customised:

Code:
i = 1000
Do While VarDiv = ""
   With Range("D" & i)
    If .Value Like "Total for*" Then
        VarDiv = Mid(.Value, 10, 20)
        .Select
        VarLin = ActiveCell.Row
    End If
   End With
   i = i - 1
Loop

Any ideas what's wrong with it?

Thanks,

Matthew
 

chergh

blah
Local time
Today, 07:09
Joined
Jun 15, 2004
Messages
1,414
Try prefixing your range with the worksheet. e.g.

Code:
i = 1000
Do While VarDiv = ""
   With thisworkbook.worksheets("Sheet1").Range("D" & i)
    If .Value Like "Total for*" Then
        VarDiv = Mid(.Value, 10, 20)
        .Select
        VarLin = ActiveCell.Row
    End If
   End With
   i = i - 1
Loop
 

M_S_Jones

Registered User.
Local time
Today, 07:09
Joined
Jan 4, 2008
Messages
119
Thanks for the advice, but that still causes the exact same error, any other suggestions?
 

chergh

blah
Local time
Today, 07:09
Joined
Jun 15, 2004
Messages
1,414
I mocked up something on my machine and it worked without any problems. Can you post the entire code please. I'm also off to a meeting so might not respond till tomorrow.
 

M_S_Jones

Registered User.
Local time
Today, 07:09
Joined
Jan 4, 2008
Messages
119
Never mind, it was me being an idiot! The data that I want to search is in column A, not D, that's why it was struggling with the range! Thanks for all of your help.

Matthew
 

Users who are viewing this thread

Top Bottom