Cell referencing, formulas, macros

[bpotter]

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

 

[ryguy7272]

Turn on the macro recorder and then type the function and then turn off the
macro recorder and then look at the results.

You will see something along the lines of:
..Formula = "yourfunctionhere"

That's pretty much it.

Regards,
Ryan---

 

[Rick Rothstein]

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

 

[bpotter]

I can insert the formula into the cell but can I get it to look for
end of the sheet minus 1 row?

 

[bpotter]

Works wonderful thankyou