Excel VBA Code - If Then with multiple If Options

Question

I am looking for some expert advice on writing a small bit of VBA for a worksheet I'm building. I've tried a few different variations with no luck. I've pasted what I have below. Any assistance would be greatly appreciated.

If Range("C2:C900").Value = ("ME","NH","VT","MA","RI","CT") Then Range("D2:D900").Value = I

If Range("C2:C900").Value = ("NY",NJ","PR") Then Range("D2:D900").Value = II

If Range("C2:C900").Value = ("PA",MD","DE","VA","DC","WV") Then Range("D2:D900").Value = III

And so on

you will need to loop the range as an array of values cannot equal one value. Also look into Select Case — Scott Craner
– Scott Craner, Commented Mar 20, 2020 at 20:19

Scott Craner · Accepted Answer · 2020-03-20 20:28:20Z

2

You will need to use a loop and use Select Case but loop a variant array and not the ranges, it will be quicker:

With ActiveSheet 'Should change to the actual sheet
    Dim inRng As Variant
    inRng = .Range("C2:C900").Value

    Dim outRng As Variant
    outRng = .Range("D2:D900").Value

    Dim i As Long
    For i = LBound(inRng, 1) To UBound(inRng, 1)
        Select Case inRng(i, 1)
            Case "ME", "NH", "VT", "MA", "RI", "CT"
                outRng(i, 1) = "I"
            Case "NY", "NJ", "PR"
                outRng(i, 1) = "II"
            Case "PA", "MD", "DE", "VA", "DC", "WV"
                outRng(i, 1) = "III"
            Case Else
                outRng(i, 1) = ""
        End Select
    Next i

    .Range("D2:D900").Value = outRng
End With

answered Mar 20, 2020 at 20:28

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Code - If Then with multiple If Options

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related