Solved: For Each c In Range("Test")

[Innany]

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]

You may want to try something like.

[vba]
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[/vba]

[Cyberdude]

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]

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]

Try
[VBA]
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

[/VBA]

[Jacob Hilderbrand]

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]

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]

mdmackillop I just tested your suggestion above and it does not work. Even if I change c inside the loop, let's say:

[VBA]
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
[/VBA]

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]

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

BTW, Please use the VBA tags on your code

[Innany]

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]

What is the condition and why do you need to go back and restart the loop?

[mdmackillop]

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.
[VBA]
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

[/VBA]

[Innany]

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]

Try
[vba]
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

[/vba]