Assign TimeZone depending on State Abbreviation

Hello,

I would like to have Column "P" (timezone) be set to either "Pacific", "Mountain", "Central", or "Eastern" depending on the value of Column "N" (State)

If cell in column N = CA, column P = "Pacific"
If cell in column N = AZ, column P = "Mountain"
If cell in column N = NV, column P = "Pacific"
If cell in column N = TX, column P = "Central"

Any help would be greatly appreciated.

Originally Posted by ByteMarks

You could do this with a formula.
In column P

Formula:

Please Login or Register  to view this content.

ByteMarks, thanks so much for the info. In this case I am running a Macro through Power Automate Desktop so it would be best to use VBA.

Only for good readers using a smart worksheet a VBA demonstration to paste to the worksheet module :

PHP Code: 

Sub Demo1()
    With UsedRange.Columns
        .Item(16).Value2 = Evaluate("CHOOSE(IFERROR(MATCH(""*""&" & .Item(14).Address & _
            "&""*"",{""AZ"",""CA NV"",""TX""},0),4),""Mountain"",""Pacific"",""Central"","""")")
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thank you Marc,

I tried it but it gives me an error message Run-time error '424': Object Required.

I thought the solution was much simpler so I did not add a sample Excel file from the beginning but I have now attached it.

As it well worked on my side, as I warned for smart worksheet (ok) so the issue is located 'between the chair and the screen' !
Well read & apply at least the post #4 direction … According to your attachment a tiny optimization :

PHP Code: 

Sub Demo1r()
    With UsedRange.Rows("2:" & UsedRange.Rows.Count).Columns
        .Item(16).Value2 = Evaluate("CHOOSE(IFERROR(MATCH(""*""&" & .Item(14).Address & _
            "&""*"",{""AZ"",""CA NV"",""TX""},0),4),""Mountain"",""Pacific"",""Central"","""")")
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by Marc L

As it well worked on my side, as I warned for smart worksheet (ok) so the issue is located 'between the chair and the screen' !
Well read & apply at least the post #4 direction … According to your attachment a tiny optimization :

PHP Code: 

Sub Demo1r()
    With UsedRange.Rows("2:" & UsedRange.Rows.Count).Columns
        .Item(16).Value2 = Evaluate("CHOOSE(IFERROR(MATCH(""*""&" & .Item(14).Address & _
            "&""*"",{""AZ"",""CA NV"",""TX""},0),4),""Mountain"",""Pacific"",""Central"","""")")
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Marc, I don't understand your 'between the chair and the screen' comment. If it is meant to be sarcastic, or the For Good Readers Only, I don' appreciate it. People don't come to this forum to feel belittled. Communication is also the responsibility of who is transmitting the information to make sure the message gets across.

I hope I am wrong and is just a language barrier.

Anyway, your sub did not work for me.

For someone looking for a solution to this, I came up with the following while avoiding UsedRange which is unreliable based on ByteMarks (who was verfy helpful by the way) formula with null as an option in case another state pops up that is not in the options and I also changed the Sheet name since I just changed the Sheet name on the workbook:

PHP Code: 


Sub StateAb()

    With Sheets("Sheet1")
    Set RngCol = .Range("N2", .Cells(Rows.Count, 1).End(xlUp))
    End With
    LastRow = RngCol.Rows.Count
    Range("P2:P" & LastRow + 1).Value = "=IFERROR(CHOOSE((RC[-2]=""CA"")+(RC[-2]=""AZ"")*2+(RC[-2]=""NV"")*3+(RC[-2]=""TX"")*4,""Pacific"",""Mountain"",""Pacific"",""Central""),"""")"

End Sub 


It was just a slight touch of humor, as the solution belongs to good readers …