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

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---

--
RyGuy


"bpotter" wrote:

> 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
>

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" <(E-Mail Removed)> wrote in message
news:ae9c314b-55a6-4522-8d77-(E-Mail Removed)...
>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

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

Works wonderful thankyou