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

Community · Accepted Answer · 2017-05-23 12:02:36Z

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, 2017 at 12:02

CommunityBot

11 silver badge

answered Apr 27, 2017 at 9:32

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BitteB Over a year ago

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

Graham · Accepted Answer · 2017-09-21 16:07:26Z

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, 2017 at 16:07

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Apr 27, 2017 at 9:27

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

1 Comment

BitteB Over a year ago

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

Collectives™ on Stack Overflow

VBA: insert formula with dynamic range and relative reference

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related