CODEWITHSUNDEEP
excelvbaloops
vividillusion
vividillusion

Reputation: 99

Excel Loop VBA incrementing rows

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!

Upvotes: 0

Views: 823

Answers (2)

J_Lard
J_Lard

Reputation: 1103

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

Upvotes: 0

tretom
tretom

Reputation: 635

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

Upvotes: 1

Related Questions