Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

VBA additional condition

I have the code below, which allows me to choose a column and replace based on a list in a different sheet.

For Canada, I have to add conditions that are not in existing code - also the replacement is NOT in the same column, but
in different column based on the value in the service & the zip.

1. Choose a column that shows service. (will not be same column each time)
2. Choose a column that shows zip code (Will not be same column each time)
3. Choose a column that shows zone. (will not be same column each time)
4. If value in service column = 'GR' or 'FHD' replace zone with Key sheet, column F.
5. If value in service column is anything else replace zone with Key sheet, column E.

Can anyone help?

Sub Zone_Canada()
    Set shList = Sheets("Data")
    Set shKey = Sheets("Canada_Zones")
    shList.Activate
    On Error Resume Next
    Set Rng = Application.InputBox(prompt:="Select the Column with Number.", Title:="Column to search", Type:=8)
    On Error GoTo 0

    If Rng Is Nothing Then Exit Sub
    For Each c In Range(shKey.Range("A2"), shKey.Range("A" & Rows.Count).End(xlUp))
        Rng.EntireColumn.Replace what:=c.Value, replacement:=c.Offset(, 5).Value, lookat:=xlWhole, MatchCase:=False
End Sub

Open in new window

Sample.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Euro5

ASKER

AWESOME. Thank you so much!!