Solving for multiple Nexts

Question

Having trouble unnesting a function that has multiple variables within the loop that needs to change. I have data that is housed into a a few rows but an annoying amount of columns. I am taking the data and stacking it on top of each other so it can be manipulated more. I need a way to change both variables y & n on the same next otherwise it gets stuck into an infinite loop and crashes.

I want the y's to continue on a normal step but need the n's to change with them, otherwise the data is just overwriting and it's repeating itself.

Sub pasteanswers()
Dim LastCol As Integer
Dim y As Integer
Dim x As Integer
Dim n As Integer
Dim v As Integer



With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column - 7
        r2s = (LastCol / 2)

End With

For x = 6 To 28
For n = 1 To r2s
For y = 6 To 1721

Cells(y, 8).Value = Cells(x, ((2 * n + 8))).Value
Cells(y, 9).Value = Cells(x, ((2 * n) + 9)).Value



Next y
Next n
Next x


End Sub

Maybe get rid of the loop and increment...` n=n+1` before the next y — Davesexcel, Apr 07 '16 at 18:15
this sounds like a good solution! Can you elaborate a bit further or provide a reference of where i can find this information? — Asim Ahmed, Apr 07 '16 at 18:24
Nothing really to elaborate, if you removed your `n` loop, set `n=0` before the loops and `n=n+1` before the `next y`,"n" would then increase by 1 every time `y` loops. — Davesexcel, Apr 07 '16 at 21:24
the issue with that is that they are different ranges so `n` would not reset when it needs to — Asim Ahmed, Apr 11 '16 at 18:45

score 0 · Answer 1 · answered Apr 07 '16 at 19:00

If you need n and y to be equal, eliminate n and use y in it's place. Without example data and expected results, it's hard to test it for you ...

Sub pasteanswers()
Dim LastCol As Integer
Dim y As Integer
Dim x As Integer
Dim v As Integer

With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column - 7
        r2s = (LastCol / 2)

End With

For x = 6 To 28
For y = 6 To r2s

Cells(y, 8).Value = Cells(x, ((2 * y + 8))).Value
Cells(y, 9).Value = Cells(x, ((2 * y) + 9)).Value

Next y
Next x

End Sub

unfortunately they are not on the same scale. i'm trying to figure out if an exit for — Asim Ahmed, Apr 07 '16 at 20:58

score 0 · Answer 2 · answered Apr 08 '16 at 07:21

0

For n = 1 To r2s
    y = n + 5
    If y > 1721 Then Exit For

    Cells(y, 8).Value = Cells(x, ((2 * n + 8))).Value
    Cells(y, 9).Value = Cells(x, ((2 * n) + 9)).Value

Next n

answered Apr 08 '16 at 07:21

Vegard

3,587
2
22
40

Solving for multiple Nexts

2 Answers2