PDA

View Full Version : Solved: Fill an array with vlookup vba



SBrooky
10-04-2012, 05:19 AM
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:
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

Any ideas? Ive attached my sheet to make explanation easier

SBrooky
10-05-2012, 08:12 AM
are we allowed to bump posts on this forum?

abdulla
10-05-2012, 08:59 AM
Did you mean as in the attached file?

CodeNinja
10-05-2012, 10:56 AM
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...

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

SBrooky
10-08-2012, 02:04 AM
Perfect, thanks alot codeninja. Learnt alot from this aswell so thanks for all your help.

snb
10-08-2012, 05:37 AM
or



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