VBA search function

[kigwakatol15]

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

[Bob Phillips]

Not a lot of data I that workbook to see what happens.

[kigwakatol15]

i attached the file with datas already sir

[kigwakatol15]

my problem is that when ever I to search for the values of Column D it wont show up but when i tried other column it works

[p45cal]

arr = .Range("A2", "I2" & lr)

probably needs to be:

arr = .Range("A2:I" & lr)

Also

        arr(x, 8) = arr(x, 1) & arr(x, 2) & arr(x, 3) & arr(x, 5) & arr(x, 6) & arr(x, 7)

probably needs to be:

        arr(x, 8) = arr(x, 1) & arr(x, 2) & arr(x, 3) & arr(x, 4) & arr(x, 5) & arr(x, 6) & arr(x, 7)

Enhancement:
Currently, if you look for ragu it'll come up with ID 5. It shouldn't.
So try:

arr(x, 8) = arr(x, 1) & "|" & arr(x, 2) & "|" & arr(x, 3) & "|" & arr(x, 4) & "|" & arr(x, 5) & "|" & arr(x, 6) & "|" & arr(x, 7)

instead.