Results 1 to 5 of 5

Thread: Apply Substitute formula to whole column

  1. #1

    Apply Substitute formula to whole column

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi and thanks in advance,

    Wondering if there is anyway to apply a Substitute formula to a whole column without looping. Replace is not an option as it has a limit of 1024 characters.

    Tried this but it just put the full range address in every cell in column A ...

    Dim x As String
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
            x = .Address
            .Value = WorksheetFunction.Substitute(x, origString,  newString)
    End With

  2. #2
    This will Start at Cell A1 and replace all cells in column A (down to first blank cell) with whatever value or data you have entered in Cell C1. You can change the cell range to whatever cell you prefer in the code.

    Code for using C1:
    Sub Replace_Content()
    Dim x As String
    Dim Y As String
    Y = Range("C1")
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
            x = .Address
            .Value = WorksheetFunction.Substitute(Y, origString, newString)
    End With
    End Sub

    if you will always be substituing the same value all the time you can just replace the x right next to the Substitute command in your original code.
    .Value = WorksheetFunction.Substitute("101-A", origString, newString)
    Last edited by tommyt61; 2012-06-18 at 03:08 PM.

  3. #3
    Thanks for your suggestion.

    The only problem I've encountered is that Substitute will only work with cells that contain no more than 1271 characters and some of my cells contain a lot more than that.

  4. #4
    Neophyte EvR's Avatar
    Join Date
    Jul 2012
    It's been a few days but does this work for you?

    Sub Replace_Content()
    Dim NewString As String, OldString As String
    NewString = """newnew"""
    OldString = """oldold"""
    With Range("A1", Range("A" & Cells(Rows.Count, "A").End(xlUp).Row))
            .Offset(, 200).Formula = "=SUBSTITUTE(" & Cells(1, 1).Address(False, True) & ", " & OldString & "," & NewString & ")"
            .Offset(, 200).Copy
            .PasteSpecial xlPasteValues
            .Offset(, 200).Value = ""
    End With
    End Sub

  5. #5
    Thanks EvR,

    I've moved onto another project but will test your code when I get some time.


Posting Permissions

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