CODEWITHSUNDEEP
excelvba
wasif ahmed
wasif ahmed

Reputation: 41

want to make multiple if statement in VBA

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. Do check If statement for check13 as there is little difference in picking up the array.

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

If check13 Then
    s1.Range("C45").Value = s2.Range("B11").Value
Else
    s1.Range("C45").Value = vbNullString

End If
End If
End If
End If
End If

End Sub

Upvotes: 1

Views: 907

Answers (3)

BigBen
BigBen

Reputation: 50008

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

EDIT:

Since the ranges on s2 are not contiguous, use two arrays:

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

With s2
    Dim ranges As Variant
    ranges = Array(.Range("B2"), _
                   .Range("B3"), _
                   .Range("B4"), _
                   .Range("B5"), _
                   .Range("B11"))
End With


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 = ranges(i).Value
    Else
        s1.Range("C42").Offset(i).Value = vbNullString
    End If
Next

Upvotes: 2

Halil Ibrahim Özcan
Halil Ibrahim Özcan

Reputation: 141

I would suggest a Select-Case (switch-case in C#) instead of If-Else, as a form of pattern matching. See: Select Case statement

Upvotes: 0

Pepe N O
Pepe N O

Reputation: 2344

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

Upvotes: 1

Related Questions