PDA

View Full Version : Solved: UserForm event 2



Nicolaf
08-26-2011, 05:27 AM
Hi this is a follow up from my previous post UserForm event.

How can I amend the code to show not Sum of numbers but List of numbers?

Code


PrivateSub TextBox1_AfterUpdate()

With Worksheets("Sheet2")

Worksheets("Sheet1").Range("A1").Value = Application.SumIf(.Columns(2), Me.TextBox1.Text, .Columns(3))
End With

End Sub


Thanks,
Nix

Bob Phillips
08-26-2011, 06:08 AM
What list? You already have a list, that which is summed. Why would you want another copy of that?

Nicolaf
08-26-2011, 07:18 AM
Hi,

The list is the one below:

France Apples 2
Italy Apples 5
France Berries 4
Spain Cherries 1
Guatemala Bananas 6
Italy Pears 4
France Grapes 7
Italy Grapes 8
France Pears 8
Spain Apples 3

What I would like to see is not the Sum of numbers eg. if I choose Pears it would be 12 but the two numbers 4 and 8 that add to 12 starting from cell A1 in Sheet1. So I would have 4 in cell A1 and 8 in cell A2 (if more numbers then A3 etc.)

Thanks!
Nic

Kenneth Hobs
08-26-2011, 08:18 AM
You can post a link to a thread if it might relate.

Private Sub TextBox1_AfterUpdate()
Dim cell As Range
With Sheet2
'Worksheets("Sheet1").Range("A1").Value = Application.SumIf(.Columns(2), Me.TextBox1.Text, .Columns(3))
For Each cell In .Range("B1:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
If cell.Value2 = TextBox1.Text Then _
Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Offset(1).Value2 = cell.Offset(0, 1).Value2
Next cell
End With
End Sub

Bob Phillips
08-26-2011, 08:55 AM
Here is a formula solution.

Put the fruit in E2, then in F1 add this array formula and copy down

=IF(ISERROR(SMALL(IF($B$1:$B$10=$E$1,ROW($B$1:$B$10)),ROW(A1))),"",INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10=$E$1,ROW($B$1:$B$10)),ROW(A1))))

Nicolaf
09-01-2011, 03:09 AM
Great thanks!

:hi: