Solved: UserForm event 2

[Nicolaf]

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

[vba]
PrivateSub TextBox1_AfterUpdate()

With Worksheets("Sheet2")

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

End Sub
[/vba]

Thanks,
Nix

[Bob Phillips]

What list? You already have a list, that which is summed. Why would you want another copy of that?

[Nicolaf]

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]

You can post a link to a thread if it might relate.

[VBA]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[/VBA]

[Bob Phillips]

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]

Great thanks!