Assign values to list box array

[duncanmcl]

Trying to create a list box with values in a column = value . Using VBA I was able to cycle true the records = value...in trying to populate a list box with those field values...error...it seems an array was needed to be defined....found code and adapted to my logic and it populated list box with values row1, col2, col3, etc row2, col2 etc. Can't see logic to assign my values to each for my match process mentioned above. Let me qualify my lack of good VBA code.....my experience is from Access VBA and I find Excel VBA very hard

Private Sub LoadBCDetail()
Me.BCDetail.ColumnCount = 5
Me.BCDetail.ColumnWidths = ".5in;.5in;.5in;.5in;.5in"
' populating variable
Const rows = 30
Const cols = 5
Dim listarray(1 To rows, 1 To cols) As Variant
For Row = 1 To rows
For col = 1 To cols
If col = 1 Then
listarray(Row, col) = "row" & Row 'ActiveCell(I, 0).Value '"row" & row
Else
listarray(Row, col) = "col" & col 'ActiveCell(I, 1).Value '"col" & col
End If
Next col
Next Row
'Transferring values from array to Prjlist.
Me.BCDetail.List = listarray
Me.BCDetail.ListIndex = -1 'No item selected
End Sub

Private Sub UserForm_Initialize()
Dim cLoc2 As Range
Dim zZ As Long
Dim PayeeN As String
PayeeN = "003943"
FindLastRow2
Set Ws2 = Worksheets("q_report_detail")
Dim I As Long
I = 2
'With Me.BCDetail
Zzz = 2
With Ws2
'MsgBox ActiveCell(I, 1).Value & "-" & PayeeN
Do While I < 100 'ActiveCell(I, 1).Value = PayeeN 'And Not .EOF 'And I < 10 '= PayeeN 'Ws2.Range("payeebc").Value = PayeeN
rownumber = Zzz
LoadBCDetail
With listarray
If ActiveCell(I, 1) = PayeeN Then
End If
I = I + 1
End With
Loop
End With

 

[Norie]

What match?

Are you trying to only populate the listbox with data from rows where a particular column is a particular value?

eg all the rows with 'Frdge' in the first column

 

[duncanmcl]

yes

 

[Norie]

Then you should just need an If and a little work with the array.

Something like this perhaps.

Dim NoRows As long

For Row = 1 To rows
For col = 1 To cols
If Cells(Row, col).Value = "Fridge" Then
     RowNo = RowNo+1
     listarray(RowNo, col) = Cells(Row, col).Value
     
End If


Next col
Next Row

 

[duncanmcl]

thanks....still no values in list box....using your code....if fridge is in col2....should I specify in your if cell stmt....i named col2 payeen?

 

[Norie]

Sorry I don't quite understand.

Perhaps you could post some sample data, indicating which rows should go in the listbox.

 

[duncanmcl]

below is current code...the ist bos is named bcdetail, the matching value is in col2....it runs w/no data displayed when the me.bcdetail.list line is commented out...otherwise an error=could not set the list property...appreciate your help!!
Private Sub UserForm_Initialize()
Dim cLoc2 As Range
Dim zZ As Long
Dim PayeeN As String
PayeeN = "000000001039"

FindLastRow2
Set Ws2 = Worksheets("q_report_detail")
Dim I As Long
I = 2
'With Me.BCDetail
Zzz = 2
With Ws2
With Me.BCDetail
Dim NoRows As Long
Const cols = 5
Const rows = 10
Dim listarray() As Variant
For Row = 1 To rows
For col = 1 To cols
If Cells(Row, 2).Value = "000000001039" Then
RowNo = RowNo + 1
listarray(RowNo, col) = Cells(Row, col).Value
MsgBox "matched" & Cells(Row, col)
End If
Next col
Next Row
End With
End With
Me.BCDetail.List = listarray
End Sub

 

[duncanmcl]

valid matching sample data for list box

 

[duncanmcl]

34	000000001039		505005361001	GLENCOE MANAGEMENT	8/1/2001	A	E	7	7
34	000000001039		505005361002	GLENCOE MGMT/HIGHLAND ENTERPRI	8/1/2001	A	E	17	17

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 48pt" span=6 width=64><TBODY>
</TBODY>

 

[Rick Rothstein]

below is current code...the ist bos is named bcdetail, the matching value is in col2....it runs w/no data displayed when the me.bcdetail.list line is commented out...otherwise an error=could not set the list property...appreciate your help!!

I am also having trouble figuring out what you want. From your code, it looks like the value you want to find is always in Column B. Assuming that is correct, and assuming the word you want to find is "Fridge", show us what the ListBox contents should look like if this is your data (I highlighted the word Fridge in red in Column B so it would be easier for you to spot)...

	A	B	C	D	E
1	DataA1	DateB1	DataC1	DataD1	DataE1
2	DataA2	Fridge	DataC2	DataD2	DataE2
3	DataA3	DateB3	DataC3	DataD3	DataE3
4	DataA4	DateB4	DataC4	DataD4	DataE4
5	DataA5	Fridge	DataC5	DataD5	DataE5
6	DataA6	Fridge	DataC6	DataD6	DataE6
7	DataA7	DateB7	DataC7	DataD7	DataE7
8	DataA8	Fridge	DataC8	DataD8	DataE8
9	DataA9	DateB9	DataC9	DataD9	DataE9

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>