Thea has attended:
Excel VBA Intro Intermediate course
Loops
I am trying to develop a code that inserts a column, writes a header and then conducts a formula concatenating 3 other columns. I want to loop the code based on whether the cell in the first column is populated or not.
A B Z AA AB
ID Name Address 1 Address 2 Address 3
00001 James 1 High St London N3 8PL
So when column A becomes blank I want the formula to stop in the new column created in cell AC that concatenates Z, AA and AB.
Hope that makes sense!
Thanks in advance
RE: Loops
Hi Thea,
I have written some code (see below) based on your question . If you copy and paste this code into the code window of your spreadsheet's VBE, you could test it out on some sample data. It worked on my sample spreadsheet. I tried to emulate your spreadsheet's layout.
Sub JoinAddresses()
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove
'This line adds a column and moves any existing content to the next column.
Range("AC1").Select
ActiveCell = "Address" 'You can replace "Address" with any text
Range("AC2").Select
Do Until ActiveCell.Offset(0, -28) = "" 'Looks at the data in column A
ActiveCell = "=(RC[-3]&"" ""&RC[-2]&"" ""&RC[-1])" 'Concatenates 3 columns
ActiveCell.Offset(1, 0).Select
Loop
Columns("AC:AC").AutoFit
End Sub
Kind regards
Marius Barnard
Excel Trainer
