Nested For Next loops not working
Nested For Next loops not working
(OP)
Here is the code as entered into my MACRO. When I Run the macro the For q = loop will not repeat. The For p = loop will cycle as many times as required, the last 3 cell inputs verify the values of q, p and the cell value in D14 of "Price list". Can anyone spot a flaw in the logic of my MACRO or is it impossible to nest for next loops.
Any help is appreciated.
Dim q As Long
Dim p As Long
Dim t As Long
For q = 1 To Worksheets("Price list").Range("d14")
For p = 1 To Worksheets("price list").Range("Cutlist_elements")(1, q)
t = t + 1
Cells(t + 3, 1) = "input data"
Cells(t + 3, 2) = "input data"
Cells(t + 3, 4) = "input data"
Cells(t + 3, 6) = "input data"
Cells(t + 3, 9) = "input data"
Cells(t + 3, 10) = q {confirmation for debugging}
Cells(t + 3, 11) = p {confirmation for debugging}
Cells(t + 3, 12) = Worksheets("price list").Range("d14") {confirmation for debugging}
Next p
Next q
End Sub
Any help is appreciated.
Dim q As Long
Dim p As Long
Dim t As Long
For q = 1 To Worksheets("Price list").Range("d14")
For p = 1 To Worksheets("price list").Range("Cutlist_elements")(1, q)
t = t + 1
Cells(t + 3, 1) = "input data"
Cells(t + 3, 2) = "input data"
Cells(t + 3, 4) = "input data"
Cells(t + 3, 6) = "input data"
Cells(t + 3, 9) = "input data"
Cells(t + 3, 10) = q {confirmation for debugging}
Cells(t + 3, 11) = p {confirmation for debugging}
Cells(t + 3, 12) = Worksheets("price list").Range("d14") {confirmation for debugging}
Next p
Next q
End Sub
Talk To Other Members
RE: Nested For Next loops not working
Cheers
Greg Locock
RE: Nested For Next loops not working
The (1,q) is similar to the Offset method, but with the difference that it is a relative reference: (1,q) refers to the same row and the q-th column. So it is equal to .Offset(0,q-1)
bpeirson,
I got your code to work without modifications. The error I made when setting up the spreadsheet is that the range "cutlist_elements" must be in a horizontal range (I first set it up in a column).
The other thing that happens with your code is that you'll quickly overwrite range [D14], dependent on the value of q and the values in Cutlist_elements.
Remember that the (1,q) notation, as well as the Cells(t+3,1) etc, is always (row, column).
So your code is ok, it seems.
The spreadsheet I made looks like:
Column A B C D E
Row14 q = 3
Row15
Row16 1 4 2 4 5
with the name Cutlist_elements defined as:
='Price list'!$A$16:$E$16
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Nested For Next loops not working
For some reason I transposed the (1,q). Should be (q,1) for the configuration of my data.
Overwriting D14 will not be a problem because this macro does not write data to the Price list worksheet.
The loops work fine now but the information written to the cells is not what was expected. But that's a different problem.