YellowLabPro
09-14-2007, 11:13 AM
This block is to check for a value in a string by setting a variable and using the Find Method. It is a list in a Worksheet.
If it finds it, it needs to replace the value w/ the value offset by 1.
This line is giving me trouble:
If Not d Is Nothing Then c = Replace(what:= c, replacement:=d.Offset(, 1))
Mdmackillop wrote this up for me originally to find/replace one for one. I am applying it to slightly different needs. Now it is to replace text in a string. I do not think the InStr is necessary, that is why I am not opting that up here.
For instance this string:
~C 32 GL NIU BOA SNBT WHITE/GUM 7
would become
~C 32 GIRLS NIU BOA SNBT WHITE/GUM 7
In the ColTab Sheet,
F1 has the value GL and G1 has GIRLS
Sub SwapAbbTerms_temp()
Dim wsDp As Worksheet
Dim c As Range, d As Range, rng As Range
Dim lrwSource As Long
Dim wb As String, wsn As String
wb = "MasterImportSheetWebStore.xls"
wsn = "DataPrep"
Set wsDp = Workbooks(wb).Worksheets(wsn)
With wsDp
lrwSource = lr(wsDp, 1)
Set rng = Range(wsDp.Cells(2, 3), wsDp.Cells(lrwSource, 3))
Application.ScreenUpdating = True
For Each c In rng
Set d = Sheets("ColTab").Columns("F").Find(c)
If Not d Is Nothing Then c = Replace(what:=c, replacement:=d.Offset(, 1))
Next c
End With
Application.ActiveSheet.Calculate
Application.ScreenUpdating = True
End Sub
If it finds it, it needs to replace the value w/ the value offset by 1.
This line is giving me trouble:
If Not d Is Nothing Then c = Replace(what:= c, replacement:=d.Offset(, 1))
Mdmackillop wrote this up for me originally to find/replace one for one. I am applying it to slightly different needs. Now it is to replace text in a string. I do not think the InStr is necessary, that is why I am not opting that up here.
For instance this string:
~C 32 GL NIU BOA SNBT WHITE/GUM 7
would become
~C 32 GIRLS NIU BOA SNBT WHITE/GUM 7
In the ColTab Sheet,
F1 has the value GL and G1 has GIRLS
Sub SwapAbbTerms_temp()
Dim wsDp As Worksheet
Dim c As Range, d As Range, rng As Range
Dim lrwSource As Long
Dim wb As String, wsn As String
wb = "MasterImportSheetWebStore.xls"
wsn = "DataPrep"
Set wsDp = Workbooks(wb).Worksheets(wsn)
With wsDp
lrwSource = lr(wsDp, 1)
Set rng = Range(wsDp.Cells(2, 3), wsDp.Cells(lrwSource, 3))
Application.ScreenUpdating = True
For Each c In rng
Set d = Sheets("ColTab").Columns("F").Find(c)
If Not d Is Nothing Then c = Replace(what:=c, replacement:=d.Offset(, 1))
Next c
End With
Application.ActiveSheet.Calculate
Application.ScreenUpdating = True
End Sub