Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mango
 
Posts: n/a
Default how to accumulate value and put in other sheet

Dear all, how to accumulate the value in table 1 and when next change value
or empty record recalculate and put in table 2 in different sheet.
How to do in vba?
Thanks


Table 1
Item value
2110 50
2110 40
2110 10
Empty record in between
2330 94
Empty record in between
2450 120

Table 2
Item value
2110 100
2330 94
2450 120


  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
have a look at 'Data - subtotals' and 'Data - pivot table'

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
Dear all, how to accumulate the value in table 1 and when next change

value
or empty record recalculate and put in table 2 in different sheet.
How to do in vba?
Thanks


Table 1
Item value
2110 50
2110 40
2110 10
Empty record in between
2330 94
Empty record in between
2450 120

Table 2
Item value
2110 100
2330 94
2450 120



  #3   Report Post  
mango
 
Posts: n/a
Default

dear frank, i mean using vba.
thanks

"Frank Kabel" wrote:

Hi
have a look at 'Data - subtotals' and 'Data - pivot table'

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
Dear all, how to accumulate the value in table 1 and when next change

value
or empty record recalculate and put in table 2 in different sheet.
How to do in vba?
Thanks


Table 1
Item value
2110 50
2110 40
2110 10
Empty record in between
2330 94
Empty record in between
2450 120

Table 2
Item value
2110 100
2330 94
2450 120




  #4   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
though this is possible I would really try a pivot table for this:
- much simpler to use
- no macros required

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank, i mean using vba.
thanks

"Frank Kabel" wrote:

Hi
have a look at 'Data - subtotals' and 'Data - pivot table'

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
Dear all, how to accumulate the value in table 1 and when next

change
value
or empty record recalculate and put in table 2 in different

sheet.
How to do in vba?
Thanks


Table 1
Item value
2110 50
2110 40
2110 10
Empty record in between
2330 94
Empty record in between
2450 120

Table 2
Item value
2110 100
2330 94
2450 120





  #5   Report Post  
mango
 
Posts: n/a
Default

but frank, this is for user to process themselves.
accumulate in 1 sheet and put in another sheet. u may refer to my table below.
frank, thanks

"Frank Kabel" wrote:

Hi
though this is possible I would really try a pivot table for this:
- much simpler to use
- no macros required

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank, i mean using vba.
thanks

"Frank Kabel" wrote:

Hi
have a look at 'Data - subtotals' and 'Data - pivot table'

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
Dear all, how to accumulate the value in table 1 and when next

change
value
or empty record recalculate and put in table 2 in different

sheet.
How to do in vba?
Thanks


Table 1
Item value
2110 50
2110 40
2110 10
Empty record in between
2330 94
Empty record in between
2450 120

Table 2
Item value
2110 100
2330 94
2450 120








  #6   Report Post  
mango
 
Posts: n/a
Default

Frank, now when i do the vlookup i can't get the right one. i know something
wrong in the code. can have yr advise.
1) do vlookup in tableB, if found put in the code.
2) accumulate amount in tableA for same code and put in other sheet
according to the code
3)can refer to example table below


Private Sub CommandButton1_Click()
Dim i As Long

Set TableA = Range("B15:B168")
Set tableB = Sheet4.Range("$A$2:$B$300")

'Set tableb = Range("Sheet4!$A$2:$B$300")
X = TableA.Cells(i, "B").Value
Z = TableA.Cells(i, "U").Value


For i = 15 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value = " " Then
' accumulate amount in TableB
Else
Y = Application.VLookup("*" & X & "*", tableB, 2, False)
Z = Y 'put code in TableA
End If
Next i





"mango" wrote:

but frank, this is for user to process themselves.
accumulate in 1 sheet and put in another sheet. u may refer to my table below.
frank, thanks

"Frank Kabel" wrote:

Hi
though this is possible I would really try a pivot table for this:
- much simpler to use
- no macros required

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank, i mean using vba.
thanks

"Frank Kabel" wrote:

Hi
have a look at 'Data - subtotals' and 'Data - pivot table'

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
Dear all, how to accumulate the value in table 1 and when next

change
value
or empty record recalculate and put in table 2 in different

sheet.
How to do in vba?
Thanks


Table 1
Item value
2110 50
2110 40
2110 10
Empty record in between
2330 94
Empty record in between
2450 120

Table 2
Item value
2110 100
2330 94
2450 120






  #7   Report Post  
Myrna Larson
 
Posts: n/a
Default

Just a comment: I think most users would be able to learn about pivot tables
much more easily than how to deal with a VBA macro that isn't behaving
correctly.

On Thu, 28 Oct 2004 23:10:02 -0700, "mango"
wrote:

but frank, this is for user to process themselves.
accumulate in 1 sheet and put in another sheet. u may refer to my table

below.
frank, thanks

"Frank Kabel" wrote:

Hi
though this is possible I would really try a pivot table for this:
- much simpler to use
- no macros required

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank, i mean using vba.
thanks

"Frank Kabel" wrote:

Hi
have a look at 'Data - subtotals' and 'Data - pivot table'

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
Dear all, how to accumulate the value in table 1 and when next

change
value
or empty record recalculate and put in table 2 in different

sheet.
How to do in vba?
Thanks


Table 1
Item value
2110 50
2110 40
2110 10
Empty record in between
2330 94
Empty record in between
2450 120

Table 2
Item value
2110 100
2330 94
2450 120







  #8   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
I agree with Myrna
pivot tables are really the way to go. They are even for novice users quite
simple, provide drill-down features, etc.

All macro solutions would either requi
- manually click a button / an icon to start the macro
- an event procedure that may even disturb a user and could be getting
difficult if you change data.
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


If you really want to use a macro solution maybe someone else will provide
you with the code :-)


So I would really suggest to do the following:
- remove the blank rows in between (no real need for them - they just make
formulas and everythzing else more complicated)
- setup a pivot table (I've provided some links to get started with them)
- setup 1 or more nice pre-formated pivot tables and your users will
probably be able to use them without any problems



"mango" wrote:

but frank, this is for user to process themselves.
accumulate in 1 sheet and put in another sheet. u may refer to my table below.
frank, thanks

"Frank Kabel" wrote:

Hi
though this is possible I would really try a pivot table for this:
- much simpler to use
- no macros required

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank, i mean using vba.
thanks

"Frank Kabel" wrote:

Hi
have a look at 'Data - subtotals' and 'Data - pivot table'

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
Dear all, how to accumulate the value in table 1 and when next

change
value
or empty record recalculate and put in table 2 in different

sheet.
How to do in vba?
Thanks


Table 1
Item value
2110 50
2110 40
2110 10
Empty record in between
2330 94
Empty record in between
2450 120

Table 2
Item value
2110 100
2330 94
2450 120






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"