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

Variable Formula - both normal and array
 

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

Variable Formula - both normal and array
 

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

Variable Formula - both normal and array
 

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