danovkos
05-15-2017, 02:02 AM
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
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