excel - How to fill a combobox with a dataset that matches a specific date -

i creating userform in excel allows user review records entered on selected date. date selected using combobox populated dates current week. other actions triggered command button.

i trying figure out how populate review combobox and/or review listbox data named range matches selected date. appreciated. following code includes vlookup command deadend.

worksheet: data_entry
named range: records_entered
userform: ufrmdataentry1
date combobox: cboreviewweek
command button: cmdreviewcount
review combobox: cboreviewrecords
review listbox: lstreviewrecords

private sub userform_initialize()     'fill combobox     me.cboreviewweek.list=[index(text(today()-weekday(today(),2)+row(1:7),"mm/dd/yyyy"),)] end sub  private sub cmdreviewcount_click()     'step 1) pass selection of cboreviewweek "formulas" sheet     activeworkbook.sheets("formulas").range("a4") = me.cboreviewweek     'step 2) return count of total records entered on selected date     me.txtreviewcount = activeworkbook.sheets("formulas").range("a5")     'step 3) return records entered listbox     var1 = worksheetfunction.vlookup(cboreviewweek.value, worksheets("data_entry").range("records_entered"), 2, false)     lstreviewrecords.value = var1     cboreviewrecords.value = var1     'me.lstreviewrecords.list = activeworkbook.sheets("data entry").range("records_entered")     'is broke here end sub 

there several ways results wish. solution may depend upon 2 issues didn't mention -- whether you're storing dates or datetimes, , whether data sorted date entered or in other order. latter critical -- if data sorted date entered, group of records you're looking contiguous. if not, scattered through worksheet.

assuming records in order, need find start , end rows. assuming date looking in records_entered range in first column -- if not, you'll need change "1"'s in code match.

dim r range, numrows integer  numrows=worksheets("data_entry").range("records_entered").rows.count set r=worksheets("data_entry").range("records_entered").columns(1).find(what:=cboreviewweek.value, after:=worksheets("data_entry").cells(numrows,1)) while r.value=cboreviewweek.value   cboreviewreviewrecords.additem r.value (or, if want return second column in vlookup above, use r.offset(0,1).value)   set r=r.offset(1,0) loop 

the "after" attribute in find needed because, unfortunately, excel starts search first cell , looks @ cells after -- i.e. if date selected first date in range, find result in second row. starting search in last row, forces excel wrap the first row start search.

if records not sorted in order, or if want more flexible solution, use findnext instead:

dim r range, numrows integer, firstcell range  numrows=worksheets("data_entry").range("records_entered").rows.count set r=worksheets("data_entry").range("records_entered").columns(1).find(what:=cboreviewweek.value, after:=worksheets("data_entry").cells(numrows,1)) set firstcell = nothing while not r nothing , r<>firstcell   if firstcell nothing set firstcell = r   cboreviewreviewrecords.additem r.value (or, if want return second column in vlookup above, use r.offset(0,1).value)   set r=worksheets("data_entry").range("records_entered").columns(1).findnext loop 

if have date/time's in column you're looking at, gets more complicated

hopefully gets headed in right direction