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

    Bernie Deitrick Guest

    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?

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

    How to format text in a Text box using VBA code

    Shane B, Nov 15, 2004, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    19,373
    Tom Ogilvy
    Nov 15, 2004
  2. shoba

    Replacing VBA code strings by using VBA code?

    shoba, Jun 27, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    2,187
    Chip Pearson
    Jun 27, 2005
  3. Mac Lingo

    VBA: Object variable or With block variable not set (Error 91) - ???

    Mac Lingo, Nov 2, 2005, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    3,483
    Norman Jones
    Nov 2, 2005
  4. mikeburg

    Extract last name into a variable using VBA code

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

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

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

    Unprotect VBA-Code using VBA-Code?

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

    Using VLookup in VBA code with variable range

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