Reputation: 93

VBA - Excessive use of IF statements

Please keep in mind that I am a starting VBA developer.

I've got a big pile of IF statements and I'm 100% sure that this can and must be shorter, but I do not have the knowledge of how to do so.

If you do answer my question, could you explain the answer with it aswell?

Here's my code:

If Sheets("Tab 1 - Prijslijst").Range("DK" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("W" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DL" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("X" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DM" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("Y" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DN" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("Z" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DO" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AA" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DP" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AB" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DQ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AC" & xlCell2.Row).Value Then
    If Sheets("Tab 1 - Prijslijst").Range("DR" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AD" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DS" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AE" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DT" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AF" & xlCell2.Row).Value Then
        If Sheets("Tab 1 - Prijslijst").Range("DU" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AG" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DV" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AH" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DW" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AI" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DX" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AJ" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DY" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AK" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DZ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AL" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("EA" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AM" & xlCell2.Row).Value Then
            If Sheets("Tab 1 - Prijslijst").Range("EB" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AN" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("EC" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AO" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("ED" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AP" & xlCell2.Row).Value Then
                If Sheets("Tab 1 - Prijslijst").Range("CQ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("C" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CR" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("D" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CS" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("E" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CT" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("F" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CU" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("G" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CV" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("H" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CW" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("I" & xlCell2.Row).Value Then
                    If Sheets("Tab 1 - Prijslijst").Range("CX" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("J" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CY" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("K" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CZ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("L" & xlCell2.Row).Value Then
                        If Sheets("Tab 1 - Prijslijst").Range("DA" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("M" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DB" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("N" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DC" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("O" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DD" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("P" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DE" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("Q" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DF" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("R" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DG" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("S" & xlCell2.Row).Value Then
                            If Sheets("Tab 1 - Prijslijst").Range("DH" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("T" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DI" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("U" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DJ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("V" & xlCell2.Row).Value Then

                            '----------
                            ' Code that is irrelevant to the question
                            '----------

                            Else
                                Call ntofourty(xlCell3, xlCell2)
                            End If
                        Else
                            Call ntofourty(xlCell3, xlCell2)
                        End If
                    Else
                        Call ntofourty(xlCell3, xlCell2)
                    End If
                Else
                    Call ntofourty(xlCell3, xlCell2)
                End If
            Else
                Call ntofourty(xlCell3, xlCell2)
            End If
        Else
            Call ntofourty(xlCell3, xlCell2)
        End If
    Else
        Call ntofourty(xlCell3, xlCell2)
    End If
Else
    Call ntofourty(xlCell3, xlCell2)
End If

Upvotes: 0

Answers (7)

John Alexiou

Reputation: 29244

I like to put the checks in a separate function. Say something like this:

Public Function MatchColumns(ByRef tab1_cols() As Variant, ByRef tab2_cols() As Variant, ByVal row_3 As Long, row_2 As Long) As Boolean
    Dim tab1 As Worksheet, tab2 As Worksheet
    Set tab1 = Sheets("Tab 1 - Prijslijst")
    Set tab2 = Sheets("Tab 2 - Nieuwe prijzen")

    Dim n As Long, i As Long
    ' Count elements in array
    n = UBound(tab1_cols) - LBound(tab1_cols) + 1   
    For i = 1 To n
        If tab1.Range(tab1_cols(i - 1) & ":" & tab1_cols(i - 1)).Cells(row_3, 1).Value <> tab2.Range(tab2_cols(i - 1) & ":" & tab2_cols(i - 1)).Cells(row_2, 1).Value Then
            MatchColumns = False
        End If
    Next i
    MatchColumns = True
End Function

Then your calling code becomes almost trivial

Dim tab1_cols() As Variant, tab2_cols() As Variant
tab1_cols = Array("DK", "DL", "DM", "DN", "DO", "DP", "DQ", ...)
tab2_cols = Array("W", "X", "Y", "Z", "AA", "AB", "AC", ...)

If MatchColumns(tab1_cols, tab2_cols, xlCell3.Row, xlCell2.Row) Then
    '----------
    ' Code that is irrelevant to the question
    '----------
Else
    Call ntofourty(xlCell3, xlCell2)
End If

Upvotes: 0

Darren Bartrup-Cook

Reputation: 19782

You could push each row to a separate function and check the values there.
This code will check the values in the range 'Tab 1 - Prijslijst'!CQ2:ED2 against the values in 'Tab 2 - Nieuwe prijzen'!D9:AQ17.

If any of the cells in the row are different then it will put Colour Cell. in the immediate window, otherwise it will put Call function.

Public Sub Test()

    Dim lRow As Long
    Dim lTarget As Long
    Dim x As Long

    Dim rSource As Range
    Dim rTarget As Range

    lTarget = 9 'First row that data appears on in "Tab 2 - Nieuwe prijzen"
    x = 0 'Used in loop to move down rows in "Tab 2 - Nieuwe prijzen"

    'lRow is the row number on "Tab 1 - Prijslijst"
    For lRow = 2 To 10

        'Set the ranges for the specific row.
        Set rSource = ThisWorkbook.Worksheets("Tab 1 - Prijslijst").Range("CQ" & lRow & ":ED" & lRow)
        Set rTarget = ThisWorkbook.Worksheets("Tab 2 - Nieuwe prijzen").Range("D" & lTarget + x & ":AQ" & lTarget + x)

        'Check the cell values in the row and flow code accordingly.
        If CellValuesAreTheDifferent(rSource, rTarget) Then
            Debug.Print "Colour Cell."
        Else
            Debug.Print "Call function."
        End If

        x = x + 1

    Next lRow

End Sub

Public Function CellValuesAreTheDifferent(rSource As Range, rTarget As Range) As Boolean

    Dim y As Long

    'Check each cell value in the passed range.
    For y = 1 To rSource.Cells.Count
        'If the cell values are different then set to TRUE and exit the function.
        If rSource.Cells(y) <> rTarget.Cells(y) Then
            CellValuesAreTheDifferent = True
            Exit For
        End If
    Next y

End Function

Upvotes: 0

Umair Mehmood

Reputation: 36

Try this code, modify it according to your need:

Dim xranges As Variant, yranges As Variant, countranges As Long
xranges = Array("DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY")
yranges = Array("W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL")
countranges = 0
For i = 0 To UBound(xranges)
    If Sheets("Tab 1 - Prijslijst").Range(xranges(i) & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range(yranges(i) & xlCell2.Row).Value Then
        countranges = countranges + 1
    End If
next i

If countranges = UBound(xranges) + 1 Then
    Call ntofourty(xlCell3, xlCell2)
End If

Upvotes: 0

Marcucciboy2

Reputation: 3259

Okay so my answer is meant to build on @SBF's answer. We had pretty similar ideas but their answer does not loop through each range you wanted to check, so I put the loop in a function to be called by each iteration of if's you have. Hope it helps.

Sub test()

    Dim ws1 As Worksheet: Set ws1 = Worksheets("Tab 1 - Prijslijst")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Tab 2 - Nieuwe prijzen")

    Dim xlcell2 As Range: Set xlcell2 = ws1.Range("A1")
    Dim xlcell3 As Range: Set xlcell3 = ws2.Range("A1")

    Dim row1 As Long: row1 = xlcell3.Row
    Dim row2 As Long: row2 = xlcell2.Row

    'each range you want to check
    Dim range1a As Range: Set range1a = ws1.Range("DK" & row1 & ":" & "DQ" & row1)
    Dim range1b As Range: Set range1b = ws2.Range("W" & row2 & ":" & "AC" & row2)

    Dim range2a As Range: Set range2a = ws1.Range("DR" & row1 & ":" & "DT" & row1)
    Dim range2b As Range: Set range2b = ws2.Range("AD" & row2 & ":" & "AF" & row2)

    '...and so on


    Dim innerMostCodeExecuted As Boolean

    If CheckRangeEqual(range1a, range1b, row1, row2) = True Then
        If CheckRangeEqual(range2a, range2b, row1, row2) = True Then
            '...and so on

            innerMostCodeExecuted = True
        End If
    End If

    If innerMostCodeExecuted = False Then ntofourty xlcell3, xlcell2

End Sub


Function CheckRangeEqual(range1 As Range, range2 As Range, row1 As Long, row2 As Long) As Boolean

    Dim areEqual As Boolean: areEqual = True

    Dim currentCol As Long

    For currentCol = 1 To range1.Columns.count - 1
        If range1.Cells(row1, currentCol).Value <> range2.Cells(row2, currentCol).Value Then
            areEqual = False
            Exit For
        End If
    Next

    CheckRangeEqual = areEqual

End Function

Since I had to make some mock data for testing, I gave xlcell2 and xlcell3 a value here, but to adapt this to yours all you'd have to do is include the other ranges and nest them in the ifs. Since there's no discernible pattern (that I can see) between what's being checked it still has to be put in manually.

Upvotes: 0

SBF

Reputation: 1369

I think you need something like this:
col1 is the range in "Tab 1 - Prijslijst"
col2 is the start of the range in "Tab 2 - Nieuwe prijzen"
If you wan to change the ranges, modify the Range("C:C").Column parts

Dim ws1 As Worksheet: Set ws1 = Worksheet("Tab 1 - Prijslijst")
Dim ws2 As Worksheet: Set ws2 = Worksheet("Tab 2 - Nieuwe prijzen")

Dim row1 As Integer: row1 = xlCell3.row
Dim col1 As Integer
Dim row2 As Integer: row2 = xlCell2.row
Dim col2 As Integer: col2 = Range("C:C").Column

innerMostCodeExecuted = True

For col1 = Range("CQ:CQ").Column To Range("EA:EA").Column
    If ws1.Cells(xlCell3.row, col1).Value <> ws2.Cells(xlCell2.row, col2).Value Then
        innerMostCodeExecuted = False
        Exit For
    End If
    col2 = col2 + 1
Next

Upvotes: 0

David Rushton

Reputation: 5030

This change would save you 14 lines.

Dim innerMostCodeExecuted As Boolean    ' Default value of a bool is false.

If Sheets("Tab 1 - Prijslijst").Range("DK" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("W" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DL" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("X" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DM" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("Y" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DN" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("Z" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DO" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AA" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DP" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AB" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DQ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AC" & xlCell2.Row).Value Then
    If Sheets("Tab 1 - Prijslijst").Range("DR" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AD" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DS" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AE" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DT" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AF" & xlCell2.Row).Value Then
        If Sheets("Tab 1 - Prijslijst").Range("DU" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AG" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DV" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AH" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DW" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AI" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DX" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AJ" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DY" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AK" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DZ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AL" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("EA" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AM" & xlCell2.Row).Value Then
            If Sheets("Tab 1 - Prijslijst").Range("EB" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AN" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("EC" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AO" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("ED" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("AP" & xlCell2.Row).Value Then
                If Sheets("Tab 1 - Prijslijst").Range("CQ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("C" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CR" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("D" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CS" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("E" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CT" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("F" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CU" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("G" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CV" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("H" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CW" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("I" & xlCell2.Row).Value Then
                    If Sheets("Tab 1 - Prijslijst").Range("CX" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("J" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CY" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("K" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("CZ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("L" & xlCell2.Row).Value Then
                        If Sheets("Tab 1 - Prijslijst").Range("DA" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("M" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DB" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("N" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DC" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("O" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DD" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("P" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DE" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("Q" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DF" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("R" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DG" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("S" & xlCell2.Row).Value Then
                            If Sheets("Tab 1 - Prijslijst").Range("DH" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("T" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DI" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("U" & xlCell2.Row).Value And Sheets("Tab 1 - Prijslijst").Range("DJ" & xlCell3.Row).Value = Sheets("Tab 2 - Nieuwe prijzen").Range("V" & xlCell2.Row).Value Then

                                ' Set to true, so we know this block was executed.
                                innerMostCodeExecuted = True

                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If


' Remove repeated else blocks.
If innerMostCodeExecuted = False Then ntofourty xlCell3, xlCell2

Upvotes: 0

MyName

Reputation: 370

You might want to check out select case. This takes input (cell DK3 nogwat) and compares it to different cases.

Sub selectcase()

Dim var As Range
Dim wSheet As Worksheet 'make some for the other worksheets as well

Set wSheet = ActiveSheet
Set var = wSheet.Range("DK3")

    Select Case var.Value 'insert variable (or range) to test DK3 in this case
    Case wSheet.Range("input range") 'check to see if it matches the value in sheet 3, cell ...
         call ...  'output, modify this to your use
    Case wSheet.Range("I18")
         MsgBox "It's I18"
    Case wSheet.Range("I19")
         MsgBox "It's I19"
    Case Else
         MsgBox "It's none"
End Select

End Sub

Because of the complexity of your code I don't quite get what your doing, so I don't know if you can use this precisely as you want.

Upvotes: 1

VBA - Excessive use of IF statements

Answers (7)

Related Questions