Fix a For Each statement :please:

Printable View

How do I tied this If Else statements into the For Each? This is what I want to do but am unable to write it correctly.. Any help would be appreciated.

VB Code:

For Each MyCell In Sheet1.Columns("J").Cells
        With MyCell
            If .Value < "04/30/2006" Then
                For Each MyCell In Sheet1.Columns("A").Cells
                With MyCell
                    If .Value = "" Then Exit For
                    If .Value = 574 _
                    Or .Value = 555 _
                    Or .Value = 510 Then
                    .Offset(0, 12).Value = .Offset(0, 8).Value * 1.1
                    Else
                        For Each MyCell In Sheet1.Columns("D").Cells
                        With MyCell
                            If .Value <> "OMI" Then
                            .Offset(0, 12).Value = .Offset(0, 8).Value * 1.4503
                            Else
                            .Offset(0, 12).Value = .Offset(0, 8).Value
                            End If
                        End With
                        Next MyCell
                    End If
                End With
                Next MyCell
            Else
            If .Value > "04/30/2006" Then
                For Each MyCell In Sheet1.Columns("A").Cells
                With MyCell
                    If .Value = "" Then Exit For
                    If .Value = 574 _
                    Or .Value = 555 _
                    Or .Value = 510 Then
                    .Offset(0, 12).Value = .Offset(0, 8).Value * 1.2 'change later
                    Else
                        For Each MyCell In Sheet1.Columns("D").Cells
                        With MyCell
                            If .Value <> "OMI" Then
                            .Offset(0, 12).Value = .Offset(0, 8).Value * 1.5 ' change later
                            Else
                            .Offset(0, 12).Value = .Offset(0, 8).Value
                            End If
                        End With
                        Next MyCell
                    End If
                End With
                Next MyCell
            End If
        End With
        Next MyCell
End Sub

Dec 21st, 2005, 10:45 AM
DKenny

Re: Fix a For Each statement :please:
Von
You can't use the MyCell Variable in nested loops, as you have here.

Here's a simple example of what works and doesn't work in loops
VB Code:
```
'This doesn't work
For x = 1 To 2
    For x = 10 To 20
        'some code here
    Next x
Next x
 
'This works
For x = 1 To 2
    For Y = 10 To 20
        'some code here
    Next Y
Next x
```
In your code you will need to replace the "inner" occurances of MyCell with a new object variable. In the same way that I have replaced the "inner" occurances of X with Y in the above.

Re: Fix a For Each statement :please:

Hmm...Like this maybe?

VB Code:

For Each MyCell1 In Sheet1.Columns("J").Cells
        With MyCell1
            If .Value < "04/30/2006" Then
                For Each MyCell2 In Sheet1.Columns("A").Cells
                With MyCell2
                    If .Value = "" Then Exit For
                    If .Value = 574 _
                        Or .Value = 555 _
                        Or .Value = 510 Then
                        .Offset(0, 12).Value = .Offset(0, 8).Value * 1.1
                    End If
                    End With
                Next MyCell2
            Else
                For Each MyCell3 In Sheet1.Columns("D").Cells
                With MyCell3
                    If .Value <> "OMI" Then
                        .Offset(0, 12).Value = .Offset(0, 8).Value * 1.4503
                    Else
                        .Offset(0, 12).Value = .Offset(0, 8).Value
                    End If
                    End With
                Next MyCell3
            End If
            End With
        Next MyCell1
            Else
            If .Value > "04/30/2006" Then
                For Each MyCell2 In Sheet1.Columns("A").Cells
                With MyCell2
                    If .Value = "" Then Exit For
                    If .Value = 574 _
                        Or .Value = 555 _
                        Or .Value = 510 Then
                        .Offset(0, 12).Value = .Offset(0, 8).Value * 1.2 'change later
                    End If
                    End With
                Next MyCell2
            Else
                For Each MyCell3 In Sheet1.Columns("D").Cells
                With MyCell3
                    If .Value <> "OMI" Then
                        .Offset(0, 12).Value = .Offset(0, 8).Value * 1.5 'change later
                    Else
                        .Offset(0, 12).Value = .Offset(0, 8).Value
                    End If
                    End With
                Next MyCell3
            End If
            End With
        Next MyCell1

Dec 21st, 2005, 01:37 PM
Webtest

Re: Fix a For Each statement :please:

The following code appears about midway down in your listing ...

Code:

Next MyCell1 Else

Where is the "If" that is associated with this "Else"? You need to straighten out your indentation, and perhaps comment the levels of "If" and "With".
Dec 21st, 2005, 01:40 PM
vonoventwin

Re: Fix a For Each statement :please:

I took that Else out (it was there by mistake) :) I will comment on the If and With and maybe you can help :) Please..

Re: Fix a For Each statement :please:

Hope this helps:

VB Code:

' use this statement for all values in column J less then the date of 04/30/2006
    For Each MyCell1 In Sheet1.Columns("J").Cells
        With MyCell1
            If .Value < "04/30/2006" Then
            ' If less then 04/30/2006 then use this statement otherwise End If
                For Each MyCell2 In Sheet1.Columns("A").Cells
                With MyCell2
                ' If value is blank Exit For
                    If .Value = "" Then Exit For
                    ' If value is 574, 555, or 510 then times value by 1.1
                    If .Value = 574 _
                        Or .Value = 555 _
                        Or .Value = 510 Then
                        .Offset(0, 12).Value = .Offset(0, 8).Value * 1.1
                    End If
                    End With
                Next MyCell2
                'If the value is not 574,555,or 510 then do this statement
            Else
                For Each MyCell3 In Sheet1.Columns("D").Cells
                With MyCell3
                'If the value of column D is not equal to OMI then times value by 1.4503
                    If .Value <> "OMI" Then
                        .Offset(0, 9).Value = .Offset(0, 5).Value * 1.4503
                        'If it is equal to OMI then do this
                    Else
                        .Offset(0, 9).Value = .Offset(0, 5).Value
                    End If
                    End With
                Next MyCell3
            End If
            End With
        Next MyCell1
 
' ______________________________________________________________________________________
        
' use this statement for all values in column J greater then the date of 04/30/2006
    For Each MyCell1 In Sheet1.Columns("J").Cells
        With MyCell1
            If .Value > "04/30/2006" Then
                For Each MyCell2 In Sheet1.Columns("A").Cells
                With MyCell2
                    If .Value = "" Then Exit For
                    If .Value = 574 _
                        Or .Value = 555 _
                        Or .Value = 510 Then
                        .Offset(0, 12).Value = .Offset(0, 8).Value * 1.2 'change later
                    End If
                    End With
                Next MyCell2
            Else
                For Each MyCell3 In Sheet1.Columns("D").Cells
                With MyCell3
                    If .Value <> "OMI" Then
                        .Offset(0, 9).Value = .Offset(0, 5).Value * 1.5 'change later
                    Else
                        .Offset(0, 9).Value = .Offset(0, 5).Value
                    End If
                    End With
                Next MyCell3
            End If
            End With
        Next MyCell1
End Sub