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]

thanks again
row2 cols b d and e, row 5 cols b d and e, row 6 cols b d and e, and row8 cols b d and e

 

[Rick Rothstein]

thanks again
row2 cols b d and e, row 5 cols b d and e, row 6 cols b d and e, and row8 cols b d and e

You want Column B? In the ListBox, each text string in that column will contain the same word ("Fridge" for the example we have been discussing), so that column in the ListBox will just have the same word ("Fridge") repeated over and over. Anyway, give this code a try...

  Dim X As Long, Cell As Range, Rng As Range, WS As Worksheet, Arr As Variant, RW As String
  
  Const SearchWord As String = "Fridge"
  
  Set WS = Worksheets("Sheet5")
  WS.Columns("B").Replace SearchWord, "=" & SearchWord, xlWhole
  Set Rng = WS.Columns("B").SpecialCells(xlFormulas)
  For Each Cell In Rng
    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.ColumnCount = 3
  BCDetail.List() = Arr

 

[duncanmcl]

Rick: with your code pointing to my data...I get error 9, subscript out of range.....I can't trap the error line....any thoughts? I can't find any reference to xlformulas...is it valid? The data in "B" is strictly text

 

[Rick Rothstein]

Rick: with your code pointing to my data...I get error 9, subscript out of range.....I can't trap the error line....any thoughts? I can't find any reference to xlformulas...is it valid? The data in "B" is strictly text

Yes, it is valid. The actual constant's name is xlCellTypeFormulas (all of SpecialCells' argument constants start with xlCellType), but VB has mirrored it (and all of the other SpecialCell constants) with the same name minus the CellType part... using those alternates are easier to remember and type. As for why you might be getting an error... did you change the "Sheet5" reference in this line of code...

Set WS = Worksheets("Sheet5")

to your actual sheet name? Another possibly is that the keyword you are searching for does not exist, all by itself, in any cells within Column B (or whatever column you pointed my code to)... could you possibly have it in cells with other words or, perhaps, have spaces or other non-printing characters after it? Just so you know, the code worked fine in my tests before I posted it.

 

[duncanmcl]

my actual data...looking for bn0621 in col B, it exists, 34 below is in col A...I did point my worksheets to the name of sheet 2, called "detail", I also tried sheet2
the col B was defined in Access as char 12 and exported to excel, there would be trailing spaces

34

BN0621

HP8823

HEALTH PROFESSIONALS AND ALLIE

7/1/2005

A

<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" width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

 

[Rick Rothstein]

my actual data...looking for bn0621 in col B, it exists, 34 below is in col A...I did point my worksheets to the name of sheet 2, called "detail", I also tried sheet2
the col B was defined in Access as char 12 and exported to excel, there would be trailing spaces

34

BN0621

HP8823

HEALTH PROFESSIONALS AND ALLIE

7/1/2005

A

<TBODY>
</TBODY>

I don't use Access, so I'm not 100% sure what "defined in Access as char 12" means, but it sounds like maybe the exported data is 12 characters long, padded with trailing spaces, when the data in the cell (field?) is less than that length. If so, then you will need to Trim the text before seaching. Here is a variation on my code that you will hopefully be able to modify as needed (in it, I am assuming the search word is located in cell B34)...

  Dim X As Long, Cell As Range, Rng As Range, WS As Worksheet, Arr As Variant, RW As String, SearchWord As String
  Set WS = Worksheets("Sheet5")
  
  SearchWord = Trim(WS.Cells(34, "B"))
  
  WS.Columns("B").Replace SearchWord, "=" & SearchWord, xlWhole
  Set Rng = WS.Columns("B").SpecialCells(xlFormulas)
  For Each Cell In Rng
    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.ColumnCount = 3
  BCDetail.List() = Arr

 

[duncanmcl]

same error w/latest...the row for this valid/matching code in col B, happens to be row 3500, not 34 as you suggested....we are looking in col B all rows for a valid matching value...so the first part of ws(cells( should be ? cells.row? Again..thanks in advance....the code seems very hard for a new Excel VB user

my currend code
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 = "BN0621" '"Fridge"
Set WS = Worksheets("detail") 'sheet5
SearchWord = Trim(WS.Cells(34, "B")) '(34, "B"))

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.ColumnCount = 3
BCDetail.List() = Arr
End Sub

 

[Rick Rothstein]

same error w/latest...the row for this valid/matching code in col B, happens to be row 3500, not 34 as you suggested....we are looking in col B all rows for a valid matching value...so the first part of ws(cells( should be ? cells.row? Again..thanks in advance....the code seems very hard for a new Excel VB user

Any chance you can post your workbook to either your SkyDrive (if you have one) or a free file-sharing website (I find http://www.box.net/files easy to use; just remember to post the URL they give you back here)? It would make it easier for us here to test out our ideas against your real data so we can see what is working and what is not working directly (rather than try and have you anticipate everything we would want to look for).

 

[duncanmcl]

my stupid oversite....sheet 2 was not named detail....thats the form name....sheet 2 is q report detail, with that correction and elim the last trim stmt...it works and produces output to the list box....however it also replaces the col B values w/0....both in the list box and raw data...any thoughts....

 

[Rick Rothstein]

my stupid oversite....sheet 2 was not named detail....thats the form name....sheet 2 is q report detail, with that correction and elim the last trim stmt...it works and produces output to the list box....however it also replaces the col B values w/0....both in the list box and raw data...any thoughts....

With the code I posted, I am not sure I can see how values of 0 will end up anywhere. I think I would still like to see your workbook so I can see directly what is going on.