CODEWITHSUNDEEP
vbaexcel
Peel
Peel

Reputation: 31

Excel VBA - How to Move Horizontal Range Down One Row in Loop

I am making a calendar that checks a report for project due dates and for any dates that match the calendar then highlights the matching cells and writes the date and name of the project in the 2 columns to the right of the month.

I am doing this by week, for all 12 months of the year. So, rather than doing this for each week of the year I want to make a loop that loops the code for 1 week, 5 times. Then put that inside of a loop that does it for all 12 months. I have code for the first week of the month and want to add the variable "x" to the range so that I can add 1 to it after the week the range will move down 1 row to do the next week. I haven't been able to find a way to put the "x" in the range.

Any help would be appreciated here is the code I have:

'for january
Set januaryRng = ActiveSheet.Range("A2:G2")
i = 2
For x = 0 to 4
For Each cell In januaryRng
 If cell <> "" Then
         For i = 2 To lastRow
               If cell.Value = Sheets("Incident Summary Report").Cells(i, "AI").Value Then
                Sheets("sheet1").Cells(2 + x, "I") = Sheets("sheet1").Cells(2 + x, "I") & Chr(10) & Sheets("Incident Summary Report").Cells(i, "B").Value
               ElseIf cell.Value = Sheets("Incident Summary Report").Cells(i, "AJ").Value Then
                   Sheets("sheet1").Cells(2 + x, "I") = Sheets("sheet1").Cells(2 + x, "I") & Chr(10) & Sheets("Incident Summary Report").Cells(i, "B").Value
              End If
              If cell.Value = Sheets("Incident Summary Report").Cells(i, "AI").Value Then
                  Sheets("sheet1").Cells(2 + x, "H") = Sheets("sheet1").Cells(2 + x, "H") & Chr(10) & Sheets("Incident Summary Report").Cells(i, "AI").Value
              ElseIf cell.Value = Sheets("Incident Summary Report").Cells(i, "AJ").Value Then
                 Sheets("sheet1").Cells(2 + x, "H") = Sheets("sheet1").Cells(2 + x, "H") & Chr(10) & Sheets("Incident Summary Report").Cells(i, "AJ").Value
                End If
          Next i
      Else
 End If
Next cell
Next x

Upvotes: 0

Views: 1738

Answers (1)

Behnam M Heidari
Behnam M Heidari

Reputation: 98

I think I see where you want to go now

The range (ActiveSheet.Range("A2:G2")) that you're looping through only contains 7 days (cells) from the first week, right?

What you need to do is to set a new range when your x-loop iterates.

This means that you need to move this part:

Set januaryRng = ActiveSheet.Range("A2:G2")

Below this part:

For x = 0 to 4

And then you need to change your range reference from

"A2:G2" to "A" & 2 + x, "G" & 2 + x

All in all it would look like

'for January
i = 2
For x = 0 to 4
    Set januaryRng = ActiveSheet.Range("A" & 2 + x, "G" & 2 + x)
    For Each cell In januaryRng
        If cell <> "" Then
            ......

This way, the januaryRng will change from .Range("A2", "G2") to .Range("A3", "G3") ...and so on.

This should work.

Upvotes: 2

Related Questions