Replace text with variable using VBA replace code?
 

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,

Replace text with variable using VBA replace code?
 

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


--
HTH,
Bernie
MS Excel MVP


"Mike" wrote in message
oups.com...
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,

Replace text with variable using VBA replace code?
 

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

--
HTH...

Jim Thomlinson


"Mike" wrote:

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,