Variable Formula - both normal and array

I've seen some similar topics to this; but I just can't seem to adapt
it. First I apologize for my novice-ness

I have a spreadsheet where the "data" portion can be variable (It is a
Pivot Table). That is why I find the Last Row because I then need to
add some lines directly under the Pivot Table.

I am having trouble trying to put those formulas into the spreadsheet.

First Formula is a simple SUM. =SUM(D175:D182). As you see below, I
just can't get it right.

Second Formula is an Array Formula -
{SUM(SUMIF($C$1:$C$175,data2,D1:D175))} but I didn't even come close on
this one.

My question is how to code the macro so that it puts the formula in the
spreadsheet.

Note: cLastRow = 175
"data2" is a range name in spreadsheet. It is the values that I am
summing.
Thanks.



Dim cLastRow, cLastcol
Sheets("Initial by Laser").Activate
cLastRow = Columns("A:A").Find(What:="Grand Total",
LookAt:=xlWhole).Row

Range("D" & cLastRow + 2).Formula = "=-d7"
Range("D" & cLastRow + 10).Formula = "=Sum(D & cLastRow & : & D &
cLastRow + 8)"
'Range("D" & cLastRow + 2).FormulaArray = {SUM(SUMIF("$C$1" & ":"&
"$C$"&clastrow,data1,"D1" & ":" & "D"&clastrow))}
'Range("D" & cLastRow + 2) = SUM(SUMIF($C$1:$C$175,data2,D1:D175))

Your sumif formula is not an array formula and you don't need to put it
inside Sum since it only returns a single value.

Range("D" & cLastRow + 2).Formula = "=-d7"
Range("D" & cLastRow + 10).Formula = "=Sum(D" & cLastRow & ":D" & _
cLastRow + 8 & ")"
Range("D" & cLastRow + 2).Formula = "=SUMIF($C$1:$C" & _
clastrow & ",data1,D1:D" & clastrow)

--
Regards,
Tom Ogilvy




"willwonka" wrote:

> I've seen some similar topics to this; but I just can't seem to adapt
> it. First I apologize for my novice-ness
>
> I have a spreadsheet where the "data" portion can be variable (It is a
> Pivot Table). That is why I find the Last Row because I then need to
> add some lines directly under the Pivot Table.
>
> I am having trouble trying to put those formulas into the spreadsheet.
>
> First Formula is a simple SUM. =SUM(D175:D182). As you see below, I
> just can't get it right.
>
> Second Formula is an Array Formula -
> {SUM(SUMIF($C$1:$C$175,data2,D1:D175))} but I didn't even come close on
> this one.
>
> My question is how to code the macro so that it puts the formula in the
> spreadsheet.
>
> Note: cLastRow = 175
> "data2" is a range name in spreadsheet. It is the values that I am
> summing.
> Thanks.
>
>
>
> Dim cLastRow, cLastcol
> Sheets("Initial by Laser").Activate
> cLastRow = Columns("A:A").Find(What:="Grand Total",
> LookAt:=xlWhole).Row
>
> Range("D" & cLastRow + 2).Formula = "=-d7"
> Range("D" & cLastRow + 10).Formula = "=Sum(D & cLastRow & : & D &
> cLastRow + 8)"
> 'Range("D" & cLastRow + 2).FormulaArray = {SUM(SUMIF("$C$1" & ":"&
> "$C$"&clastrow,data1,"D1" & ":" & "D"&clastrow))}
> 'Range("D" & cLastRow + 2) = SUM(SUMIF($C$1:$C$175,data2,D1:D175))
>
>

Oops... The Array formula is =sum(SUMIF($C$1:$C" & clastrow &
",data1,D1:D" & clastrow))

I will try similar syntax.

Thanks.


Tom Ogilvy wrote:
> Your sumif formula is not an array formula and you don't need to put it
> inside Sum since it only returns a single value.
>
> Range("D" & cLastRow + 2).Formula = "=-d7"
> Range("D" & cLastRow + 10).Formula = "=Sum(D" & cLastRow & ":D" & _
> cLastRow + 8 & ")"
> Range("D" & cLastRow + 2).Formula = "=SUMIF($C$1:$C" & _
> clastrow & ",data1,D1:D" & clastrow)
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "willwonka" wrote:
>
> > I've seen some similar topics to this; but I just can't seem to adapt
> > it. First I apologize for my novice-ness
> >
> > I have a spreadsheet where the "data" portion can be variable (It is a
> > Pivot Table). That is why I find the Last Row because I then need to
> > add some lines directly under the Pivot Table.
> >
> > I am having trouble trying to put those formulas into the spreadsheet.
> >
> > First Formula is a simple SUM. =SUM(D175:D182). As you see below, I
> > just can't get it right.
> >
> > Second Formula is an Array Formula -
> > {SUM(SUMIF($C$1:$C$175,data2,D1:D175))} but I didn't even come close on
> > this one.
> >
> > My question is how to code the macro so that it puts the formula in the
> > spreadsheet.
> >
> > Note: cLastRow = 175
> > "data2" is a range name in spreadsheet. It is the values that I am
> > summing.
> > Thanks.
> >
> >
> >
> > Dim cLastRow, cLastcol
> > Sheets("Initial by Laser").Activate
> > cLastRow = Columns("A:A").Find(What:="Grand Total",
> > LookAt:=xlWhole).Row
> >
> > Range("D" & cLastRow + 2).Formula = "=-d7"
> > Range("D" & cLastRow + 10).Formula = "=Sum(D & cLastRow & : & D &
> > cLastRow + 8)"
> > 'Range("D" & cLastRow + 2).FormulaArray = {SUM(SUMIF("$C$1" & ":"&
> > "$C$"&clastrow,data1,"D1" & ":" & "D"&clastrow))}
> > 'Range("D" & cLastRow + 2) = SUM(SUMIF($C$1:$C$175,data2,D1:D175))
> >
> >

Worked like a charm.. Thanks.

willwonka wrote:
> Oops... The Array formula is =sum(SUMIF($C$1:$C" & clastrow &
> ",data1,D1:D" & clastrow))
>
> I will try similar syntax.
>
> Thanks.
>
>
> Tom Ogilvy wrote:
> > Your sumif formula is not an array formula and you don't need to put it
> > inside Sum since it only returns a single value.
> >
> > Range("D" & cLastRow + 2).Formula = "=-d7"
> > Range("D" & cLastRow + 10).Formula = "=Sum(D" & cLastRow & ":D" & _
> > cLastRow + 8 & ")"
> > Range("D" & cLastRow + 2).Formula = "=SUMIF($C$1:$C" & _
> > clastrow & ",data1,D1:D" & clastrow)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "willwonka" wrote:
> >
> > > I've seen some similar topics to this; but I just can't seem to adapt
> > > it. First I apologize for my novice-ness
> > >
> > > I have a spreadsheet where the "data" portion can be variable (It is a
> > > Pivot Table). That is why I find the Last Row because I then need to
> > > add some lines directly under the Pivot Table.
> > >
> > > I am having trouble trying to put those formulas into the spreadsheet.
> > >
> > > First Formula is a simple SUM. =SUM(D175:D182). As you see below, I
> > > just can't get it right.
> > >
> > > Second Formula is an Array Formula -
> > > {SUM(SUMIF($C$1:$C$175,data2,D1:D175))} but I didn't even come close on
> > > this one.
> > >
> > > My question is how to code the macro so that it puts the formula in the
> > > spreadsheet.
> > >
> > > Note: cLastRow = 175
> > > "data2" is a range name in spreadsheet. It is the values that I am
> > > summing.
> > > Thanks.
> > >
> > >
> > >
> > > Dim cLastRow, cLastcol
> > > Sheets("Initial by Laser").Activate
> > > cLastRow = Columns("A:A").Find(What:="Grand Total",
> > > LookAt:=xlWhole).Row
> > >
> > > Range("D" & cLastRow + 2).Formula = "=-d7"
> > > Range("D" & cLastRow + 10).Formula = "=Sum(D & cLastRow & : & D &
> > > cLastRow + 8)"
> > > 'Range("D" & cLastRow + 2).FormulaArray = {SUM(SUMIF("$C$1" & ":"&
> > > "$C$"&clastrow,data1,"D1" & ":" & "D"&clastrow))}
> > > 'Range("D" & cLastRow + 2) = SUM(SUMIF($C$1:$C$175,data2,D1:D175))
> > >
> > >