CODEWITHSUNDEEP
excelvbaoffset
fedone
fedone

Reputation: 55

Offset address to be used in dynamic formula

I am trying to identify a cell in excel using VBA which is going to be used to add a dynamic formula. This is what I have done so far:

STCELL = Sheets("ADMIN").Range("h" & Rows.Count).End(xlUp).Address(False, False)

The above line gives me cell "H16"

Now I need to offset this address. I thought about this:

STCELL1 = Range(" & STCELL & ").Offset(1, 0)

but unfortunately is not working. The results of the STCELL (start cell) should be used in the next line:

Sheets("admin").Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Formula = "=INDEX(Mapping!$D:$F,MATCH(" & STCELL & ",Mapping!$A:$A,0),1)"

the address of STCELL is properly read by VBA in the last line, but I need to offset it since the location of the cell where the formula is going to be applied will keep changing.

So, question is, how can I offset the address I have identified with my first line of code?

Thank you for all your help.

Upvotes: 0

Views: 129

Answers (1)

BigBen
BigBen

Reputation: 50008

Since it is the Range.Offset property, focus on offsetting a Range, not the address of a Range:

Dim rng As Range
Set rng = Sheets("ADMIN").Range("h" & Rows.Count).End(xlUp)

STCELL = rng.Address(False, False)
STCELL1 = rng.Offset(1).Address(False, False)

Upvotes: 2

Related Questions