| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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)) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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)) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| array to normal version formula | Excel Discussion (Misc queries) | |||
| use a variable array in a formula | Excel Discussion (Misc queries) | |||
| Convert Normal formula to array formula | Excel Programming | |||
| Normal Random Variable Generator | Excel Programming | |||
| Problem trying to us a range variable as an array variable | Excel Programming | |||
Linear Mode
