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 :)

score 2 · Answer 1 · edited Sep 21 '17 at 16:07

2

You are searching for …

.Address(ReferenceStyle:=xlR1C1)

Having a look into the Microsoft VBA References sometimes helps: Range.Address Property (Excel).

Also, this reference should be fully qualified with the worksheet name and not left to the relative context:

Cells(j, LastCol).Address

edited Sep 21 '17 at 16:07

Graham

7,431
18
59
84

answered Apr 27 '17 at 09:27

Pᴇʜ

56,719
10
49
73

1

Thank you so much for :) I went with @Vityata, but I really appreciate your help and the "VBA Best practices" etc. I am fairly new to VBA, so I appreciate all the tips and tricks I can get :) – BitteB Apr 27 '17 at 11:22

score 2 · Accepted Answer · edited May 23 '17 at 12:02

2

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?

edited May 23 '17 at 12:02

Community

1
1

answered Apr 27 '17 at 09:32

Vityata

42,633
8
55
100

Thank you so much! I am using `.Address(RowAbsolute:=False, ColumnAbsolute:=False)`. I had actually already tried using `.Address(False,False)` and `.Address(0,0)`, but didn't know why + it didn't help. And thanks for the hints :) I am fairly new to VBA, so I appreciate all the tips and tricks I can get :) – BitteB Apr 27 '17 at 11:20

VBA: insert formula with dynamic range and relative reference

2 Answers2