Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
When I enter a formula it shows the formula not the result | Excel Discussion (Misc queries) | |||
can you wrap formula results via a formula eg. Alt Enter | Excel Discussion (Misc queries) | |||
What does hitting Ctrl + Shift + Enter to enter a formula do??? Help a n00b out. | Excel Worksheet Functions | |||
Cannot enter formula in a cell after removing a circular formula | Excel Worksheet Functions |