Amend Search Code

[scwyj]

I have the following code to search for serial numbers.

Private Sub CommandButton1_Click()
Dim Message, Title, Default, SearchString
Message = "Enter Serial Number" ' Set prompt.
Title = "Find Serial Number" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)

'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set f = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If Not f Is Nothing Then
f.Offset(, 3) = Date
Exit For
End If
End With
Next S

End Sub
I would like to amend this so that (a) if the serial number is not found I get a message box saying "Serial number not found" and (b) if the serial number is found, I would like it to highlight the relevant row (after inserting the date).

Many thanks.
Scwyjjy

 

[QuietRiot]

Private Sub CommandButton1_Click()
Dim addrss As String
Dim Message, Title, Default, SearchString
Message = "Enter Serial Number" ' Set prompt.
Title = "Find Serial Number" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)

'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set f = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If Not f Is Nothing Then
f.Offset(, 3) = Date
addrss = f.Address
addrss = Mid(addrss, InStr(2, addrss, "$") + 1, 10)
Rows(addrss).Interior.ColorIndex = 44
Exit For
Else
MsgBox "Serial Number Not Find "
Exit For
End If
End With
Next S
End Sub

 

[scwyj]

Thanks for that...but now if I run the command, it gives me the message box every time, even if the serial number is in the sheet?

 

[QuietRiot]

it does?!!

did you copy my entire code and use it.

or did you edit your original somehow.

i tested it a bunch of times and it seems to work here.

there is an
Else
MsgBox "Serial Number Not Find "
Exit For
End If

shouldn't pop up if its found

and if it is found it highlights

 

[scwyj]

Yes, copied and pasted entire code - tried it again, using only three sample serial numbers - still getting not found msg each time. Hair going gray as we speak!

 

[scwyj]

Second Test - appears to be an extraneous End For in the code - removed that and I still get "not found" message each time, but it does enter the date and highlight the row once I hit ok.

 

[QuietRiot]

Private Sub CommandButton1_Click()
Dim addrss As String
Dim Message, Title, Default, SearchString
Message = "Enter Serial Number" ' Set prompt.
Title = "Find Serial Number" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)

'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set f = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If Not f Is Nothing Then
f.Offset(, 3) = Date
addrss = f.Address
addrss = Mid(addrss, InStr(2, addrss, "$") + 1, 10)
Rows(addrss).Interior.ColorIndex = 44
Else
MsgBox "Serial Number Not Find "
End If
End With
Exit For
Next S
End Sub

try that

 

[QuietRiot]

is this on a userform? and is the search needed across sheets?

or is it a button on a worksheet?

 

[scwyj]

Worksheet button. Not needed across sheets (but found the code for multiple sheets so used it).

Still testing; msg box keeps coming up.

If I take out the other sheets in this workbook, the code works fine and as it should. But if I add sheets to the book, I get the message box for all entries.

Seems like the best way round this is to forget the extra sheets!

Thanks for your help, but isn't it quitting time in Boston?

 

[QuietRiot]

funny, it was quitting time when you said that. Left the office in Boston at 5. Anyway, at home now and the Sox don't start for a few minutes

give this guy a whirl..

Private Sub CommandButton1_Click()

Dim f As Range, rng As Range
Dim addrss As String

Dim Message, Title, SearchString
Message = "Enter Serial Number"
Title = "Find Serial Number"
SearchString = InputBox(Message, Title, Default)

Set rng = ActiveSheet.UsedRange

With rng

Set f = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
    
    If Not f Is Nothing Then
        f.Offset(, 3) = Date
        addrss = f.Address
        addrss = Mid(addrss, InStr(2, addrss, "$") + 1, 10)
        Rows(addrss).Interior.ColorIndex = 44
    Else
        MsgBox "Serial Number Not Find "
    End If

End With
End Sub