VBA: How to fix cells positions in the OFFSET function

Question

I would like how this is coded:

OFFSET($F$52, 0, ($D342 - $D$342),1,1)

Since the cells positions are not the same, to represent cell D342 I will be using for instance Cells(300 + i, 5 + j).

I want to know how to fix cells using VBA code: i) To fix the row ii) To fix the column iii) To fix the row and the column

Thanks :)

Answer 1

When you have a range and you want to insert its address in a formula, you can use its .Address. This property is very flexible and permits you to control many things, including and most importantly, whether you want things to be absolute (fixed) or relative (movable when copying).

For the sake of your problem you can use the parameters RowAbsolute and ColumnAbsolute of the .Address method. For example, Cells(300 + i, 5 + j).Address(RowAbsolute=False). Remember that the default value for these parameters is True.

The following code snippet will tell you how to do so, explanation are included.

Sub TestUsingAddress()
   Dim r As Range
   Set r = Sheet1.Cells(3, 4) ' (D3)

   ' You can control the parameters RowAbsolute and ColumnAbsolute
   Debug.Print r.address                        ' $D$3 (default)
   Debug.Print r.address(ColumnAbsolute:=False) ' D$3
   Debug.Print r.address(RowAbsolute:=False)    ' $D3
   Debug.Print r.address(RowAbsolute:=False, ColumnAbsolute:=False) ' D3

   'and you can get the sheet's name with the address using the External parameter
   Debug.Print r.address(external:=True) ' [SOTest.xlsm]Sheet1!$D$3

   ' You can also get R1C1 format using the ReferenceStyle Parameter
   Debug.Print r.address(ReferenceStyle:=xlR1C1) ' R3C4. default format is xlA1
End Sub