Code Question?

[Todd_M]

Hi-
If I have a listbox with dates in column 1 and amounts in column 2 and I click on a date (April 26), how can I find the amount for that date and all other amounts with the same date also to be listed in textbox1.

column 1 column 2
april 26 $3.00
april 26 $8.00
april 27 $5.00
april 26 $4.00
april 28 $2.00

When the user clicks on april 26 then the textbox needs to read $15.00.

Private Sub ListBox2_Click()
For Each h In ActiveSheet.range("a1:a100") If h.Value = ListBox2.Value Then
??????
End If
Next h
End Sub

[Mark O'Brien]

Private Sub ListBox2_Click()
For Each h In ActiveSheet.range("a1:a100") If h.Value = ListBox2.Value Then
??????
End If
Next h
End Sub

Actually there's a builtin function that will do this for you, "SumIf". (I don't usually use these in code, so don't tell anyone.

Private Sub ListBox2_Click()

  Dim oDateRange As Range

  Dim oMoneyRange As Range

  

  Set oDateRange = Sheets("Sheet1").Range("A2:A6")  'Date Range

  Set oMoneyRange = Sheets("Sheet1").Range("B2:B6") 'Money Range

  

  TextBox1.Text = Application.WorksheetFunction.SumIf(oDateRange, ListBox2.Text, oMoneyRange)

 End Sub

I stuck your sample data in A2:B6. So I hard coded those data ranges in, if this is a variable range, do a search on this board for dynamic ranges. (typically they use code that contains End(xlUp) or something.

There may be a problem with the syntax at Listbox2.Text, since I didn't test this with a listbox.

Anyway, I hope this helps. If you need further help just repost.

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-04-26 10:18 ]

[Todd_M]

Thankyou so much Mark-

Unfortanatly the bound column of the list box was the 9th column which needs to be that way for other purposes and the money range was in column G so using your code and some of mine, this works great:


For Each h In ActiveSheet.range("i2:i100") If h.Value = ListBox2.Value Then
x = h.Offset(0, -8).Value
Dim oDateRange As range
Dim oMoneyRange As range
Set oDateRange = ActiveSheet.range("A2:A100") 'Date Range
Set oMoneyRange = ActiveSheet.range("g2:g100") 'Money Range
TextBox3.Text = Format((Application.WorksheetFunction.SumIf(oDateRange, x, oMoneyRange)), "$#,##0.00")
End If
Next h

[Mark O'Brien]

Just curious, what is "h" in your code?