How to loop through the same cells in specified columns

Question

I wrote the following VBA to loop through the same cells in each column from column 2 to 1002. That is, in the coding, when i=2, I have Range("B3", "B1298"); when i=3, I need to have have Range("C3", "C1298"), and so on. How to achieve this, please? Thank you.

Sub ForwardRScen()

    Application.ScreenUpdating = False

    For i = 2 To 102

        Range("ChgBP").Value = Sheets("sth").Cells(2, i).Value
        Sheets("sth").Range("B3", "B1298").Value = Range("DResults").Value

    Next i

    Application.ScreenUpdating = True

End Sub

Sheets("sth").Range("B3", "B1298").Offset(0,i).Value = Range("DResults").Value and adjust i accordingly — Banana
– Banana, Commented Jun 5, 2018 at 10:47

Vityata · Accepted Answer · 2018-06-05 10:57:32Z

2

Assigning a range and changing it in the loop is a good way to go:

Public Sub TestMe()

    Dim i As Long
    For i = 2 To 12
        Dim myRange As Range
        Set myRange = Range(Cells(2, i), Cells(1298, i))
        myRange = i
    Next i

End Sub

This is what you will get:

edited Jun 5, 2018 at 10:57

answered Jun 5, 2018 at 10:47

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

Collectives™ on Stack Overflow

How to loop through the same cells in specified columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related