Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'replace' function within formulas (Excel 2000)

    Can you use the "replace" function within a formula? I tried and it was trying to reference the other worksheets within the workbook instead of the formula itself. For example, I want to replace "C2" with "C4" or "E2" with "G2".

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'replace' function within formulas (Excel 2000)

    You'd have to use VBA function, e.g.

    Sub ReplaceInFormula(strWhat As String, strWith As String)
    Dim oCell As Range
    For Each oCell In Selection.Cells
    oCell.Formula = Replace(oCell.Formula, strWhat, strWith)
    Next oCell
    Set oCell = Nothing
    End Sub

    Use like this:

    ReplaceInFormula "C2", "C4"
    ReplaceInFormula "E2", "G2"

    Note: the code is not intelligent. The first call will also replace C23 with C43, and AC2 with AC4.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'replace' function within formulas (Excel 2000)

    The replace function is for strings not formulas.

    You could use Find/replace from the menus

    You could use application.worksheetfunction.replace with the formulas in VB (when you read the formulas they are strings)

    You could use some type of INDIRECT function, though I am not sure exactly what you are after, could you elaborate?

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •