Euro5
asked on
VBA error replacing data
The code below is resulting in an error.
It should replace the zone column based on the lookup column.
It looks at the service and zip code then replaces the zone.
Need to be able to change zone even if there is number or something different in the zip code column.
I get the error 'type mismatch'
Cells(c.Row, rngZone.Column) = shKey.Range("A1").Offset(i ntRow - 1, intOffset)
DESPERATE for help!! Can anyone assist quickly?
It should replace the zone column based on the lookup column.
It looks at the service and zip code then replaces the zone.
Need to be able to change zone even if there is number or something different in the zip code column.
I get the error 'type mismatch'
Cells(c.Row, rngZone.Column) = shKey.Range("A1").Offset(i
DESPERATE for help!! Can anyone assist quickly?
Sub Zone_Canada()
Set shList = Sheets("Data")
Set shKey = Sheets("Canada_Zones")
shList.Activate
With Range("1:1")
Set rngZip = Application.InputBox(prompt:="Select the Column with Zip Codes.", Title:="Column to search", Type:=8)
Set rngServ = Application.InputBox(prompt:="Select the Column with FedEx Services.", Title:="Column to search", Type:=8)
Set rngZone = Application.InputBox(prompt:="Select the Column with Zones.", Title:="Column to search", Type:=8)
End With
For Each c In Range(shList.Cells(2, rngServ.Column), shList.Cells(Rows.Count, rngServ.Column).End(xlUp))
If c = "GR" Or c = "FHD" Then
intOffset = 5
Else
intOffset = 4
End If
intRow = Application.Match(Cells(c.Row, rngZip.Column), shKey.Range("A:A"), 0)
Cells(c.Row, rngZone.Column) = shKey.Range("A1").Offset(intRow - 1, intOffset)
Next
End Sub
conversion.xlsm
ASKER
It says "12" what does that mean?
ASKER
If it does not find something in the list, it gives me an error.
How can I just SKIP if it is not found on the list?
That would be perfect!
How can I just SKIP if it is not found on the list?
That would be perfect!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!!
You're welcome!
Open in new window
I suspect it is not finding a match and intRow not what you think