Reading values into an array of User Defined Types

[Susan]

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!

 

[Roger Govier]

Hi

Why not just create a Pivot Table.
Place your cursor within the data table>Data>Pivot Table>Finish
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

 

[gavin.bird]

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

 

[Susan]

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

 

[Susan]

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

 

[Susan]

Hi

Why not just create a Pivot Table.
Place your cursor within the data table>Data>Pivot Table>Finish
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

Hi,

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

Thanks for responding

Priya

 

[Roger Govier]

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

 

[Bill Renaud]

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

Turn on the Macro Recorder while generating the pivot table. Post the
resulting code back here. Somebody will help you clean it up (substitute
variables, etc.), so that it is robust and works in the future, regardless
of size of data.