Hi,

I'm new to macro programming.
I need a simple way to sum values in one column based on criteria in
another column eg number of items per country.

Im trying to program this with a array where each element points to a
User Defined Type (containing a string and double value) . I realize I
will need an outer loop to iterate through the entire column and an
inner loop to iterate through the array and add to value of same
country eg. every time I get to a row of France, I have to search for
France in my array and add to the exisiting item value or create a new
array entry for France.

Im getting tangled up in my code and have a feeling I'm doing this the
long way.
Would greatly appereciate some help:

Private Type Volumes
Country As String
Values As Double
End Type
Sub ComputeValues()

Dim rngToSearch As Range
Dim rngFound As Range
Dim mVolumes() As Volumes

Set rngToSearch = Sheets("Bill").Columns("B")
Set rngFound = rngToSearch.Find("Country")

If rngFound Is Nothing Then
MsgBox "No Range found"
Else
rngFound.Select

Set rng = Range(ActiveCell, ActiveCell.End(xlDown))

For Each cell In rng

'This is where I'm stuck

Next

End If

End Sub

Thanks alot in advance!

Hi

Why not just create a Pivot Table.
Place your cursor within the data tableDataPivot TableFinish
On the PT skeleton that appears on a new sheet,
Drag Country to the Row Area
Drag the item you wish to count to the Data area.
Double click on that field, and choose Count

--

Regards
Roger Govier

"Susan" wrote in message
...
Hi,

I'm new to macro programming.
I need a simple way to sum values in one column based on criteria in
another column eg number of items per country.

Im trying to program this with a array where each element points to a
User Defined Type (containing a string and double value) . I realize I
will need an outer loop to iterate through the entire column and an
inner loop to iterate through the array and add to value of same
country eg. every time I get to a row of France, I have to search for
France in my array and add to the exisiting item value or create a new
array entry for France.

Im getting tangled up in my code and have a feeling I'm doing this the
long way.
Would greatly appereciate some help:

Private Type Volumes
Country As String
Values As Double
End Type
Sub ComputeValues()

Dim rngToSearch As Range
Dim rngFound As Range
Dim mVolumes() As Volumes

Set rngToSearch = Sheets("Bill").Columns("B")
Set rngFound = rngToSearch.Find("Country")

If rngFound Is Nothing Then
MsgBox "No Range found"
Else
rngFound.Select

Set rng = Range(ActiveCell, ActiveCell.End(xlDown))

For Each cell In rng

'This is where I'm stuck

Next

End If

End Sub

Thanks alot in advance!

On Dec 12, 10:07 am, Susan wrote:
Hi,

I'm new to macro programming.
I need a simple way to sum values in one column based on criteria in
another column eg number of items per country.

Im trying to program this with a array where each element points to a
User Defined Type (containing a string and double value) . I realize I
will need an outer loop to iterate through the entire column and an
inner loop to iterate through the array and add to value of same
country eg. every time I get to a row of France, I have to search for
France in my array and add to the exisiting item value or create a new
array entry for France.

Im getting tangled up in my code and have a feeling I'm doing this the
long way.
Would greatly appereciate some help:

Private Type Volumes
Country As String
Values As Double
End Type
Sub ComputeValues()

Dim rngToSearch As Range
Dim rngFound As Range
Dim mVolumes() As Volumes

Set rngToSearch = Sheets("Bill").Columns("B")
Set rngFound = rngToSearch.Find("Country")

If rngFound Is Nothing Then
MsgBox "No Range found"
Else
rngFound.Select

Set rng = Range(ActiveCell, ActiveCell.End(xlDown))

For Each cell In rng

'This is where I'm stuck

Next

End If

End Sub

Thanks alot in advance!

Try the SUMIF function

Hi Roger,

Thanks for the prompt response.

I need to do it programmatically as it's not a once-off task.
So i'm trying to trigger this function with a button click.

Thanks

Susan

On Dec 12, 3:22 am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi

Why not just create a Pivot Table.
Place your cursor within the data tableDataPivot TableFinish
On the PT skeleton that appears on a new sheet,
Drag Country to the Row Area
Drag the item you wish to count to the Data area.
Double click on that field, and choose Count

--

Regards
Roger Govier

"Susan" wrote in message

...

Hi,

I'm new to macro programming.
I need a simple way to sum values in one column based on criteria in
another column eg number of items per country.

Im trying to program this with a array where each element points to a
User Defined Type (containing a string and double value) . I realize I
will need an outer loop to iterate through the entire column and an
inner loop to iterate through the array and add to value of same
country eg. every time I get to a row of France, I have to search for
France in my array and add to the exisiting item value or create a new
array entry for France.

Im getting tangled up in my code and have a feeling I'm doing this the
long way.
Would greatly appereciate some help:

Private Type Volumes
Country As String
Values As Double
End Type
Sub ComputeValues()

Dim rngToSearch As Range
Dim rngFound As Range
Dim mVolumes() As Volumes

Set rngToSearch = Sheets("Bill").Columns("B")
Set rngFound = rngToSearch.Find("Country")

If rngFound Is Nothing Then
MsgBox "No Range found"
Else
rngFound.Select

Set rng = Range(ActiveCell, ActiveCell.End(xlDown))

For Each cell In rng

'This is where I'm stuck

Next

End If

End Sub

Thanks alot in advance!

Hi,

I thought about SUMIF.
=SUMIF(G:G,"*countryValue*",E:E) is what i ideally need, where
countryValue is substituted by the respective country, but i am not
sure how to do this programmatically.

Thanks for responding

Susan

On Dec 12, 3:22 am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi

Why not just create a Pivot Table.
Place your cursor within the data tableDataPivot TableFinish
On the PT skeleton that appears on a new sheet,
Drag Country to the Row Area
Drag the item you wish to count to the Data area.
Double click on that field, and choose Count

--

Regards
Roger Govier

"Susan" wrote in message

...

Hi,

I'm new to macro programming.
I need a simple way to sum values in one column based on criteria in
another column eg number of items per country.

Im trying to program this with a array where each element points to a
User Defined Type (containing a string and double value) . I realize I
will need an outer loop to iterate through the entire column and an
inner loop to iterate through the array and add to value of same
country eg. every time I get to a row of France, I have to search for
France in my array and add to the exisiting item value or create a new
array entry for France.

Im getting tangled up in my code and have a feeling I'm doing this the
long way.
Would greatly appereciate some help:

Private Type Volumes
Country As String
Values As Double
End Type
Sub ComputeValues()

Dim rngToSearch As Range
Dim rngFound As Range
Dim mVolumes() As Volumes

Set rngToSearch = Sheets("Bill").Columns("B")
Set rngFound = rngToSearch.Find("Country")

If rngFound Is Nothing Then
MsgBox "No Range found"
Else
rngFound.Select

Set rng = Range(ActiveCell, ActiveCell.End(xlDown))

For Each cell In rng

'This is where I'm stuck

Next

End If

End Sub

Thanks alot in advance!

Hi,

I still need to do it programmatically as the function will be
triggered by button click in Excel.

Thanks for responding

Priya

Hi Susan

Assuming your countries are in column B and the values to be summed are in
column C
Also, assuming row 1 has headers, and that columns F and G are not being
used, then the following simple macro will achieve what you want.

Sub AddCountryValues()

Dim lr As Long
Columns("F:G").ClearContents
Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("F1"), Unique:=True
lr = Range("F65536").End(xlUp).Row
Range("G2").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],C[-1],C[-4])"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & lr), _
Type:=xlFillDefault

End Sub

Change all of the column references to suit

--

Regards
Roger Govier

"Susan" wrote in message
...
On Dec 12, 3:22 am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi

Why not just create a Pivot Table.
Place your cursor within the data tableDataPivot TableFinish
On the PT skeleton that appears on a new sheet,
Drag Country to the Row Area
Drag the item you wish to count to the Data area.
Double click on that field, and choose Count

--

Regards
Roger Govier

"Susan" wrote in message

...

Hi,

I'm new to macro programming.
I need a simple way to sum values in one column based on criteria in
another column eg number of items per country.

Im trying to program this with a array where each element points to a
User Defined Type (containing a string and double value) . I realize I
will need an outer loop to iterate through the entire column and an
inner loop to iterate through the array and add to value of same
country eg. every time I get to a row of France, I have to search for
France in my array and add to the exisiting item value or create a new
array entry for France.

Im getting tangled up in my code and have a feeling I'm doing this the
long way.
Would greatly appereciate some help:

Private Type Volumes
Country As String
Values As Double
End Type
Sub ComputeValues()

Dim rngToSearch As Range
Dim rngFound As Range
Dim mVolumes() As Volumes

Set rngToSearch = Sheets("Bill").Columns("B")
Set rngFound = rngToSearch.Find("Country")

If rngFound Is Nothing Then
MsgBox "No Range found"
Else
rngFound.Select

Set rng = Range(ActiveCell, ActiveCell.End(xlDown))

For Each cell In rng

'This is where I'm stuck

Next

End If

End Sub

Thanks alot in advance!

Hi,

I still need to do it programmatically as the function will be
triggered by button click in Excel.

Thanks for responding

Priya