kigwakatol15
01-03-2020, 03:21 AM
i am having a hard time on identifying the problem because whenever i try to search a value with my search function it skips a column or i would say it wont show up but the rest would load up. would appreciate if someone can help me
Private Sub cmdFind_Click()
Dim lr As Long, x As Long, j As Long, arr As Variant, sn As Variant
With Sheets("DATABASE")
lr = .Range("A" & Rows.Count).End(xlUp).Row
arr = .Range("A2", "I2" & lr)
ReDim sn(1 To UBound(arr), 1 To 7)
For x = 1 To UBound(arr)
arr(x, 8) = arr(x, 1) & arr(x, 2) & arr(x, 3) & arr(x, 5) & arr(x, 6) & arr(x, 7)
Next
j = 0
For x = 1 To UBound(arr)
If InStr(1, LCase(arr(x, 8)), LCase(Me.txtSearchMe)) > 0 Then
j = j + 1
sn(j, 1) = arr(x, 1)
sn(j, 2) = arr(x, 2)
sn(j, 3) = arr(x, 3)
sn(j, 4) = arr(x, 4)
sn(j, 5) = arr(x, 5)
sn(j, 6) = arr(x, 6)
sn(j, 7) = arr(x, 7)
End If
Next
End With
Me.lstData.List = sn
End Sub
Private Sub cmdFind_Click()
Dim lr As Long, x As Long, j As Long, arr As Variant, sn As Variant
With Sheets("DATABASE")
lr = .Range("A" & Rows.Count).End(xlUp).Row
arr = .Range("A2", "I2" & lr)
ReDim sn(1 To UBound(arr), 1 To 7)
For x = 1 To UBound(arr)
arr(x, 8) = arr(x, 1) & arr(x, 2) & arr(x, 3) & arr(x, 5) & arr(x, 6) & arr(x, 7)
Next
j = 0
For x = 1 To UBound(arr)
If InStr(1, LCase(arr(x, 8)), LCase(Me.txtSearchMe)) > 0 Then
j = j + 1
sn(j, 1) = arr(x, 1)
sn(j, 2) = arr(x, 2)
sn(j, 3) = arr(x, 3)
sn(j, 4) = arr(x, 4)
sn(j, 5) = arr(x, 5)
sn(j, 6) = arr(x, 6)
sn(j, 7) = arr(x, 7)
End If
Next
End With
Me.lstData.List = sn
End Sub