-
Solved: For next i help
Hi guys, need some help with the following code. I have a calendar over 2 years. I use this code 24 times....
Is there a way to modify it just to loop?
It starts at row 10 and skips every 4 rows.
For i = 2 To 32
'january
If Cells(10, i).Value = "PU" Or Cells(10, i).Value = "PP" Or Cells(10, i).Value = "F" Or Cells(10, i).Value = "pu" Or Cells(10, i).Value = "pp" Or Cells(10, i).Value = "f" Then
Email_Body = Email_Body & vbNewLine & Format(Cells(8, i).Value, "dddd mmmm d, yyyy = ") & Cells(10, i).Value & " - " & Cells(11, i).Value
End If
Next i
.Body = Email_Body
If Range("ah8") + Range("ai8") + Range("al8") > 0 Then
Email_Body = Email_Body & vbNewLine
End If
.Body = Email_Body
For i = 2 To 32
'february
If Cells(14, i).Value = "PU" Or Cells(14, i).Value = "PP" Or Cells(14, i).Value = "F" Or Cells(14, i).Value = "pu" Or Cells(14, i).Value = "pp" Or Cells(14, i).Value = "f" Then
Email_Body = Email_Body & vbNewLine & Format(Cells(12, i).Value, "dddd mmmm d, yyyy = ") & Cells(14, i).Value & " - " & Cells(15, i).Value
End If
Next i
.Body = Email_Body
If Range("ah12") + Range("ai12") + Range("al12") > 0 Then
Email_Body = Email_Body & vbNewLine
End If
.Body = Email_Body
-
Put your current code inside another loop. Change 34 in the line [vba]For j = 10 To 34 Step 4[/vba] to the correct row number of your last month. The current code will do the first 6 months:
[vba] For j = 10 To 34 Step 4
For i = 2 To 32
If Cells(j, i).Value = "PU" Or Cells(j, i).Value = "PP" Or Cells(j, i).Value = "F" Or _
Cells(j, i).Value = "pu" Or Cells(j, i).Value = "pp" Or Cells(j, i).Value = "f" Then
Email_Body = Email_Body & vbNewLine & Format(Cells(j - 2, i).Value, "dddd mmmm d, yyyy = ") _
& Cells(j, i).Value & " - " & Cells(j + 1, i).Value
End If
Next i
.Body = Email_Body
If Range("ah" & j - 2) + Range("ai" & j - 2) + Range("al" & j - 1) > 0 Then
Email_Body = Email_Body & vbNewLine
End If
.Body = Email_Body
Next j
[/vba]
-
I'm just testing now, but in my origional post I have ref to row 10 and row 8. If I use "j" will it still ref the 2 diff rows for the same loop?
-
Hi mbarron, it works great. Thanks so much. That save me from adjusting 24 separate codes 6 times.
Mike.
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