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