Apply formula to cells without relative cell referencing in VBA

[gracecyl]

Hi,

How do I prevent relative cell referencing with this formula?


Dim LastRow As Long

LastRow = Worksheets("data").Cells(Rows.Count, "I").End(xlUp).Row


Range(Range("N2"), Range("N2").End(xlDown)).formula = _
"=SUMIFS(data!J2:J" & LastRow & ",data!I2:I" & LastRow & ",Sheet1!I2)"

Range("N2").Select

Selection.AutoFill Destination:=Range(Range("N2"), Range("N2").End(xlDown))
Range(Range("N2"), Range("N2").End(xlDown)).Select

 

[Yongle]

Try this

  "=SUMIFS(data!J[COLOR=#ff0000]$[/COLOR]2:J[COLOR=#ff0000]$[/COLOR]" & LastRow & ",data!I[COLOR=#ff0000]$[/COLOR]2:I[COLOR=#ff0000]$[/COLOR]" & LastRow & ",Sheet1!I[COLOR=#ff0000]$[/COLOR]2)"

- not sure if you need the final $ - that depends on what the formula is supposed to be summing!

 

[gracecyl]

It’s not possible to lock cells with “$” on VBA.

 

[Yongle]

In that case

Sub AutoFill()
    Dim LastRow As Long
    LastRow = Worksheets("data").Cells(Rows.Count, "I").End(xlUp).Row
    
    With Range("N2")
        .Formula = "=SUMIFS(data!J$2:J$" & LastRow & ",data!I$2:I$" & LastRow & ",Sheet1!I$2)"
        .Copy Range(Range("N2"), Range("N2").End(xlDown))
    End With
End Sub

Do you have something to prevent Range("N2").End(xlDown) filling formula all the way to row 1048576 ?

 

[RoryA]

It’s not possible to lock cells with “$” on VBA.

Yes, it is. You're just putting a formula in, which works the same however you do it. I doubt you want to lock that last reference though as you'll end up with an entire column of the same result.