PDA

View Full Version : VBA search function



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

Bob Phillips
01-03-2020, 04:18 AM
Not a lot of data I that workbook to see what happens.

kigwakatol15
01-03-2020, 04:31 AM
i attached the file with datas already sir

kigwakatol15
01-03-2020, 06:23 AM
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
01-03-2020, 07:21 AM
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.