Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|