Euro5
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?
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
Sample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER