Hi everybody.
I have a problem with a population of a listbox using the method Application.index.
I need load a listbox with a series of data in a sheet.
I only need just some data (some rows) of the sheet. This data is filtered by a intelligent search that indicate which row should be load in the listbox.
My problem is:
I dont know how can i do, to manage the variable rows to be implemented with the Method Application.index.
What i could achieve so far is:
i can populate the listbox, but integrating the whole sheet:
when i run the code, i get this:
But in this case the listbox is populated with all the data of the sheet, and i need only load some specifically rows, that i don't know which will be.
In my ignorancy, i need something like this¨:
Arr = Application.Index(Cells, Array(..................), Application.Transpose([row(1:15)]))
where on the dotted line, would go the rows that are determined by the intelligent search.
I would appreciate any help .
the file I'm working on:
I have a problem with a population of a listbox using the method Application.index.
I need load a listbox with a series of data in a sheet.
I only need just some data (some rows) of the sheet. This data is filtered by a intelligent search that indicate which row should be load in the listbox.
My problem is:
I dont know how can i do, to manage the variable rows to be implemented with the Method Application.index.
What i could achieve so far is:
i can populate the listbox, but integrating the whole sheet:
VBA Code:
Private Sub datesearch_Change()
Worksheets("register").Activate
sizerow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Worksheets("register").AutoFilterMode = False
historial.ColumnHeads = False
Dim x, Arr As Variant
Arr = Application.Index(Cells, Evaluate("ROW(1:" & Cells(Rows.Count, "A").End(xlUp).Row & ")"), Application.Transpose([row(1:15)])) 'Generating Array
'Format of column date of the Array
For x = 1 To UBound(Arr)
Arr(x, 1) = Format(Arr(x, 1), "d/m/yyyy")
Next
historial.List = Arr ' Populate Listbox
'Intelligent Search
i = 1
For rowin = 2 To sizerow
dateinput = Worksheets("register").Cells(rowin, 1).Value
If dateinput Like "*" & Me.datesearch.Value & "*" Or rowin = 1 Then
'''''''''''''''''Do something in order to only load the rows that match the search on the Listbox "historial"
End If
Next
End Sub
Private Sub UserForm_Initialize()
'Definition of Listbox "historial"
With Me.historial
.ColumnCount = 15
.ColumnHeads = True
End With
End Subwhen i run the code, i get this:
But in this case the listbox is populated with all the data of the sheet, and i need only load some specifically rows, that i don't know which will be.
In my ignorancy, i need something like this¨:
Arr = Application.Index(Cells, Array(..................), Application.Transpose([row(1:15)]))
where on the dotted line, would go the rows that are determined by the intelligent search.
I would appreciate any help .
the file I'm working on:
