PDA

View Full Version : [SOLVED] VBA to insert column and write concatenation formula



nirvehex
09-04-2014, 07:12 AM
Hi,

I'm trying to insert a column in between columns A & B. Then in the new B1 I would like to write text that says "Unique Key"

Then in cells b2:end of data set I want to write =concatenate(F1,"-",R1,"-",T1) and have it copy this formula all the way down column B until the data stops in those other columns.

I have the first part of the code which inserts a column, the rest is hard for me:

Here's what I have so far:



Sub sbInsertingColumns()
Dim x As Long
Range("B1").EntireColumn.Insert
End Sub


Any ideas?

Thanks.

mancubus
09-04-2014, 07:42 AM
hi.
try this.



With Worksheets("MyWorksheet") 'change ws name to suit
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
.Columns(2).Insert
.Range("B1").Value = "Unique Key"
With .Range("B2:B" & LastRow)
.Formula = "=F2&"" - ""&R2&"" - ""&T2"
.Value = .Value 'convert formulas to formula results
End With
End With

nirvehex
09-04-2014, 08:20 AM
Worked perfectly.

Thanks!