Apply Substitute formula to whole column

[deutz]

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

 

[tommyt61]

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)

 

[deutz]

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.

 

[EvR]

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

 

[deutz]

Thanks EvR,

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

Cheers