excel vba - loop down column with 4 cell range

Question

Basically I'm trying to write a loop - that on first iteration will have

srcT = Worksheets("I").Range("B1").Value
srcC = Worksheets("I").Range("B2").Value
trgT = Worksheets("I").Range("B3").Value
trgC = Worksheets("I").Range("B4").Value

then 2nd iteration

srcT = Worksheets("I").Range("B5").Value
srcC = Worksheets("I").Range("B6").Value
trgT = Worksheets("I").Range("B7").Value
trgC = Worksheets("I").Range("B8").Value

then 3rd iteration

srcT = Worksheets("I").Range("B9").Value
srcC = Worksheets("I").Range("B10").Value
trgT = Worksheets("I").Range("B11").Value
trgC = Worksheets("I").Range("B12").Value

and continue with this pattern until a blank cell is encountered in column B.

Why you do you even need to loop, you are just overwriting the variables and not working with them, why not just find the last blank cell and populate the variables from there? — Reafidy, Oct 06 '11 at 19:54

score 1 · Answer 1 · answered Oct 06 '11 at 15:43

There are many ways to write a loop; this is one:

Option Explicit

Sub Test()

    Dim srcT As String
    Dim srcC As String
    Dim trgT As String
    Dim trgC As String

    Dim counter As Long

    Const ADD_FACTOR = 4
    Const WORKSHEET_NAME = "I"
    Const COLUMN_TO_USE = "B"


    Dim blankFound As Boolean

    blankFound = False

    counter = 1
    Do
        srcT = Worksheets(WORKSHEET_NAME).Range(COLUMN_TO_USE & counter).Value
        srcC = Worksheets(WORKSHEET_NAME).Range(COLUMN_TO_USE & counter + 1).Value
        trgT = Worksheets(WORKSHEET_NAME).Range(COLUMN_TO_USE & counter + 2).Value
        trgC = Worksheets(WORKSHEET_NAME).Range(COLUMN_TO_USE & counter + 3).Value

        blankFound = srcT = "" Or srcC = "" Or trgT = "" Or trgC = ""

        counter = counter + ADD_FACTOR

    Loop While blankFound = False


End Sub

Note there is declaration for each variable and the use Constants for readability.

score 1 · Answer 2 · edited May 23 '17 at 12:19

Sub LoopExample()
Dim i as Integer
    With Worksheets("I")
        For i = 1 to 3
            srcT = .Cells(i, 2).Value
            srcC = .Cells(i + 1, 2).Value
            trgT = .Cells(i + 2, 2).Value
            trgC = .Cells(i + 3, 2).Value
        Next i
    End With
End Sub

Note the use of With to optimize VBA performance when selecting object.

Please take care next time to:

ask a real question - see the faq
search a little bit more about your question on the web, you can really find much about VBA loop

[EDIT] As @Readify pointed out in his comment, I forgot to answer the "last row issue". Here is a thread (where readify made a very clear answer btw) that could help you: Getting the actual usedrange

+1 nicely laid out. @Toop for longer loops you should consider using variant arrays in conjunction with ranges for speed — brettdj, Oct 06 '11 at 23:43

excel vba - loop down column with 4 cell range

2 Answers2