Enter Formula via VBA
 

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

Enter Formula via VBA
 

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" wrote in message
...
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

Enter Formula via VBA
 

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

Enter Formula via VBA
 

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)