Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next
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 -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|