Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

bringing subtotal to another sheet

  Asked By: Cory    Date: Feb 24    Category: MS Office    Views: 656
  

I would be very happy if some one solves my simple compiling error.

MyRange is in sheet1

A B C
1 date Item1Sales Item2Sales
2 01/09/07 200 500
3 01/09/07 300 200
4 02/09/07 500 800
5 02/09/07 100 200
6 02/09/07 600 800
7 03/09/07 100 600
8 03/09/07 500 700

12 Enter Date :01/09/07
13 SubTotal Item1 :{=SUMIFA2:A8,"="&C12,B2:B8)}
14 SubTotal Item2 : {=SUMIFA2:A8,"="&C12,C2:C8)}

After clicking SubTotal Button the input date subtotals should display in
Sheet2 d2 & E2

MyCodings:

After declaring the variables

Sub SubTotal_button_click()

Range("C13").Select
subtot_Item1 = ActiveCell.Value

Range("C14").Select
subtot_Item2 = ActiveCell.Value

Sheets("sheet2").Select

Range("D2").Select
ActiveCell.Value = subtot_Item1

Range("E2").Select
ActiveCell.Value = subtot_Item2


End Sub

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Noel Peterson     Answered On: Feb 24

The coding of the sub works fine (at least here on Excel 2003) however
there are some points to mention about both formulae on the worksheet:

1.Your formula: {=SUMIFA2:A8,"="&C12,B2:B8)}
You've got curly brackets around it, I'm guessing you typed these into
the message yourself, manually, rather than copied them, as it's what
you can see in the formula bar. This means you've array entered it
(held down the Ctrl and Alt keys while pressing Enter), which in this
case is not necessary. Committing the formula with just Enter is fine.

2. Your formulae are both missing an open parentheses directly after
the F of SumIf.

3. The part of your formula:
"="&C12
doesn't need to be so complicated, just
C12
will do. So your final formula can be:
=SUMIF(A2:A8,C12,B2:B8)

The same applies to both formulae.

 
Didn't find what you were looking for? Find more on bringing subtotal to another sheet Or get search suggestion and latest updates.




Tagged: