Reading values into an array of User Defined Types
 

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!

Reading values into an array of User Defined Types
 

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!

Reading values into an array of User Defined Types
 

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

Reading values into an array of User Defined Types
 

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!

Reading values into an array of User Defined Types
 

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

Reading values into an array of User Defined Types
 

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

Reading values into an array of User Defined Types
 

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