Multiple For Each Next Statements, Excel 2000 & 2003

[jfcby]

Hello,

I wanted to know how to get multiple For Each Next statements to work.

This is what I want For Each Next Statement 1, For Each Next Statement
2 and I'll need to include several but the For Each Next Statement 1 is
the only one that will work the macro will never go to the next one.

My code:

Sub ListBeltSizesPrefix()
'Add prefix to all belt sizes 4L, A, AX, 5L, B, BX
'Add prefix to belt sizes 4L
Dim Cell As Object, Cell2 As Object

For Each Cell In Range("A:A") 'Selection
Cell.Select
If Cell.Value = "" Or Cell.Value = "-" Then
Exit Sub
ElseIf Cell.Value = "4L" Then
Range("A2").Select
Else
Cell.Value = "4L" & Cell.Value
End If
Next Cell

'Add prefix to belt sizes A
For Each Cell2 In Range("B:B") 'Selection
Cell2.Select
If Cell2.Value = "" Or Cell2.Value = "-" Then
Exit Sub
ElseIf Cell2.Value = "A" Then
Range("A2").Select
Else
Cell2.Value = "A" & Cell2.Value
End If
Next Cell2
End Sub

Thank you for your help in advance,
jfcby

 

[Pete_UK]

Where you have this in the first loop:

If Cell.Value = "" Or Cell.Value = "-" Then
Exit Sub

it's causing you to jump out of the routine when you reach an empty
cell or one containing "-", and so you will never get to the second
loop. By re-examining your logic on the first loop, you could come up
with this:

For Each Cell In Range("A:A") 'Selection
Cell.Select
If Cell.Value <> "" And Cell.Value <> "-" And Cell.Value <> "4L" Then
Cell.Value = "4L" & Cell.Value
End If
Next Cell

and a similar modification for the second loop. Still not very
efficient, as you are examining every cell in column A, but it should
do what I think you are trying to do.

Hope this helps.

Pete

 

[jfcby]

Hello Pete_UK,

Thank you for your help!

This is the working code:

Private Sub ListBeltSizesPrefix()
'Add prefix to all belt sizes 4L, A, AX, 5L, B, BX
Dim Cell As Object, Cell2 As Object
Dim r As Range
Dim c As Variant

'Add prefix to belt sizes 4L
For Each Cell In Range("A:A") 'Selection
c = "4L"
Set r = Range("A3")
Cell.Select
If Cell.Value = "" Or Cell.Value = "-" Then
Exit For
ElseIf Cell.Value <> c Then
Cell.Value = c & Cell.Value
End If
Next Cell

'Add prefix to belt sizes A
For Each Cell In Range("B:B") 'Selection
c = "A"
Set r = Range("B3")
Cell.Select
If Cell.Value = "" Or Cell.Value = "-" Then
Exit For
ElseIf Cell.Value <> c Then
Cell.Value = c & Cell.Value
End If
Next Cell

'Add prefix to belt sizes AX
For Each Cell In Range("C:C") 'Selection
c = "AX"
Set r = Range("C3")
Cell.Select
If Cell.Value = "" Or Cell.Value = "-" Then
Exit For
ElseIf Cell.Value <> c Then
Cell.Value = c & Cell.Value
End If
Next Cell

'Add prefix to belt sizes 5L
For Each Cell In Range("F:F") 'Selection
c = "5L"
Set r = Range("F3")
Cell.Select
If Cell.Value = "" Or Cell.Value = "-" Then
Exit For
ElseIf Cell.Value <> c Then
Cell.Value = c & Cell.Value
End If
Next Cell

'Add prefix to belt sizes B
For Each Cell In Range("G:G") 'Selection
c = "B"
Set r = Range("G3")
Cell.Select
If Cell.Value = "" Or Cell.Value = "-" Then
Exit For
ElseIf Cell.Value <> c Then
Cell.Value = c & Cell.Value
End If
Next Cell

'Add prefix to belt sizes BX
For Each Cell In Range("H:H") 'Selection
c = "BX"
Set r = Range("H3")
Cell.Select
If Cell.Value = "" Or Cell.Value = "-" Then
Exit For
ElseIf Cell.Value <> c Then
Cell.Value = c & Cell.Value
End If
Next Cell
End Sub

Thank you for your help,
jfcby

 

[Pete_UK]

Glad you got it working. It is not a good idea, though, to keep jumping
out of a loop structure by means of the Exit For statements. I think
you would be better using a Do While structure or Do Until.

Hope this helps.

Pete

 

[jfcby]

Hello Pete,

I'm still learning Excel VBA and I was wondering if you could give me
some examples Do While and Do Until code to understand it better. I've
tried to work with them some but they do not work.

Thank you for your help,
jfcby

 

[Pete_UK]

OK. First of all, if you examine your 6 loops you will see that they
are almost the same, particularly now that you have introduced the
variable c - the only difference between them is the column you are
searching and the value of c for that column. This implies that you
should be able to make the search loop into a subroutine into which you
can pass the parameters for c and for column.

In terms of the loop itself, you want to search through a column until
you reach a cell which is blank or contains a hyphen - this statement
implies the use of DO UNTIL. You could make use of DO WHILE, but the
condition here would be the opposite, i.e. While the cell does NOT
contain a hyphen or is blank, then carry on ...

In looking at a cell, if it already contains the value of c then don't
do anything, otherwise add c to the beginning of the contents. These
things considered, then, you could probably simplify your macro as
follows:

Private Sub ListBeltSizesPrefix()

'Add prefixes to all belt sizes 4L, A, AX, 5L, B, BX

Add_prefix "4L", "A"
Add_prefix "A", "B"
Add_prefix "AX", "C"
Add_prefix "5L", "F"
Add_prefix "B", "G"
Add_prefix "BX", "H"
End Sub


Private Sub Add_prefix(c, col)

'Add prefix c (belt size) in appropriate column col

Range(col &"3").Select
Do Until ActiveCell.Value = "" Or ActiveCell.Value = "-" Or
ActiveCell.Row >10000
'no point in looping too far
If ActiveCell.Value <> c Then
ActiveCell.Value = c & ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

You can see that the code is a lot shorter and that it is easier to
follow (and therefore to maintain in the future). I've not tested it
out.

Hope this helps.

Pete

 

[Pete_UK]

Note: awkward line-break on the Do Until line - change it to this:

Do Until ActiveCell.Value = "" Or _
ActiveCell.Value = "-" Or ActiveCell.Row >10000

Pete

 

[jfcby]

Hello Pete,

Thank you for the explaination on Do Until and Do While. It has help
me understand it better for further use and learned a few things I did
not know you could do. The code works great!

I'm sorry for waiting so long to thank you.

Thank you for your help,
jfcby

 

[Pete_UK]

Thanks for the feedback - glad it worked for you.

Pete