For next

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Good morning all... I've got nested (For Next) statements
that I can't seem to get to loop through the code with a
counter... I can achieve my goal by setting the Testname,
NameRange, Startdate and Enddate to reference cell (1,1),
then copying the (For Each c) statement, changing the Cell
references to (2,1), (3,1) and (4,1)... This seems like
alot of code...

To make it neat and easy to change for future use, I'd
like to use the For a = 1 to 4 statement to loop through
and change the cell reference each time by 1... Is this
possible?? The below code only seems to loop through 1
time...

Thanks in advance
Kevin...

Sub Button5_Click()
Set Testname = Worksheets("Sheet4").Range("a4:a7")
Set NameRange = Worksheets("Sheet2").Range("A1:A4")
Set DateRange = Worksheets("Sheet4").Range("C1:O1")
Set Startdate = Worksheets("Sheet2").Range("F1:F4")
Set Enddate = Worksheets("Sheet2").Range("G1:G4")
For a = 1 To 4
For Each c In [DateRange]
If c.Value >= Startdate(a, 1) And c.Value <=
Enddate(a, 1) Then
If NameRange(a, 1) = Testname(a, 1) Then
Cells(Testname.Row,
c.Column).Interior.ColorIndex = 35
End If
End If
Next c
Next a
End Sub
 
Your For Next code executes 4 times for me. If you havn't
already used a MsgBox to test the execution then I suggest
the following. Insert this line immediately after the For
a = 1 to 4 line.

MsgBox StartDate(a, 1).Address & " " & _
EndDate(a, 1).Address & " " & NameRange(a, 1).Address

Regards,
Greg
 
Back
Top