Excel Loop VBA incrementing rows

Question

Hi I have a section of code that I am trying to write a loop for but I am struggling with how to go about it.

This portion of the code runs fine. But I actually have 4 cells which are the ranges, C26, C91, C156 and C221. (See Container 1 comment in code)

I managed to get it to loop, but then my references below (e.g B33,C33,D33,etc.) just write over the top. Is there anyway to write a loop that can increment all of the successive code by the required 65 rows??

I really want to learn how to do this properly rather than copying and pasting 4 times and manually updating the references!

Private Sub RunStabSetup()


' Confirmation of Entry to Form

If MsgBox("Have you double checked your data is correct and ALL test points have been selected before entering on the spreadsheet?", vbYesNo) = vbNo Then Exit Sub

Application.ScreenUpdating = False

Application.Worksheets("Req Sheet").Range("C83") = " "

If Container1CB.Value > "" Then

'Container 1

    Application.Worksheets("StabDataCapture").Range("C26") = Container1CB

  '60° CheckBox logic statements

    If W1T60.Value = True Then Application.Worksheets("StabDataCapture").Range("B33") = "1"
    If W1T60.Value = False Then Application.Worksheets("StabDataCapture").Range("B33") = ""

    If W2T60.Value = True Then Application.Worksheets("StabDataCapture").Range("C33") = "2"
    If W2T60.Value = False Then Application.Worksheets("StabDataCapture").Range("C33") = ""

    If W3T60.Value = True Then Application.Worksheets("StabDataCapture").Range("D33") = "3"
    If W3T60.Value = False Then Application.Worksheets("StabDataCapture").Range("D33") = ""

    If W4T60.Value = True Then Application.Worksheets("StabDataCapture").Range("E33") = "4"
    If W4T60.Value = False Then Application.Worksheets("StabDataCapture").Range("E33") = ""

    If W5T60.Value = True Then Application.Worksheets("StabDataCapture").Range("F33") = "5"
    If W5T60.Value = False Then Application.Worksheets("StabDataCapture").Range("F33") = ""

    If W6T60.Value = True Then Application.Worksheets("StabDataCapture").Range("G33") = "6"
    If W6T60.Value = False Then Application.Worksheets("StabDataCapture").Range("G33") = ""

    If W7T60.Value = True Then Application.Worksheets("StabDataCapture").Range("H33") = "7"
    If W7T60.Value = False Then Application.Worksheets("StabDataCapture").Range("H33") = ""

    If W8T60.Value = True Then Application.Worksheets("StabDataCapture").Range("I33") = "8"
    If W8T60.Value = False Then Application.Worksheets("StabDataCapture").Range("I33") = ""
 End If

End Sub

Thanks for you help everyone!

Will the W1T60 values keep incrementing too? I assume that you want B33 = 1, C33 = 2, and if we went to like, Z33, you'd want Z33 = 25? — BruceWayne
– BruceWayne, Commented Sep 28, 2016 at 14:05

tretom · Accepted Answer · 2016-09-28 14:27:58Z

1

I'd make it like:

i=2
do while i<= maxColumn 
        If W1T60.Value = True Then Application.Worksheets("StabDataCapture").Cells(i,33).Value2 = i-1
        If W1T60.Value = False Then Application.Worksheets("StabDataCapture").Cells(i,33).Value2 = ""
loop

from your code I don't see how to change the Cells(i,j) parameter, so I left it constant, but with similar logic you can modify it

edited Sep 28, 2016 at 14:27

answered Sep 28, 2016 at 14:20

tretom

6451 gold badge9 silver badges18 bronze badges

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

Comments

J_Lard · Accepted Answer · 2016-09-28 15:34:48Z

There are a few different ways you can do this using for loops and the offset feature. I would probably do it by first defining your range as an array of ranges. Dim rng(0 to 3) as Range and then defining each of your 4 cells in column C.

Set rng(0) = Range("C26")
Set rng(1) = Range("C91")
Set rng(2) = Range("C156")
Set rng(3) = Range("C221")

Then you can enclose your "if" statement in a for each loop.

Dim c As Variant
For Each c In rng
    if Container1CB.Value > "" Then

        Sheets("StabDataCapture").c.Value = Container1CB

        If W1T60.Value = True Then Sheets("StabDataCapture").c.Offset(7,-1).Value = "1"
        If W1T60.Value = False Then sheets("StabDataCapture").c.Offset(7,-1).Value = ""

        If W2T60.Value = True Then sheets("StabDataCapture").c.Offset(7,0).Value = "2"
        If W2T60.Value = False Then sheets("StabDataCapture").c.Offset(7,0).Value = ""

....

end if

Alternatively, you could use a for loop like For i = 0 to 65*4 Step 65 and you could replace statements like Range("C26") with Cells(i,3).Value

To set each value in your "IF-THEN" statements, your best solution again is probably an array. Dim WT(1 To 8) as Variant and then you can set each value of the array equal to the value of W1T60, W2T60, etc. WT(1) = W1T60.Value. Then the code can be updated to:

Dim c As Variant
Dim i as Integer
For Each c In rng
    if Container1CB.Value > "" Then

        Sheets("StabDataCapture").c.Value = Container1CB

        For i = 1 To 8
            If WT(i) Then 
                Sheets("StabDataCapture").c.Offset(7, i - 2).Value = i
            else
                Sheets("StabDataCapture").c.Offset(7, i - 2).Value = ""
            end if
        next i

    End If
Next

Collectives™ on Stack Overflow

Excel Loop VBA incrementing rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related