Filtered visible cells to Userform as row source.

[danovkos]

Hi, i try to figured out, how to load my filtered data to userform by rows (as row source). I googled and found some code, which works almost good. My data are numbers, text, blanks cells, dates.
I have 2 issues.
1.)
My data has 12 columns, but when i use my code it stops (error) on row

.List(.ListCount - 1, 10) = oneCell.Offset(0, 10).Value

2.)
When i cut (fix) my code(not for 12 but only for 9 columns) , then it works without error, but it make each row from my data as row and also as column and of course i miss my last 3 columns. I mean, that my data are appeared like this:

e.g. my filtered visible source data are
1,2,3,4,5,6
7,8,9,10,11,12

in user form are shown inccorrect as
1, 2, 3, 4, 5, 6
2, 3, 4, 5, 6
3, 4, 5, 6
4, 5, 6,
5, 6
6
7,8,9,10,11,12
8,9,10,11,12
9,10,11,12
10,11,12
11,12
12

i want it in user form
exactly as it are in source
1,2,3,4,5,6
7,8,9,10,11,12


Run time error 380: Could not set list of property...

Any suggestions pls.?
thx for all help

This is my code:



Private Sub UserForm_Initialize()

Dim cell As Range
Dim MyArrP As Variant, i As Long
Dim CIF As Variant, Rowz
Dim LogExist As Variant
Dim LOGYtab As Range
Dim LR As Variant

CIF = Workbooks("Preh.xlsm").Sheets("view").Range("B1").Value
With Workbooks("Preh.xlsm").Sheets("zrkadlo")
.Unprotect
.Range("$A$3:$FR$100000").AutoFilter Field:=162, Criteria1:=CIF
End With

Dim oneCell As Range

LR = Workbooks("Preh.xlsm").Sheets("zrkadlo").Range("FF65536").End(xlUp).Row


With Workbooks("Preh.xlsm").Sheets("zrkadlo")
.Unprotect


'With ListBox_Prechodne 'FUNKCNE --------------------
' For Each oneCell In Sheets("zrkadlo").Range("ff4:f" & LR).SpecialCells(xlCellTypeVisible)
'' For Each oneCell In Sheets("zrkadlo").Range("ff4:F100000").SpecialCells(xlCellTypeVisible)
' .AddItem CStr(oneCell.Value)
' Next oneCell
'End With

With ListBox_Prechodne
.ColumnCount = 13
For Each oneCell In Sheets("zrkadlo").Range("ff4:fQ" & LR).SpecialCells(xlCellTypeVisible)
' For Each oneCell In Sheets("zrkadlo").Range("ff4:F100000").SpecialCells(xlCellTypeVisible)
.AddItem CStr(oneCell.Value)
.List(.ListCount - 1, 1) = oneCell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = oneCell.Offset(0, 2).Value
.List(.ListCount - 1, 3) = oneCell.Offset(0, 3).Value
.List(.ListCount - 1, 4) = oneCell.Offset(0, 4).Value
.List(.ListCount - 1, 5) = oneCell.Offset(0, 5).Value
.List(.ListCount - 1, 6) = oneCell.Offset(0, 6).Value
.List(.ListCount - 1, 7) = oneCell.Offset(0, 7).Value
.List(.ListCount - 1, 8) = oneCell.Offset(0, 8).Value
.List(.ListCount - 1, 9) = oneCell.Offset(0, 9).Value
.List(.ListCount - 1, 10) = oneCell.Offset(0, 10).Value
.List(.ListCount - 1, 11) = oneCell.Offset(0, 11).Value
.List(.ListCount - 1, 12) = oneCell.Offset(0, 12).Value
Next oneCell
End With

'.ShowAllData
End With
'oneCell = ""

End Sub

[rlv]

My guess, and it is only a guess since you have not provided a workbook to examine or test, is that the range you are looping though



Sheets("zrkadlo").Range("ff4:fQ" & LR).SpecialCells(xlCellTypeVisible)


contains cells that are not in column FF, so your "offset" statement oneCell.Offset(0, n).Value (where n = 1-12) is not starting from where you think it is, which gives you the odd looking result. Does it work if you limit the additem to only cells in Column FF?




With ListBox_Prechodne
.ColumnCount = 13
For Each onecell In Sheets("zrkadlo").Range("ff4:fQ" & LR).SpecialCells(xlCellTypeVisible)
' For Each oneCell In Sheets("zrkadlo").Range("ff4:F100000").SpecialCells(xlCellTypeVisible)


If Split(onecell.Address, "$")(1) = "FF" Then
.AddItem CStr(onecell.Value)
.List(.ListCount - 1, 1) = onecell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = onecell.Offset(0, 2).Value
.List(.ListCount - 1, 3) = onecell.Offset(0, 3).Value
.List(.ListCount - 1, 4) = onecell.Offset(0, 4).Value
.List(.ListCount - 1, 5) = onecell.Offset(0, 5).Value
.List(.ListCount - 1, 6) = onecell.Offset(0, 6).Value
.List(.ListCount - 1, 7) = onecell.Offset(0, 7).Value
.List(.ListCount - 1, 8) = onecell.Offset(0, 8).Value
.List(.ListCount - 1, 9) = onecell.Offset(0, 9).Value
.List(.ListCount - 1, 10) = onecell.Offset(0, 10).Value
.List(.ListCount - 1, 11) = onecell.Offset(0, 11).Value
.List(.ListCount - 1, 12) = onecell.Offset(0, 12).Value
End If
Next onecell
End With

[danovkos]

Nice it worked OK with on error resume next. It shows data as i wish. Mainly :).

Form is ok, my data are in rows as i want and only onec. But It still occurs error on the line

.List(.ListCount - 1, 10) = onecell.Offset(0, 10).Value
So i insert
On error resume next
Now it works ok, but still i miss my columns 10, 11, and 12.
In column, 10 where return errors i have date in this format:
16. 5. 2017 12:24:34
Strange is, that this error (when i try to debug code) show on row 10, 11 and 12. So maybe its not the date, but maybe is problem 2 digits column definition (10,11,12......). Only guess.


Why its problem for my code read this format?
When i select with my mouse cursor on command
.List(.ListCount - 1, 10), it shows
this
"Could not get list property.Invalid argument"

[danovkos]

Now i googled, that i can have only 10 columns when i use AddItem :(
So i will try googled solutionf for my 12 columns :(

[rlv]

AddItem is not the only way to populate a listbox. If instead of using AddItem, you load your data into an array and then assign the array to the .list property you should be able to get around the 10 column limit.

[danovkos]

OK, thx. I will try to find some examples, that i see how to make it this way (array).
thx