On Mar 12, 10:08*am, "Per Jessen" wrote:
Hi

Why not use the SumIf function. Insert the formula below in B2 on output
sheet, and enter codes (XXX, BBB ...) in A2 and down. Then copy down the
formula as required.

=SUMIF(DATA!$A$2:$A$8,A2,DATA!$B$2:$B$8)

With VBA I would use the same function:

Set CodeRange = Sheets("Data").Range("A2:A8")
Set CriteriaRange = Sheets("Output").Range("A2")
Set SumRange = Sheets("Data").Range("B2:B8")
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange, SumRange)
Sheets("Output").Range("B2") = Total

Regards,
Per

"joecrabtree" skrev i ...

All,

I have a worksheet called 'data' in which I have two colounms of data
shown below:

CODE * *VALUE
XXX * * * * 1
XXX * * * * 22
XXX * * * * 21
XXX * * * * 45
BBB * * * 64
NNN * * * 54
AAA * * * 64

Using VBA how can I add up all the XXX codes and produce an output
total in a worksheet labelled 'output'?

For example in this case the worksheet 'output' would display

XXX * * *89

Thanks for your help,

Regards

Joe Crabtree

Thanks. And if the range was undefined, i.e if the list was longer
than A2: A8 and could vary depending on the data set used. How would I
incorporate that?

Thanks

Joe