PC Review


Reply
Thread Tools Rate Thread

Replace text with variable using VBA replace code?

 
 
Mike
Guest
Posts: n/a
 
      9th Nov 2006
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,

 
Reply With Quote
 
 
 
 
Bernie Deitrick
Guest
Posts: n/a
 
      9th Nov 2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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,
>



 
Reply With Quote
 
 
 
 
=?Utf-8?B?SmltIFRob21saW5zb24=?=
Guest
Posts: n/a
 
      9th Nov 2006
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,
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to replace one dynamic variable with another dynamic variable djreason Microsoft Excel Worksheet Functions 8 11th Sep 2009 01:57 AM
select offset (variable ,1) to offset(variable ,variable) Buffyslay Microsoft Excel Programming 1 15th Nov 2006 12:45 PM
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? Daniel Microsoft Excel Discussion 1 9th Jul 2005 01:04 AM
Hi, can I replace plain text with bold text using Find/Replace or possibly with a macro? Derek Microsoft Excel New Users 9 4th Mar 2004 12:39 PM
Replace methode, Replace Function, Stringbuilder replace, Regex Replace, Split Cor Microsoft VB .NET 4 1st Mar 2004 03:50 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:35 PM.