VBA Range Formula

[Guest]

Can anybody tell me how to get this to work? In other words, how do I
reference my procedure variables in a cell formula? Thanks!

Sub Marktest()
Set b = Cells.Find("Total", , xlValues)
Set c = Range("C5")

Range(b.Offset(-1, 2), c).Formula = _
"=sumproduct(($B5=Range(b.Offset(-7, 1), b.Offset(-5, 1)))*$C$1:$C$3)"
End Sub

 

[Dave Peterson]

I'd do something like:

Option Explicit
Sub Marktest2()

Dim myRng As Range
Dim b As Range
Dim c As Range

With ActiveSheet
Set b = .Cells.Find("Total", , xlValues)
Set c = .Range("C5")

Set myRng = .Range(b.Offset(-7, 1), b.Offset(-5, 1))

.Range(b.Offset(-1, 2), c).Formula = _
"=sumproduct(($B5=" & myRng.Address(external:=True) & ")*$C$1:$C$3)"
End With

End Sub