PDA

View Full Version : Solved: Fill 2-column list



Kaizer
09-26-2006, 08:00 AM
Hi there.

I've got categories sitting in column C and amounts sitting in column D. What I would like is the 2-column list is filled when a user form gets open. The first column should get non-duplicate categories and the other column is the summary value for that category. Can you help with code?

thanks in advance.

mdmackillop
09-26-2006, 09:36 AM
Hi Kaiser,
Set or change the range name as required for Column C data only


Option Explicit

Private Sub UserForm_Initialize()
Listing
End Sub

Sub Listing()
Dim d, a
Dim Arr()
Dim Rg As Range, Cel As Range
Dim i As Long
Set Rg = Range("Data")
Set d = CreateObject("Scripting.Dictionary")
For Each Cel In Rg
On Error Resume Next
d.Add Cel.Text, Cel.Text
Next
a = d.Items
ReDim Arr(d.Count - 1, 1)
For i = 0 To d.Count - 1
Arr(i, 0) = a(i)
Arr(i, 1) = Application.WorksheetFunction.SumIf(Rg, a(i), Rg.Offset(, 1))
Next
ListBox1.List = Arr
End Sub

Kaizer
09-26-2006, 11:41 AM
Thank you mdmackillop. It worked just fine.

Kaizer
09-27-2006, 07:44 AM
mdmackillop, I just realized that I need to update my list based on more than one parameter. This means that Application.WorksheetFunction.SumIf... will not work. Is there a way to go around this? In my case I will need to sum up by Category/Region/Date.

Bob Phillips
09-27-2006, 08:00 AM
Arr(i, 1) = ActiveSheet.Evaluate( _
"SUMPRODUCT(--(" & Rg.Address & "=" & a(i) & ")," & _
"--(" & Rng2.Address & "=""" & some_text_value & """)," & _
Rg.Offset(, 1).Address & ")")


as an example

Kaizer
09-27-2006, 08:50 AM
Have to admit that I don't know how this Evaluate thing works. What is "--"? Anyway, can you show how it should be usung the attached file please? I change the Period or Region cmb and based on that the list should be filled.

Thanks again.

Bob Phillips
09-27-2006, 09:13 AM
It would help to know what to test and what to sum in this multiple criteria test.

Kaizer
09-27-2006, 09:51 AM
It would help to know what to test and what to sum in this multiple criteria test.

The list should get Accounts summed based on 2 criterias: (1) Region and (2) Period. For example Region="France" and Period="Aug".

Kaizer
09-28-2006, 01:11 AM
Arr(i, 1) = ActiveSheet.Evaluate( _
"SUMPRODUCT(--(" & Rg.Address & "=" & a(i) & ")," & _
"--(" & Rng2.Address & "=""" & some_text_value & """)," & _
Rg.Offset(, 1).Address & ")")


hm, I tryied to understand the Evaluate method. I think I am not far from the finish. But still missing something as the procedure still doesn't work. Can you help to make the finishing touch and correct the code below please? :banghead:
Arr(i, 0) = a(i)
Arr(i, 1) = Evaluate("SUMPRODUCT((" & Rg.Address & "=" & a(i) & ")*(" & _
r_rgn.Address & "=" & Chr(34) & Me.cmbRegion.Value & Chr(34) & ")*(" & _
r_Date.Address & "=" & Chr(34) & Me.cmbPeriod.Value & Chr(34) & ")*" & _
r_Amount.Address & ")")

Bob Phillips
09-28-2006, 04:20 AM
Post your latest workbook please with that formula embedded so I can see exactly what you are doing.

Kaizer
09-28-2006, 04:29 AM
Post your latest workbook please with that formula embedded so I can see exactly what you are doing.

Please, see attached. Have a look at Sub Listing().

Bob Phillips
09-28-2006, 05:49 AM
You missed the quotes round the category



a = d.Items
ReDim Arr(d.Count - 1, 1)
For i = 0 To d.Count - 1
Arr(i, 0) = a(i)
Arr(i, 1) = Evaluate("SUMPRODUCT((" & Rg.Address & "=""" & a(i) & """)*(" & _
r_rgn.Address & "=""" & Me.cmbRegion.Value & """)*(" & _
r_Date.Address & "=""" & Me.cmbPeriod.Value & """)*" & _
r_Amount.Address & ")")
Next

Kaizer
09-28-2006, 06:16 AM
Thank you xld. This really was a finishing touch. :)