OzGrid

How to use VBA code using relative references

Requirement:

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.

Sub test()
Dim LastRow As Long

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("B1").EntireColumn.Insert
Range("B1").FillRight
Range("B2:B" & LastRow).FormulaR1C1 = "SJ-" & Cells(2, 1)

End Sub

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?

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149553-vba-using-relative-references

Solution:

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.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery