Solved: Fill an array with vlookup vba

[SBrooky]

I have a listbox and a MonthView. I want to know how to get all the dates from column B that have the same value as the selected in the listbox from column A.

this is my attempt:
[VBA]Private Sub MonthView1_Click()
Dim bolddate As Date
Dim dates As Range
Dim i As Integer

Set dates = Application.WorksheetFunction.VLookup(ListBox1.Value, Sheet.Range("A:B"), 2, False)
For i = 1 To 34
If dates(i) > MonthView1.Month Then MonthView1.DayBold(dates(i)) = True
Next i

End Sub[/VBA]

Any ideas? Ive attached my sheet to make explanation easier

[SBrooky]

are we allowed to bump posts on this forum?

[abdulla]

Did you mean as in the attached file?

[CodeNinja]

sbrooky,
Not quite sure exactly what you want to do with the array once you have it, but here is one option to build it...

[VBA]Private Sub MonthView1_Click()

Dim dates() As Date 'the array of dates associated with chosen listbox
Dim i As Integer 'for looping

ReDim dates(1 To 1)

'loop through rows
For i = 1 To Sheet1.Range("B65536").End(xlUp).Row
If Sheet1.Cells(i, 1) = Me.ListBox1.Value Then
'if there is a match, incriment and update ubound(dates)
'the first time, dates will be un-used and will be "12:00:00 AM"
If dates(UBound(dates)) <> "12:00:00 AM " Then
ReDim Preserve dates(1 To UBound(dates) + 1) ' incriment ubound(dates)
End If
dates(UBound(dates)) = Sheet1.Cells(i, 2)
End If
Next i

'not sure what you want to do with the array, but I just printed it out for you to see
For i = 1 To UBound(dates)
Debug.Print dates(i)
Next i

End Sub[/VBA]

[SBrooky]

Perfect, thanks alot codeninja. Learnt alot from this aswell so thanks for all your help.

[snb]

or

[VBA]

Private Sub MonthView1_Click()
cells(1,3)=ListBox1.Value
sn=filter([transpose(if(A1:A1000=C1,B1:B1000))],"false",false)
end sub

[/VBA]