CODEWITHSUNDEEP
excelvba
Maggie Cullather
Maggie Cullather

Reputation: 1

Excel VBA Code - If Then with multiple If Options

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

Upvotes: 0

Views: 130

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

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

Upvotes: 2

Related Questions