PDA

View Full Version : Solved: Replace Method using variables to find/replace



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

Norie
09-14-2007, 12:04 PM
Doug

So what's the question?

By the way why do you need the find in the first place?

Couldn't you just loop down the range?

YellowLabPro
09-14-2007, 12:08 PM
The first line listed in vba code in the initiating thread is invalid.
What am I trying to do you ask? Replace values that are known to exist in text strings that are invalid, basically abbreviations w/ their un-abbreviated terms.
I am moving out of storing it in code to a table in a worksheet.

for example:

If InStr(c, " LTH ") > 0 Then c.Formula = _
Application.WorksheetFunction.Substitute(c.Formula, " LTH ", " LEATHER")
If InStr(c, " VZ ") > 0 Then c.Formula = _
Application.WorksheetFunction.Substitute(c.Formula, " VZ ", " VONZIPPER ")
If InStr(c, "GRASSH") > 0 Then c.Formula = _
Application.WorksheetFunction.Substitute(c.Formula, "GRASSH", "GRASSHOPPER")

is in code, now it will reference it in the sheet table.

mdmackillop
09-14-2007, 12:13 PM
I think you'll need to search in the original string for each abbreviation. This could cause unexpected results!

Norie
09-14-2007, 12:22 PM
Doug

I think by now we all realise what you are actually trying to achieve.

But I still don't see why you need find.

If you have a list of the values you are looking for and the values to replace them with why can't you just loop down that and apply the relevant find/replace to the range in one go?

mdmackillop
09-15-2007, 01:59 AM
Option Explicit

Sub SwapAbbTerms()
Dim wsDp As Worksheet
Dim d As Range, Rng As Range, Abbr As Range
Dim lrwSource As Long
Dim wb As String, wsn As String

Application.ScreenUpdating = False

'Define range to be updated
wb = "MasterImportSheetWebStore.xls"
wsn = "DataPrep"
Set wsDp = Workbooks(wb).Worksheets(wsn)
lrwSource = lr(wsDp, 1)
Set Rng = Range(wsDp.Cells(2, 3), wsDp.Cells(lrwSource, 3))

'Set range containing criteria
With Sheets("ColTab")
Set Abbr = Range(.Cells(1, "F"), .Cells(Rows.Count, "F").End(xlUp))
End With

'Replace within Range
For Each d In Abbr
Rng.Replace What:=d, Replacement:=d.Offset(, 1)
Next d

Application.ActiveSheet.Calculate
Application.ScreenUpdating = True
End Sub

YellowLabPro
09-15-2007, 02:11 AM
MD-
Works Great! Another Great Solution from the wizard... http://www.vbaexpress.com/forum/images/smilies/wizard.gif