VBA: insert formula with dynamic range and relative reference

Question

I have done a lot of searching and trial and error and I still haven't been able to find an answer for my question.

I would like to fill a range in sheet2 with a formula that refers to a dynamic range in sheet1. I have used the piece of code that I have pasted below, and it works perfectly so far.

Dim rng as Range
Dim x as Integer

Set rng=worksheets(sheet2).Range("J6:J9")
x = 21

    With sheet1
        LastCol = .Cells(21, .Columns.Count).End(xlToLeft).Column
    End With

    For Each c In rng
        c.Formula = "=sheet1!" & Cells(j, LastCol).Address
        j = j + 1
    Next c

My problem is that this code creates an absolute reference, and I need a relative (or mixed) since I need to be able to do some "dragging" of cells later on. I have tried using formulaR1C1, but I can't get it to work with dynamic ranges.

I would be very happy if anyone can help me and if you need me to explain anything feel free to ask :)

Vityata Vityata · Accepted Answer · 2017-04-27T09:32:10

Or you are actually searching for this:

.Address(RowAbsolute:=False, ColumnAbsolute:=False)

The address has 5 properties - .Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

As far as these are the first two parameters from the 5 in the .Address, you may call them like this:

.Address(False, False)

or even like this:

.Address(0, 0)

And some additional hints to what Peh already mentioned - do not use Integers in VBA - Why Use Integer Instead of Long?

VBA: insert formula with dynamic range and relative reference

2 Answers