More Find/REplace from table questions...

D

deforsythe

Hi again.
Thanks to Barry-Jon and Dave Peterson for their help earlier on this
topic. Now I need a little more help.

I have a macro I pieced together from this group that finds a value in
column 1 of a table in replaces it with it's corresponding value from
column 2, which works fantastically well.

Now I have one pesky column of data that has to match by 2 columns of
data. So essentially, if Columns C and D on worksheet1 match columns B
and C on worksheey2, I need to replace Column C on worksheet1 with
Colum A on worksheet2.

Does that make sense?

Here is the code I am working with for the single item matching
Find/Replace from a range:
______________________________________

Sub RangeFind2Replace1()

Dim rngReplaceWith As Excel.Range
Dim rngSearchArea As Excel.Range
Dim lngRepaceCount As Long

Set rngReplaceWith = GetUserRange("Please select find/replace
values range (two columns)")

If Not rngReplaceWith Is Nothing Then

'basic range size validation - a lot more could be done
If rngReplaceWith.Columns.Count = 2 Then

'now get the area in which to do the find/replace
Set rngSearchArea = GetUserRange("Please select the range
in which to find/replace")

If Not rngSearchArea Is Nothing Then

'do the search and replace
For lngRepaceCount = 1 To rngReplaceWith.Rows.Count

rngSearchArea.Replace
What:=rngReplaceWith.Cells(lngRepaceCount, 2).Value, _
Replacement:=rngReplaceWith.Cells(lngRepaceCount,
1).Value, _
MatchCase:=False, lookat:=xlWhole, ReplaceFormat:=False

Next lngRepaceCount

End If

Else

MsgBox "Invalid find/replace range selected", vbExclamation
+ vbOKOnly

End If

End If

End Sub

Private Function GetUserRange(Prompt As String, Optional Title As
String = "Input") As Excel.Range

On Error GoTo ErrorHandler

Dim retVal As Excel.Range

Set retVal = Application.InputBox(Prompt, Title, , , , , , 8)

ExitProc:
Set GetUserRange = retVal
Exit Function

ErrorHandler:
Set retVal = Nothing
Resume ExitProc

End Function

________________________________

Can this be modified to do what I need it to?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top