Replace text with variable using VBA replace code?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I'm trying to use the formula below to replace the text in a formula
with the info from a variable.

Dim strg As String
strg = Range("D4").Text
Range("C7").Select
ActiveCell.Replace What:="Master Sample Data Form", Replacement:= _
"& strg &", LookAt:=xlPart, SearchOrder:=xlByColumns,
MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False

In short it doesn't work, it places & strg & in the formual instead of
the variable information. I've also tried "strg" and strg (without
quotes) and non work. Does any one know how you might be able to
replace text in a formula with variable information?

Thanks!

Mike,
 
Mike,

This one line:

Range("C7").Replace What:="Master Sample Data Form", _
Replacement:=Range("D4").Text, LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False


or, if you still want to use a variable:

Dim strg As String
strg = Range("D4").Text
Range("C7").Replace What:="Master Sample Data Form", Replacement:= _
strg, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
 
This worked for me with "Master Sample Data Form..." in C7 and some other
text in D4...

Dim strg As String
strg = Range("D4").Text
Range("C7").Replace What:="Master Sample Data Form", _
Replacement:=strg, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
 
Back
Top