Give this macro a try...

Sub InsertFormula()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "U").End(xlUp).Row - 1
.Range("T1").Formula = "=sumproduct(ISNUMBER(SEARCH(U2:U" & _
LastRow & ",""cyc""))*Q2:Q" & LastRow & ")"
End With
End Sub

--
Rick (MVP - Excel)


"bpotter" wrote in message
...
I am trying to insert a formula into a cell using a macro. Is there
anyway I can insert a variable into formula this way?

The formula
=sumproduct(isnumber(search("cyc", u2:u34))*q2:q34)
I would like it to search from u2 to the end of file minus 1 and q2 to
the end of file minus 1.
I am also having problems with relative referencing when I insert the
formula like it is. By taking out r1c1 does this fix this?

Thanks
Bryan