PDA

View Full Version : Solved: For next i help



wilg
06-25-2011, 06:04 AM
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

mbarron
06-25-2011, 06:37 AM
Put your current code inside another loop. Change 34 in the line For j = 10 To 34 Step 4 to the correct row number of your last month. The current code will do the first 6 months:
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

wilg
06-25-2011, 06:55 AM
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?

wilg
06-25-2011, 07:18 AM
Hi mbarron, it works great. Thanks so much. That save me from adjusting 24 separate codes 6 times.

Mike.