Absolute Reference While Using Cells Function

[zmster2033]

Good morning,

Looking to use an absolute reference to lock certain cells into a sumif formula. Please see below for current code:

 Range(Cells(2, UpdatedLastCol + 4), Cells(LOBCount, UpdatedLastCol + 4)).Formula = "=SUMIF($" & Cells(2, LastCol + 3).Address(0, 0) & ":" & Cells(LastRow, LastCol + 3).Address(0, 0) & "," & Cells(2, UpdatedLastCol + 3).Address(0, 0) & ",$H$2:$" & Cells(LastRow, LastCol).Address(0, 0) & ")"

I am needing to lock in the row numbers for the criteria and sum range. Any help is appreciated. Currently the above formula returns this within the spreadsheet:

=SUMIF($P2:P3217,S2,$H$2:$M3217)

I am needing it to be the following:

=SUMIF($P$2:$P$3217,S2,$H$2:$M$3217)

Thanks.

[HaHoBe]

Hi, zmster2033,

maybe try

 Range(Cells(2, UpdatedLastCol + 4), Cells(LOBCount, UpdatedLastCol + 4)).Formula = "=SUMIF(" & Cells(2, LastCol + 3).Address & ":" & Cells(LastRow, LastCol + 3).Address & "," & Cells(2, UpdatedLastCol + 3).Address(0, 0) & ",$H$2:$" & Cells(LastRow, LastCol).Address & ")"

Ciao,
Holger

[zmster2033]

Hello Holger,

Unfortunately that did not work. It gave me a Runtime 1004 error.

Thanks.

[HaHoBe]

Hi, zmster2033,

missed an additional $ you ghad set:

 Range(Cells(2, UpdatedLastCol + 4), Cells(LOBCount, UpdatedLastCol + 4)).Formula = "=SUMIF(" & Cells(2, LastCol + 3).Address & ":" & Cells(LastRow, LastCol + 3).Address & "," & Cells(2, UpdatedLastCol + 3).Address(0, 0) & ",$H$2:" & Cells(LastRow, LastCol).Address & ")"

Address(0, 0) stands for the relative address of any cell, if you omitt the values in the bracket you should get the absolute address.

Ciao,
Holger

[zmster2033]

Fantastic, thanks! Was not sure what Address function actually did. Thank you for the explanation!