Purpose: Look at table in Parameters worksheet for find/replace parameters. Use these values to find/replace text in Sales worksheet.
Heres the parameters table in the Parameters worksheet
[TABLE="width: 320"]
<tbody>[TR]
[TD]IF
[/TD]
[TD]then
[/TD]
[TD]Entry Code
[/TD]
[/TR]
[TR]
[TD]WILL CALL (Winebridge)
[/TD]
[TD][/TD]
[TD]Wine Bridge
[/TD]
[/TR]
[TR]
[TD]STEWCH09
[/TD]
[TD]>>>
[/TD]
[TD]CH09S1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWCS08
[/TD]
[TD]>>>
[/TD]
[TD]CS08S1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWCS09
[/TD]
[TD]>>>
[/TD]
[TD]CS09S1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWPN08
[/TD]
[TD]>>>
[/TD]
[TD]PN08S1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWHCS07
[/TD]
[TD]>>>
[/TD]
[TD]HCS07A1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWSCS10
[/TD]
[TD]>>>
[/TD]
[TD]CS10@VLX
[/TD]
[/TR]
[TR]
[TD]STE-07NCS6PK
[/TD]
[TD]>>>
[/TD]
[TD]NCS07S1@WB
[/TD]
[/TR]
[TR]
[TD]STE-08CS6PK
[/TD]
[TD]>>>
[/TD]
[TD]CS08S1@WB
[/TD]
[/TR]
[TR]
[TD]STE-08RRPN
[/TD]
[TD]>>>
[/TD]
[TD]PN08S1@WB
[/TD]
[/TR]
[TR]
[TD]STE-09CH6PK
[/TD]
[TD]>>>
[/TD]
[TD]CH09S1@WB
[/TD]
[/TR]
[TR]
[TD]STE-09HCS
[/TD]
[TD]>>>
[/TD]
[TD]HCS09A1@WB
[/TD]
[/TR]
[TR]
[TD]STE-09SLSB
[/TD]
[TD]>>>
[/TD]
[TD]SB09@WB
[/TD]
[/TR]
[TR]
[TD]STE-10SLCS
[/TD]
[TD]>>>
[/TD]
[TD]CS10@WB
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Here's the code. wsSales has already been defined and set as a global variable.
Thanks, in advance.
Crystal
Heres the parameters table in the Parameters worksheet
[TABLE="width: 320"]
<tbody>[TR]
[TD]IF
[/TD]
[TD]then
[/TD]
[TD]Entry Code
[/TD]
[/TR]
[TR]
[TD]WILL CALL (Winebridge)
[/TD]
[TD][/TD]
[TD]Wine Bridge
[/TD]
[/TR]
[TR]
[TD]STEWCH09
[/TD]
[TD]>>>
[/TD]
[TD]CH09S1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWCS08
[/TD]
[TD]>>>
[/TD]
[TD]CS08S1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWCS09
[/TD]
[TD]>>>
[/TD]
[TD]CS09S1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWPN08
[/TD]
[TD]>>>
[/TD]
[TD]PN08S1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWHCS07
[/TD]
[TD]>>>
[/TD]
[TD]HCS07A1@VLX
[/TD]
[/TR]
[TR]
[TD]STEWSCS10
[/TD]
[TD]>>>
[/TD]
[TD]CS10@VLX
[/TD]
[/TR]
[TR]
[TD]STE-07NCS6PK
[/TD]
[TD]>>>
[/TD]
[TD]NCS07S1@WB
[/TD]
[/TR]
[TR]
[TD]STE-08CS6PK
[/TD]
[TD]>>>
[/TD]
[TD]CS08S1@WB
[/TD]
[/TR]
[TR]
[TD]STE-08RRPN
[/TD]
[TD]>>>
[/TD]
[TD]PN08S1@WB
[/TD]
[/TR]
[TR]
[TD]STE-09CH6PK
[/TD]
[TD]>>>
[/TD]
[TD]CH09S1@WB
[/TD]
[/TR]
[TR]
[TD]STE-09HCS
[/TD]
[TD]>>>
[/TD]
[TD]HCS09A1@WB
[/TD]
[/TR]
[TR]
[TD]STE-09SLSB
[/TD]
[TD]>>>
[/TD]
[TD]SB09@WB
[/TD]
[/TR]
[TR]
[TD]STE-10SLCS
[/TD]
[TD]>>>
[/TD]
[TD]CS10@WB
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>>>
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Here's the code. wsSales has already been defined and set as a global variable.
Code:
Sub SearchReplaceWithParameters()
Dim rngParameters As range
Dim rngRow As range
Set rngParameters = ActiveWorkbook.Worksheets("Parameters").range("FindReplace")
For Each rngRow In rngParameters.Rows
'Replace text in Sales Orders
If Not IsEmpty(rngRow.Cells(rngRow.Rows.Row, 1)) Then
wsSales.Cells.Replace _
What:=rngRow.Cells(rngRow.Rows.Row, 1), _
Replacement:=rngRow.Cells(rngRow.Rows.Row, 3)
End If
Next
End Sub
Thanks, in advance.
Crystal