+ Reply to Thread
Results 1 to 4 of 4

Variable Formula - both normal and array

  1. #1
    willwonka
    Guest

    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. #2
    Tom Ogilvy
    Guest

    RE: 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. #3
    willwonka
    Guest

    Re: 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. #4
    willwonka
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1