Solved: Fill 2-column list
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.
Hi Kaiser,
Set or change the range name as required for Column C data only
[vba]
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
[/vba]
Last edited by mdmackillop; 09-26-2006 at 09:44 AM. Reason: code revision
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thank you mdmackillop. It worked just fine.
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.
[vba]
Arr(i, 1) = ActiveSheet.Evaluate( _
"SUMPRODUCT(--(" & Rg.Address & "=" & a(i) & ")," & _
"--(" & Rng2.Address & "=""" & some_text_value & """)," & _
Rg.Offset(, 1).Address & ")")
[/vba]
as an example
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.
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".Originally Posted by xld
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?
[VBA]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 & ")")[/VBA]
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().
You missed the quotes round the category
[vba]
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
[/vba]
Thank you xld. This really was a finishing touch.
Posting Permissions
Reply With Quote
