CODEWITHSUNDEEP
excelvbaif-statement
Marc Hipolito
Marc Hipolito

Reputation: 9

How to consolidate If Or then statements, too many lines

This is what I have so far and it reaches a point where it is too many lines so I made another IF else and no results anymore. Is there a way to consolidate all of this in vba or am I doing this wrong.

Dim Run_Medallions() As Long                                                                    'Medallions used in each run
ReDim Run_Medallions(RunCnt)                                                                    'Medallions used in each run
For i = 1 To RunCnt
         If Left(Ip.Cells(3, i + 2), 7) = "VMCCHAR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMCBDXB" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMEBDOR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMEPIOR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMEROOR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMESAOR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPBDXB" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPCHAR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPGEWZ" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPMERL" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPMEOR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPSAOR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPPGRI" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPPINO" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPRIES" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPROSE" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMPSAUV" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMCSAUV" Or _
            Left(Ip.Cells(3, i + 2), 3) = "RSX" Then
            
            '# Product use medallions
             Run_Medallions(i) = Ip.Cells(38, i + 2) + Ip.Cells(63, i + 2) + 50
            
         Else
            
            '# No medallions
            Run_Medallions(i) = 0
            
        End If ' Medallions
        
        '# Medallions label  Part 2                                                             'Medallions used in each run
        '# Medallion label =
        '# Labeller count + specific wastes + 50 start-up wastes
        
        '# If product is Single Vineyard Range, Te Awa Range, Tesco, Vidal Range
        
         If Left(Ip.Cells(3, i + 2), 7) = "VMPSALA" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMSALBA" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMSCHAR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMSROOR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMSPINO" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMSPGRI" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMSSAUV" Or _
            Left(Ip.Cells(3, i + 2), 7) = "VMSPIOR" Or _
            Left(Ip.Cells(3, i + 2), 7) = "TAEBDXB" Or _
            Left(Ip.Cells(3, i + 2), 7) = "TAESYRH" Or _
            Left(Ip.Cells(3, i + 2), 3) = "PTE" Or _
            Left(Ip.Cells(3, i + 2), 3) = "THV" Or _
            Left(Ip.Cells(3, i + 2), 3) = "VDL" Or _
            Left(Ip.Cells(3, i + 2), 3) = "VDJ" Or _
            Left(Ip.Cells(3, i + 2), 3) = "VMI" Then
            
            '# Product use medallions
             Run_Medallions(i) = Ip.Cells(38, i + 2) + Ip.Cells(63, i + 2) + 50
            
         Else
            
            '# No medallions
            Run_Medallions(i) = 0
            
        End If ' Medallions2
            
    End If '# Front and Back and Medallions Label
                                                                                                '# Display Pallet Ticket Usage
        .Cells(32, i + 1) = Run_Medallions(i)

Ip.cells is the input worksheet and .Cells is another worksheet where it gets displayed. Basically Any product that appears to have "characters" it shows it has medallions, if it doesnt than it shows 0.

Surely this is an easy fix

Upvotes: 0

Views: 46

Answers (1)

Samuel Everson
Samuel Everson

Reputation: 2102

Other than using Select...Case you could incorporate a For...Next loop and assign all values to check for into an Array.

Something like;

Dim ArrayElement As Long
Dim MyArray As Variant

MyArray = Array("VMCCHAR", "VMCBDXB", "VMEBDOR", "VMEPIOR", "VMESAOR", "VMPBDXB", "VMPCHAR", "RSX")  'Add all items to the array here

For i = 1 To RunCnt
    For ArrayElement = LBound(MyArray) To UBound(MyArray)
         If Left(Ip.Cells(3, i + 2), Len(MyArray(ArrayElement))) = MyArray(ArrayElement) Then
            '# Product use medallions
             Run_Medallions(i) = Ip.Cells(38, i + 2) + Ip.Cells(63, i + 2) + 50
         Else
            '# No medallions
            Run_Medallions(i) = 0
        End If ' Medallions
    Next ArrayElement
Next i

Note: I didn't add all of your values into the array so if you use this method be sure to add the missing values to check for.

Upvotes: 1

Related Questions