Splitting Cell Contents

[cabbagefoot]

Hello

I am trying to split the contents of a cell which has a complete address in it:

Company name,
Address 2,
Address 3,
Address 4,
Post code

The below visual basic code which I found somewhere on this forum does a great job of splitting one cell (A4), however, is there any way to automatically change the cell reference so I can run the macro for A4, A5, A6,A7 in one go.........

Sub tst()
Dim X As Variant
X = Split(Range("A4").Value, ",")
Range("A4").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End Sub

Thanks in advance

 

[lenze]

Hi and welcome to the board!!!
Untested, but give this a try

Sub tst()
Dim cl As Range
For Each cl In Selection
Dim X As Variant
X = Split(cl.Value, ",")
cl.Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
Next cl
End Sub

lenze

 

[cabbagefoot]

Works perfectly. Thanks for your help.