Log In
Register
Home
October 25, 2017
Offline
Hi,
I have this code for searching but not working when I type in SAGEID textbox for example 100261 get message data not found but I know the number is there is there something wrong with code: (attaching sample file)
Private Sub CommandButton1_Click()
Dim wsDistCode As Worksheet
Dim m As Variant, Search As Variant
Dim i As Long, C As Long
Set wsDistCode = ThisWorkbook.Worksheets("DIST_CODE")
For C = 1 To 8
Search = Me.Controls("TextBox" & C)
If Len(Search) > 0 Then
If IsNumeric(Search) Then Search = Val(Search)
m = Application.Match(Search, wsDistCode.Columns(C), 0)
For i = 1 To 8
Me.Controls("TextBox" & i).Value = IIf(Not IsError(m), wsDistCode.Cells(CLng(m), i).Text, "")
Next i
Exit For
End If
Next
If IsError(m) Then MsgBox Search & Chr(10) & " DATA Not FOUND", 48, "Not Found"
End Sub
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
There's no file attached but, in the meantime, if your codes are stored as text in the worksheet, then that code won't find them because it converts all numeric strings to actual numbers. Try commenting out this line:
If IsNumeric(Search) Then Search = Val(Search)
If that fixes it, then that was the problem.
October 25, 2017
Offline
Hi,
Yes, SAGEID is like vendor number but sometimes works for example if type first time doesn't but If I go to spreadsheet and retype the number and try agian works, the the wildcard search for vendor name works is just SAGEID doesn't work all the time.
I commented out that line seems to work, just attaching the file now if you can please check if that really the problem, much appreciated.
Thank you,
Trusted Members
Moderators
November 1, 2018
Offline
Yes, that is the issue. I assume you have done exactly that in the sample workbook because now you have a mix of actual numbers and numbers stored as text, which could cause you issues. You also appear to have the same ID repeated more than once, and your search code will only ever find the first entry.
As a general comment, you have a fair amount of repeated code which could really do with refactoring into separate routines - it will make maintenance easier.
Answers Post
1 Guest(s)
