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

 

[duncanmcl]

my employer is very sensitive to data sharing....the cells in my data now show =BN0621, the searched value, the data sheed in each of the matching rows col B display 0, a 2nd search gets a hit...and again the searched col B output is 0 vs bn0621

 

[Rick Rothstein]

my employer is very sensitive to data sharing....the cells in my data now show =BN0621, the searched value, the data sheed in each of the matching rows col B display 0, a 2nd search gets a hit...and again the searched col B output is 0 vs bn0621

Would it be possible to remove the sensitive data and leave just the columns involvled in the search and output? Or do these columns contain your sensitive data? If they do, what about replacing the data with nonsense text (assuming you can do that easily enough)? I don't need to see your full worksheet... just maybe 10 rows of data that contain, say, 5 rows of matching values... I just need to see something "real" that is not working correctly so I can try to trace down the problem. Another possibility... I am a Microsoft MVP and Microsoft trusts me with their sensitive data (of course, I signed a legally binding Non-Disclosure Agreement, NDA, with them to gain that trust), so if you were willing to trust me not to do anything untoward with your data, you can send your workbook directly to me at rickDOTnewATverizonDOTnet (replace the upper case letters with the symbols they spell out). If you decide you are willing to do that, include the name of this thread in your email so that I can reference it back here upon receipt.

 

[duncanmcl]

aside from the data issue..which I could easily cut and paste to a new file.....its the exporting of data from my PC...rules....and red tape to get permissions....you have seen the core row data....other rows would have different values for each col's....some row's would match the col B value but have different values for other col's...., col A is a market value numeric (2 char)and the same for each record in file, col B is a reference # for a payee code (6 to 12 char), C is a name, D is a cust # (12 char), F a date, G a status code value (a,c) ..thats it...the problem is the searched matching col B on the list box is 0, the worksheet cell has the value =BN0621 and displayes 0 in col B on the worksheet.

 

[Rick Rothstein]

..thats it...the problem is the searched matching col B on the list box is 0, the worksheet cell has the value =BN0621 and displayes 0 in col B on the worksheet.

Oh, I think I see what may be the problem... does the value in the cell start with its own equal sign? In other words, =BN0621, with that leading equal sign, is what is displayed in the cell itself, correct? And that is also the value you see in the formula bar as well, correct?

 

[duncanmcl]

no, the original file cell has no = sign, after running the cell displays a 0, the fx bar shows =bno621, the searched value....plus I added a row with your fridge value in the searchword field in your code....on rerun I still get the old output from bn0621, I created a listbox.clear and it cleared the listbox but on rerun still values for bn0621....again...can't tell how MUCH I appreciate your assistance!

 

[Rick Rothstein]

no, the original file cell has no = sign, after running the cell displays a 0, the fx bar shows =bno621, the searched value....plus I added a row with your fridge value in the searchword field in your code....on rerun I still get the old output from bn0621, I created a listbox.clear and it cleared the listbox but on rerun still values for bn0621....again...can't tell how MUCH I appreciate your assistance!

Show me the exact code you last used that produced the above problem.

 

[duncanmcl]

Private Sub UserForm_Initialize()
Dim X As Long, Cell As Range, Rng As Range, WS As Worksheet, Arr As Variant, RW As String ', searchword As String

Const searchword As String = "fridge" '"BN0621" '"Fridge"

Set WS = Worksheets("q_report_detail") 'sheet5
'searchword = Trim(WS.Cells(34, "B")) '(34, "B"))
MsgBox searchword

WS.Columns("B").Replace searchword, "=" & searchword, xlWhole
Set Rng = WS.Columns("B").SpecialCells(xlFormulas)
For Each Cell In Rng
'MsgBox RW
RW = RW & " " & Cell.Row
Next
RW = Trim(RW)
WS.Columns("B").Replace "=" & searchword, searchword, xlWhole
Arr = Application.Index(WS.Cells, Application.Transpose(Split(RW)), Split("2 4 5"))
BCDetail.ColumnWidths = ".5in;.5in;1.5in"
BCDetail.ColumnHeads = True
BCDetail.ColumnCount = 3
BCDetail.List() = Arr
End Sub