The point of this code is to insert column B, copy the header from the first cell on the left into B1 and then insert "SJ-" followed by the value in the cell to the left (A2) in B2 down to the bottom of the data.
The problem is that every cell contains "SJ-" and the value of A2 so the question is, how to make Cells(2, 1) relative so that it always using the value to the left of the cell?
Sub test() Dim LastRow As Long LastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("B1").EntireColumn.Insert Range("B1").FillRight With Range("B2:B" & LastRow) .Value = "=""SJ-"" & RC[-1]" .Value = .Value End With End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Skywriter.
|How to copy/paste between workbooks with relative referencing|
|How to understand relative notations|