Enter Formula via VBA

[Bobby]

Greetings
I have a file that is imported daily and it varies as to the number of lines
in the file each day. Using a macro I woud like find the next available row
and enter the following formula in ColH:ColAE (24 Cols)
=SUMPRODUCT(--($F$3:$F$66="Cat"),--($G$3:$G$66="Bird"),--
($C$3:$C$66<>"Black"),--($C$3:$C$66<>"White"),H3:H66)
The example file had 66 rows of data and this is the part that could change
each day.
Thanks

[Tom Ogilvy]

set rng = cells(rows.count,"H").End(xlup)(2)
rng.Resize(1,24).formulaR1C1 = "=Sumproduct(--(R3C5:R[-1]C5=""Cat"")," & _
"--(R3C6:R[-1]C6=""Bird""),--(R3C3:R[-1]C3<>""Black"")," & _
"--(R3C3:R[-1]C3<>""White""),R3C:R[-1]C)"

an alternative

set rng = cells(rows.count,"H").End(xlup)(2)
s = "=SUMPRODUCT(--($F$3:$F$66=""Cat""),--($G$3:$G$66" & _
"=""Bird""),--($C$3:$C$66<>""Black"")," & _
"--($C$3:$C$66<>""White""),H3:H66)"
s = Replace(s,"66",rng.offset(-1,0).row)
rng.Resize(1,24).Formula = s
--
Regards,
Tom Ogilvy


"Bobby" <[email protected]> wrote in message
news:[email protected]...
> Greetings
> I have a file that is imported daily and it varies as to the number of
lines
> in the file each day. Using a macro I woud like find the next available
row
> and enter the following formula in ColH:ColAE (24 Cols)
> =SUMPRODUCT(--($F$3:$F$66="Cat"),--($G$3:$G$66="Bird"),--
> ($C$3:$C$66<>"Black"),--($C$3:$C$66<>"White"),H3:H66)
> The example file had 66 rows of data and this is the part that could
change
> each day.
> Thanks
>

[Bill Martin]

Bobby wrote:
> Greetings
> I have a file that is imported daily and it varies as to the number of lines
> in the file each day. Using a macro I woud like find the next available row
> and enter the following formula in ColH:ColAE (24 Cols)
> =SUMPRODUCT(--($F$3:$F$66="Cat"),--($G$3:$G$66="Bird"),--
> ($C$3:$C$66<>"Black"),--($C$3:$C$66<>"White"),H3:H66)
> The example file had 66 rows of data and this is the part that could change
> each day.
> Thanks
>
--------------------

Tom gave you the "right" answer. Here's a cheezier one which is perhaps easier
to understand:

dim LastRow as Long
dim ColIndex as Long
LastRow = Range("H1").end(xlDown).row
for ColIndex = 8 to 31
cells(LastRow,ColIndex) = "=SUMPRODUCT(...etc...."
next ColIndex

Bill

[Bill Martin]

Bobby wrote:
> Greetings
> I have a file that is imported daily and it varies as to the number of lines
> in the file each day. Using a macro I woud like find the next available row
> and enter the following formula in ColH:ColAE (24 Cols)
> =SUMPRODUCT(--($F$3:$F$66="Cat"),--($G$3:$G$66="Bird"),--
> ($C$3:$C$66<>"Black"),--($C$3:$C$66<>"White"),H3:H66)
> The example file had 66 rows of data and this is the part that could change
> each day.
> Thanks
>
--------------------

Tom gave you the "right" answer. Here's a cheezier one which is perhaps easier
to understand:

dim LastRow as Long
dim ColIndex as Long
LastRow = Range("H1").end(xlDown).row
for ColIndex = 8 to 31
cells(LastRow + 1,ColIndex) = "=SUMPRODUCT(...etc...."
next ColIndex

Bill (Small change edited into code)