PDA

View Full Version : Solved: For Each c In Range("Test")

Innany
06-08-2006, 01:21 PM
Trying to see if this is possible. I have a loop:

For Each c In Range("Test")

....

next c

Test is a named range with 1 column, let's say it is numbers 1 through 10.

What I need to do is when a certain condition inside the loop is met, I need to go back to previous c and repeat the loop( ex. if c is currently 5, and the condition is met, I need it to become 4 and do the for loop again). Is there anyway to do that?
Your help on this is greatly appreciated.

Jacob Hilderbrand
06-08-2006, 01:36 PM
You may want to try something like.

For i = 1 to 10 'Row 1 to 10
'Do Something
'Range("A" & i) etc.
If Something = True then
i = i - 2 'Minus 2 because the next line will add 1.
End If
'Do Something Else
Next i

Cyberdude
06-08-2006, 01:38 PM
I'm guessing that if you used a loop control like "For N=1 to ..." you could modify the value of N to do what you want.
Just a thought.

Innany
06-08-2006, 01:47 PM
Actually I was using 1 to 10 as an example (i guess bad example in this case), the actual range consists of server names (strings), so that would not work

mdmackillop
06-08-2006, 02:13 PM
Try

Sub Loops()
Dim c As Range, Check As Boolean
Retest:
Check = False
For Each c In Range("Test")
If c = 5 Then
c.Value = 4
Check = True
Exit For
End If
Next
If Check = True Then GoTo Retest
End Sub

Jacob Hilderbrand
06-08-2006, 02:27 PM
The 1-10 I used above would be used to determine the Row number and has nothing to do with the actual value in the cell. So long as the range is contiguous you can use this loop and just specify the start and end row.

For example, if your data is in Row 5-40 then.

For i = 5 to 40

Etc.

Innany
06-09-2006, 06:53 AM
Is there a way to determine the row inside the range, when you know the value? Kind of an opposite of INDEX function. Never mind just found it, it is called MATCH, I think that should solve my issue. Thank you for all your help

Innany
06-09-2006, 07:32 AM
mdmackillop (http://vbaexpress.com/forum/member.php?u=87) I just tested your suggestion above and it does not work. Even if I change c inside the loop, let's say:

Sub Loops()
Dim c As Range, Check As Boolean
Retest:
Check = False
For Each c In Range("Test")
If c = 5 Then
c.Value = 4
Check = True
Exit For
End If
Next
If Check = True Then Goto Retest
End Sub

If Test is a range of numbers from 1 to 5, and I change the value of c inside the loop, it will still go to the next c in range. Example:

if c=3 and I change it inside the loop to 1, next it will go to 4, not to 2.

When I try the same inside:

for i=1 to 10 loop, like someone suggested above , that will work, but I need to do it inside the range

mdmackillop
06-09-2006, 04:23 PM
My code will restart the loop. Whether this is appropriate or not depends upon the intervening code. your question states "I need it to become 4 and do the for loop again). ", which is what my code does.
Regards
MD

Innany
06-12-2006, 08:30 AM
Ok, maybe I am explaining this wrong. Let me try again:

The range I have consists of strings (server names), what i need is when a certain condition inside the for each c in range loop met, I need the loop to be repeated for a previous line in the range and then continue to the next one.

Let me give you an example:

Range:

server1
server2
server3
server4
server5

It goes through the loop, and it gets to server3, the condition is met, so it needs to go back to server2 do the loop, and then continue to server3.

If I change c inside the loop using your code, it does not do that. What it does it gets to server3, the condition is met, it changes it to server2, but when it goes to next c, it still continues to server4.

I hope this makes it clearer.

Norie
06-12-2006, 09:15 AM
What is the condition and why do you need to go back and restart the loop?

mdmackillop
06-12-2006, 02:19 PM
If you add a messagebox into the loop, you'll see that it is restarting the loop. I can't think of a simple way of regressing a for each loop by one value.

Sub Loops()
Dim c As Range, Check As Boolean
Retest:
Check = False
For Each c In Range("Test")
If c = 5 Then
c.Value = 4
Check = True
Exit For
End If
MsgBox c
Next
If Check = True Then GoTo Retest
End Sub

Innany
06-12-2006, 06:05 PM
Thank you, I changed the loop to : for i = 1 to x and then it does work the way I need it, I just need an extra step to convert an i into the value from the range, but that's ok. Thanks for all your help.

mdmackillop
06-12-2006, 10:42 PM
Try

Sub Loops()
Dim c As Range, Check As Boolean
Retest:
Check = False
For i = 1 To UBound(Range("Test")())
If Range("Test")(i) = "Server4" Then
Range("Test")(i).Value = "Changed"
i = i - 1
End If
MsgBox Range("Test")(i)
Next
End Sub