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 — John Coleman
– John Coleman, Commented Jul 21, 2021 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
– Tim Williams, Commented Jul 21, 2021 at 15:22

Tim Williams · Accepted Answer · 2021-07-21 16:14:01Z

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, 2021 at 16:14

answered Jul 21, 2021 at 15:29

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

actuarial.codes Over a year ago

Hi, I am getting an error "Invalid use of property" and it highlights .Offset in wsCode.Range("C2").Offset((i - 1) * 13)

Tim Williams Over a year ago

That copy operation is one long line with a _ continuation - did you include that ?

actuarial.codes Over a year ago

That worked like a charm. Thanks so much, I really appreciate the help!! :) :)

actuarial.codes Over a year ago

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 Over a year ago

Thank you so much you're a savior! Would you have any book/resource recommendations to help me learn vba better?

|

Collectives™ on Stack Overflow

vba nested for loop

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related