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

Hi Joe,

The following assumes that your table of data is in Data A1:A8 with the
first row as column headers (CODE VALUE)

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Sheets("Output").Range("A2") = _
WorksheetFunction.SumIf(Sheets("Data") _
.Range("A2:A8"), "=XXX", Sheets("Data") _
.Range("B2:B8"))



--
Regards,

OssieMac


"joecrabtree" wrote:

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

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 meddelelsen
...
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

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

Hi Joe

Look at this:

With Sheets("Data")
LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).row
Set CodeRange = .Range("A2:A" & LastRow)
Set SumRange = .Range("B2:B" & LastRow)
End With
Set CriteriaRange = Sheets("Output").Range("A2")

Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange, SumRange)
Sheets("Output").Range("B2") = Total


Regards,
Per


On 12 Mar., 12:20, joecrabtree wrote:
On Mar 12, 10:08*am, "PerJessen" 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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

On Mar 13, 2:12*am, Per Jessen wrote:
Hi Joe

Look at this:

With Sheets("Data")
* * LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).row
* * Set CodeRange = .Range("A2:A" & LastRow)
* * Set SumRange = .Range("B2:B" & LastRow)
End With
Set CriteriaRange = Sheets("Output").Range("A2")

Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange, SumRange)
Sheets("Output").Range("B2") = Total

Regards,
Per

On 12 Mar., 12:20, joecrabtree wrote:

On Mar 12, 10:08*am, "PerJessen" 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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

When I run this code, I just get an output of zero in the output
worksheet, with no code names. Any ideas?

Thanks

Joe

Hi Joe

The previous macro didn't create a list of unique code names in the output
sheet.

Try this:

Sub AAA()
With Sheets("Data")
LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("A2:A" & LastRow)
Set SumRange = .Range("B2:B" & LastRow)
End With

Sheets("data").Activate
Range("A1", "A" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A1")
ActiveSheet.ShowAllData

Set CriteriaRange = Sheets("Output").Range("A2")
For r = 2 To Sheets("Output").Range("A2").End(xlDown).Row
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange, SumRange)
CriteriaRange.Offset(0, 1) = Total
Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next
End Sub

Regards,
Per

"joecrabtree" skrev i meddelelsen
...
On Mar 13, 2:12 am, Per Jessen wrote:
Hi Joe

Look at this:

With Sheets("Data")
LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).row
Set CodeRange = .Range("A2:A" & LastRow)
Set SumRange = .Range("B2:B" & LastRow)
End With
Set CriteriaRange = Sheets("Output").Range("A2")

Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange, SumRange)
Sheets("Output").Range("B2") = Total

Regards,
Per

On 12 Mar., 12:20, joecrabtree wrote:

On Mar 12, 10:08 am, "PerJessen" 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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

When I run this code, I just get an output of zero in the output
worksheet, with no code names. Any ideas?

Thanks

Joe