vba nested for loop

Question

I am still in the learning process so I need a little help with this.

I have this code

worksheets("List").Activate

Range("B2").Activate

    For i = 0 To 500
        ActiveCell.Offset(i, 0).Select
        Selection.Copy
        
        For j = 0 To 8000
        
            Worksheets("Code").Activate
            Range("C2").Activate
        
            ActiveCell.Offset(j, 0).Range("A1").Select
            ActiveSheet.Paste
            
            j = j + 13
    
        Next j
    
    Next i

End Sub

so basically what I am trying to do is copy a cell from the List worksheet onto another cell on the Code worksheet. But I want it to skip 13 cells every time when it copies a cell over to the Code worksheet. I think there is an issue with the way I nested it, so I want it to copy i from List worksheet, paste it onto j on the Code worksheet, then go back, copy the next i from the List worksheet, then paste it onto (j + 13)th cell on the Code sheet and loop until all i values are copied over. Can someone help correct the loop nesting for me I would really appreciate it! :)

I think what I have is copying the same i value onto j + 13 everytime, which is not what I want.

If you are still in the learning process, you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248) — John Coleman, Jul 21 '21 at 15:17
`For j = 0 To 8000 Step 13` would be much better than altering `j` inside the loop - that is a no-no when using a `For...Next` loop. — Tim Williams, Jul 21 '21 at 15:22

Tim Williams · Accepted Answer · 2021-07-21T16:14:01.177

1

It's a little difficult to interpret the code you have, but something like this may be close:

Sub Tester()
    Dim wsList As Worksheet, wsCode As Worksheet, i As Long
    
    Set wsList = Worksheets("List")
    Set wsCode = Worksheets("Code")
    
    For i = 1 To 500
        'copy/paste
        wsList.Range("B2").Offset(i - 1).Copy _
            wsCode.Range("C2").Offset((i - 1) * 13)

        'or copy value only (faster)
        wsCode.Range("C2").Offset((i - 1) * 13).Value = _
            wsList.Range("B2").Offset(i - 1).Value

    Next i
End Sub

No Activate/Select required

edited Jul 21 '21 at 16:14

answered Jul 21 '21 at 15:29

Tim Williams

154,628
8
97
125

Hi, I am getting an error "Invalid use of property" and it highlights .Offset in wsCode.Range("C2").Offset((i - 1) * 13) – actuarial.codes Jul 21 '21 at 15:38
That copy operation is one long line with a `_` continuation - did you include that ? – Tim Williams Jul 21 '21 at 15:44
That worked like a charm. Thanks so much, I really appreciate the help!! :) :) – actuarial.codes Jul 21 '21 at 15:51
one more quick question. I am trying to do the exact same thing to another cell but in this case the cell im copying has a formula and I want to only paste the value, not the formula. How can I modify the code to do that? – actuarial.codes Jul 21 '21 at 16:12
Thank you so much you're a savior! Would you have any book/resource recommendations to help me learn vba better? – actuarial.codes Jul 21 '21 at 16:18
I've not read a VBA book for many years, but I started with the Excel 95 edition of this: https://www.amazon.com/Excel-2019-Power-Programming-VBA/dp/1119514924 I thought it was a good introduction. Also like this: https://www.amazon.com/Professional-Excel-Development-Definitive-Applications/dp/0321508793 – Tim Williams Jul 21 '21 at 16:20

vba nested for loop

1 Answers1