Apply Substitute formula to whole column

deutz

New member
Joined
May 28, 2012
Messages
27
Reaction score
0
Points
0
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 ...

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

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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:
Code:
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.
Code:
.Value = WorksheetFunction.Substitute("101-A", origString, newString)
 
Last edited:

deutz

New member
Joined
May 28, 2012
Messages
27
Reaction score
0
Points
0
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

New member
Joined
Jul 5, 2012
Messages
1
Reaction score
0
Points
0
Location
Netherlands
It's been a few days but does this work for you?

Code:
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

New member
Joined
May 28, 2012
Messages
27
Reaction score
0
Points
0
Thanks EvR,

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

Cheers
 
Top