want to make multiple if statement in VBA-CodePudding

how could I run this multiple If statement it only run the first if statement (as it is true) but I want the Programm to go for all the If statements step by step.

Sub main_process_data()
   Dim s1 As Worksheet, s2 As Worksheet
   Dim check1 As Boolean, check5 As Boolean, check6 As Boolean, check7 As Boolean, check13 As Boolean

Set s1 = ThisWorkbook.Worksheets(1)
Set s2 = ThisWorkbook.Worksheets(2)

check1 = s2.CHECKBOXES("Check Box 1").Value = xlOn
check5 = s2.CHECKBOXES("Check Box 5").Value = xlOn
check6 = s2.CHECKBOXES("Check Box 6").Value = xlOn
check7 = s2.CHECKBOXES("Check Box 7").Value = xlOn
check13 = s2.CHECKBOXES("Check Box 13").Value = xlOn

If check1 Then
    s1.Range("C42").Value = s2.Range("B2").Value
Else
    s1.Range("C42").Value = vbNullString

If check5 Then
    s1.Range("C43").Value = s2.Range("B3").Value
Else
    s1.Range("C43").Value = vbNullString

If check6 Then
    s1.Range("C44").Value = s2.Range("B4").Value
Else
    s1.Range("C44").Value = vbNullString
    
If check7 Then
    s1.Range("C45").Value = s2.Range("B5").Value
Else
    s1.Range("C45").Value = vbNullString


End If
End If
End If
End If

End Sub

CodePudding user response：

To run each if one by one it should be like this:

Sub main_process_data()
   Dim s1 As Worksheet, s2 As Worksheet
   Dim check1 As Boolean, check5 As Boolean, check6 As Boolean, check7 As Boolean, check13 As Boolean

Set s1 = ThisWorkbook.Worksheets(1)
Set s2 = ThisWorkbook.Worksheets(2)

check1 = s2.CHECKBOXES("Check Box 1").Value = xlOn
check5 = s2.CHECKBOXES("Check Box 5").Value = xlOn
check6 = s2.CHECKBOXES("Check Box 6").Value = xlOn
check7 = s2.CHECKBOXES("Check Box 7").Value = xlOn
check13 = s2.CHECKBOXES("Check Box 13").Value = xlOn

If check1 Then
    s1.Range("C42").Value = s2.Range("B2").Value
Else
    s1.Range("C42").Value = vbNullString
End If

If check5 Then
    s1.Range("C43").Value = s2.Range("B3").Value
Else
    s1.Range("C43").Value = vbNullString
End If

If check6 Then
    s1.Range("C44").Value = s2.Range("B4").Value
Else
    s1.Range("C44").Value = vbNullString
End If

If check7 Then
    s1.Range("C45").Value = s2.Range("B5").Value
Else
    s1.Range("C45").Value = vbNullString
End If

End Sub

CodePudding user response：

Using a loop and Offset:

Dim checks As Variant
checks = Array(check1, check5, check6, check7, check13)

Dim i As Long
For i = Lbound(checks) to Ubound(checks)
    If checks(i) Then
        s1.Range("C42").Offset(i).Value = s2.Range("B2").Offset(i).VAlue
    Else
        s1.Range("C42").Offset(i).Value = vbNullString
    End If
Next

Even better, skip creating the check variables:

Dim checkNums As Variant
checkNums = Array(1, 5, 6, 7, 13)

Dim i As Long
For i = Lbound(checkNums) to Ubound(checkNums)
    If s2.CHECKBOXES("Check Box " & checkNums(i)).Value = xlOn Then
        s1.Range("C42").Offset(i).Value = s2.Range("B2").Offset(i).VAlue
    Else
        s1.Range("C42").Offset(i).Value = vbNullString
    End If
Next