Assign values to list box array

duncanmcl

New Member
Joined
Aug 7, 2012
Messages
19
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Then you should just need an If and a little work with the array.

Something like this perhaps.
Code:
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

New Member
Joined
Aug 7, 2012
Messages
19
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Sorry I don't quite understand.

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

duncanmcl

New Member
Joined
Aug 7, 2012
Messages
19
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

New Member
Joined
Aug 7, 2012
Messages
19
34000000001039505005361001GLENCOE MANAGEMENT8/1/2001AE77
34000000001039505005361002GLENCOE MGMT/HIGHLAND ENTERPRI8/1/2001AE1717

<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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,508
Office Version
2010
Platform
Windows
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)...

ABCDE
1DataA1DateB1DataC1DataD1DataE1
2DataA2FridgeDataC2DataD2DataE2
3DataA3DateB3DataC3DataD3DataE3
4DataA4DateB4DataC4DataD4DataE4
5DataA5FridgeDataC5DataD5DataE5
6DataA6FridgeDataC6DataD6DataE6
7DataA7DateB7DataC7DataD7DataE7
8DataA8FridgeDataC8DataD8DataE8
9DataA9DateB9DataC9DataD9DataE9

<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>
 

Forum statistics

Threads
1,082,276
Messages
5,364,195
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top