Excel VBA: Input Box returning "SN Found" or "SN Not Found"

[Bluesguy07]

Hello Guys and Gals,

I found this macro elsewhere, and modified it slightly to fit my needs. However, I'm unsure how to turn it essentially into an IF statement where it returns "SN Found" if it does find the serial number or "SN Not Found" if it does not find the serial number. I would also like to add something that will actually copy the serial number if found into column C (starting with C2) and if not found into column D (D2). Any assistance would be greatly appreciated.

*Note* - This is a 1 page workbook, so multiple worksheets are not needed for this

Sub SNLookup()Dim x As Long
Dim lr, lookRng As Range
Dim findStr As String
Dim foundCell As Variant




lr = Cells(Rows.Count, "A").End(xlUp).Row
Set lookRng = Range("A1:A" & lr)


findStr = InputBox("Enter Serial Number")


    For x = 1 To lr
        If Range("A" & x).Value = findStr Then
        Set foundCell = Range("A" & x).Offset(0, 1)
        MsgBox foundCell & "SN Found"
        End If
    Next x


End Sub

Just as an aside, I am not even sure that all of the code I referenced is needed for this task.

 

[Fluff]

How about

Sub SNLookup()
   Dim Fnd As Range
   Dim findStr As String

   findStr = InputBox("Enter Serial Number")
   Set Fnd = Range("A:A").find(findStr, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Fnd.Value
   Else
       Range("D" & Rows.Count).End(xlUp).Offset(1).Value = Fnd.Value
   End If
End Sub

 

[Bluesguy07]

It sort of work, and mostly doesn't work.

I need text box stating whether or not the SN was found. It does copy the value if found into column C, but it returns an error "Object variable or With block variable not set" if I enter a SN that does not exist.

I just realized that your code doesn't actually have anything that will actually display a message box after finding or not finding the SN. I'm heading to a meeting and I'll see if I can't figure out how to get that added.

Regardless, if it doesn't find the SN it does not paste the entered SN into column D.

 

[gallen]

Hello, I think this achieves what you want.

Sub SNLookup()


Dim lr
Dim findStr As String
Dim rFind As Range


lr = Cells(Rows.Count, "A").End(xlUp).Row


findStr = InputBox("Enter Serial Number")
    
    Set rFind = Range("A1:A" & lr).Find(findStr)


    If rFind Is Nothing Then
        'serial number not found
        MsgBox "SN Not found"
        Cells(Range("D" & Rows.Count).End(xlUp).Row + 1, "D") = findStr
    Else
        'serial number found
        MsgBox "SN Found"
        Cells(Range("C" & Rows.Count).End(xlUp).Row + 1, "C") = findStr
    End If
    
    'housekeeping
    Set rFind = Nothing


End Sub

 

[Fluff]

Ok, How about

Sub SNLookup()
   Dim Fnd As Range
   Dim findStr As String

   findStr = InputBox("Enter Serial Number")
   Set Fnd = Range("A:A").find(findStr, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      MsgBox "Found"
      Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Fnd.Value
   Else
      MsgBox "Not Found"
      Range("D" & Rows.Count).End(xlUp).Offset(1).Value = findStr
   End If
End Sub

 

[Bluesguy07]

Ok, How about

Sub SNLookup()
   Dim Fnd As Range
   Dim findStr As String

   findStr = InputBox("Enter Serial Number")
   Set Fnd = Range("A:A").find(findStr, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      MsgBox "Found"
      Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Fnd.Value
   Else
      MsgBox "Not Found"
      Range("D" & Rows.Count).End(xlUp).Offset(1).Value = findStr
   End If
End Sub

This works beautifully. Thank you very much.

Gallen,

Since Fluff's worked, I did not give yours a try. I can try it later though just to let you know if it worked if you'd like.



You guys are the true heroes of the world.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Bluesguy07]

So, it appears that a loop is needed, and the person I'm doing this for doesn't need to show whether or not the SN is found or not found since I have it split into multiple columns. However, he needs it on a loop. I created the below, but with this it does not allow for the macro to be cancelled with the "Cancel" button on the input box. Any idea how to fix that?

Sub SNLookup()   Dim Fnd As Range
   Dim findStr As String
   Dim i As Integer
For i = 1 To 200
 
   findStr = InputBox("Enter Serial Number")
   Set Fnd = Range("A:A").Find(findStr, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Fnd.Value
   Else
      Range("D" & Rows.Count).End(xlUp).Offset(1).Value = findStr
   End If
   
Next i
  
End Sub

 

[Fluff]

Try

Sub SNLookup()
Dim Fnd As Range
   Dim findStr As String
   Dim i As Integer
For i = 1 To 200
 
   findStr = InputBox("Enter Serial Number")
   If findStr = "" Then Exit Sub
   Set Fnd = Range("A:A").find(findStr, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Fnd.Value
   Else
      Range("D" & Rows.Count).End(xlUp).Offset(1).Value = findStr
   End If
   
Next i
  
End Sub

 

[Bluesguy07]

Perfect. You are awesome. Thanks for the quick (and working) response.