This code works fine in the listbox, But when you try two scenarios, the following happens:
1) when replacing a line (myCols = Array(1, 3,4, 5, 7, 10) with TextBox and let TextBox 2 written inside it (1,3,4,5,7,10) it gives me an error message (application defined or object defined error
It is marked on the next line in the code ( a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value) . What is the solution to this challenge?
2) when replacing a line (myCols = Array(1, 3,4, 5, 7, 10) with Textboxes containing separate column numbers are written as follows (myCols = Array( TextBox,3TextBox4, TextBox5,TextBox6 ,TextBox7, TextBox8) It works fine, but if one of the textboxes is empty, it gives me the following error message type mismatch
It is marked on the next line in the code ( a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value) . What is the solution to this challenge?
1) when replacing a line (myCols = Array(1, 3,4, 5, 7, 10) with TextBox and let TextBox 2 written inside it (1,3,4,5,7,10) it gives me an error message (application defined or object defined error
It is marked on the next line in the code ( a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value) . What is the solution to this challenge?
2) when replacing a line (myCols = Array(1, 3,4, 5, 7, 10) with Textboxes containing separate column numbers are written as follows (myCols = Array( TextBox,3TextBox4, TextBox5,TextBox6 ,TextBox7, TextBox8) It works fine, but if one of the textboxes is empty, it gives me the following error message type mismatch
It is marked on the next line in the code ( a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value) . What is the solution to this challenge?
VBA Code:
Dim X, ws As Worksheet, i As Long, j As Long, lastRow As Long, y As Long
With Me.ListBox1
.Clear
.ColumnCount = 6
.ColumnWidths = ""
Set ws = ThisWorkbook.Sheets("data")
X = Application.Match(ComboBox1.Value, ws.Rows(1), 0)
If Not IsError(X) Then
lastRow = ws.Cells(Rows.Count, "e").End(xlUp).Row
Dim a, myCols, ii As Long
ReDim a(1 To 6, 1 To lastRow)
myCols = Array(1, 3, 4, 5, 7, 10)
For i = 1 To lastRow
If TextBox1 <> "" And InStr(ws.Cells(i, X), TextBox1) <> 0 Then
j = j + 1
For ii = 0 To UBound(myCols)
a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value
Next
End If
Next i
'.......................................................
For i = 1 To lastRow
If TextBox1 = "" Then
j = j + 1
For ii = 0 To UBound(myCols)
a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value
Next
End If
Next i
'............................................................
ReDim Preserve a(1 To UBound(a, 1), 1 To j)
.Column = a
End If
End With