Replace text with variable using VBA replace code?

Discussion in 'Microsoft Excel Programming' started by Mike, Nov 9, 2006.

  1. Mike

    Mike Guest

    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, Nov 9, 2006
    #1
    1. Advertisements

  2. 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
    news:...
    > 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,
    >
     
    Bernie Deitrick, Nov 9, 2006
    #2
    1. Advertisements

  3. Mike

    Guest Guest

    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,
    >
    >
     
    Guest, Nov 9, 2006
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. shoba

    Replacing VBA code strings by using VBA code?

    shoba, Jun 27, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    1,293
    Chip Pearson
    Jun 27, 2005
  2. mikeburg

    Extract last name into a variable using VBA code

    mikeburg, Jan 22, 2006, in forum: Microsoft Excel Programming
    Replies:
    9
    Views:
    502
    David
    Jan 22, 2006
  3. Buffyslay

    select offset (variable ,1) to offset(variable ,variable)

    Buffyslay, Nov 15, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    387
    Bob Phillips
    Nov 15, 2006
  4. Michael Mueller

    Unprotect VBA-Code using VBA-Code?

    Michael Mueller, Apr 16, 2007, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    1,113
    Carl Hartness
    Apr 17, 2007
  5. Tommy

    Using VLookup in VBA code with variable range

    Tommy, Aug 14, 2007, in forum: Microsoft Excel Programming
    Replies:
    15
    Views:
    1,252
    Tommy
    Aug 15, 2007
Loading...

Share This Page