|
-
Dec 21st, 2005, 10:33 AM
#1
Thread Starter
Hyperactive Member
 Fix a For Each statement :please:
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
#2
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.
Declan
Don't forget to mark your Thread as resolved.
Take a moment to rate posts that you think are helpful 
-
Dec 21st, 2005, 11:22 AM
#3
Thread Starter
Hyperactive Member
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
#4
Frenzied Member
Re: Fix a For Each statement :please:
The following code appears about midway down in your listing ...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".
Blessings in abundance,
All the Best,
& ENJOY!
Art . . . . Carlisle, PA . . USA
-
Dec 21st, 2005, 01:40 PM
#5
Thread Starter
Hyperactive Member
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..
-
Dec 21st, 2005, 01:49 PM
#6
Thread Starter
Hyperactive Member
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote