Private Sub CommandButton_Search_Click()
ActiveWorkbook.Sheets("Issued Items").Activate
Application.ScreenUpdating = False
Dim FindString1 As String
Dim FindString2 As String
Dim FindString3 As String
Dim FindString4 As String
Rng1 = ""
Rng2 = ""
Rng3 = ""
Rng4 = ""
FindString1 = TextBox_UTC.Value
FindString2 = TextBox_Desc.Value
FindString3 = TextBox_Name.Value
FindString4 = TextBox_Location.Value
If TextBox_UTC.Value = "" And TextBox_Desc.Value = "" And TextBox_Name.Value = "" And TextBox_Location.Value = "" Then
MsgBox "Enter The ""UTC/Serial"" Or ""Description"" Or ""Name"" Or ""Location"" Information!", vbInformation + vbOKOnly, "Error"
TextBox_UTC.SetFocus
ElseIf TextBox_Desc.Value = "" And TextBox_Name.Value = "" And TextBox_Location.Value = "" Then
With Sheets("Issued Items").Range("C4:C500")
Set c = .Find(what:=FindString1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng1 = Rng1 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
Else
MsgBox "The UTC/Serial You Have Entered Is Not Issued!" & vbCrLf & vbCrLf & " Please Enter A Valid UTC/Serial.", vbInformation + vbOKOnly, "Invalid Entry"
TextBox_UTC.Value = ""
TextBox_Desc.Value = ""
TextBox_Name.Value = ""
TextBox_Location.Value = ""
TextBox_UTC.SetFocus
End If
End With
If Rng1 <> "" Then
Rng1 = Left(Rng1, Len(Rng1) - 1)
Range(Rng1).Select
Range("A1").Select
Union(Range("A1"), Range(Rng1)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_UTC.Value = "" And TextBox_Name.Value = "" And TextBox_Location.Value = "" Then
With Sheets("Issued Items").Range("D4:D500")
Set c = .Find(what:=FindString2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng2 = Rng2 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng2 <> "" Then
Rng2 = Left(Rng2, Len(Rng2) - 1)
Range(Rng2).Select
Range("A1").Select
Union(Range("A1"), Range(Rng2)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_UTC.Value = "" And TextBox_Desc.Value = "" And TextBox_Location.Value = "" Then
With Sheets("Issued Items").Range("F4:F500")
Set c = .Find(what:=FindString3, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng3 = Rng3 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng3 <> "" Then
Rng3 = Left(Rng3, Len(Rng3) - 1)
Range(Rng3).Select
Range("A1").Select
Union(Range("A1"), Range(Rng3)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_UTC.Value = "" And TextBox_Desc.Value = "" And TextBox_Name.Value = "" Then
With Sheets("Issued Items").Range("G4:G500")
Set c = .Find(what:=FindString4, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng4 = Rng4 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng4 <> "" Then
Rng4 = Left(Rng4, Len(Rng4) - 1)
Range(Rng4).Select
Range("A1").Select
Union(Range("A1"), Range(Rng4)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_Name.Value = "" And TextBox_Location.Value = "" Then
With Sheets("Issued Items").Range("C4:C500")
Set c = .Find(what:=FindString1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng1 = Rng1 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng1 <> "" Then
Rng1 = Left(Rng1, Len(Rng1) - 1)
Range(Rng1).Select
End If
With Sheets("Issued Items").Range("D4:D500")
Set c = .Find(what:=FindString2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng2 = Rng2 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng2 <> "" Then
Rng2 = Left(Rng2, Len(Rng2) - 1)
Range("A1").Select
Union(Range("A1"), Range(Rng2), Range(Rng1)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_Desc.Value = "" And TextBox_Location.Value = "" Then
With Sheets("Issued Items").Range("C4:C500")
Set c = .Find(what:=FindString1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng1 = Rng1 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng1 <> "" Then
Rng1 = Left(Rng1, Len(Rng1) - 1)
Range(Rng1).Select
End If
With Sheets("Issued Items").Range("F4:F500")
Set c = .Find(what:=FindString3, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng3 = Rng3 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng3 <> "" Then
Rng3 = Left(Rng3, Len(Rng3) - 1)
Range(Rng3).Select
Range("A1").Select
Union(Range("A1"), Range(Rng3), Range(Rng1)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_Desc.Value = "" And TextBox_Name.Value = "" Then
With Sheets("Issued Items").Range("C4:C500")
Set c = .Find(what:=FindString1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng1 = Rng1 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng1 <> "" Then
Rng1 = Left(Rng1, Len(Rng1) - 1)
Range(Rng1).Select
End If
With Sheets("Issued Items").Range("G4:G500")
Set c = .Find(what:=FindString4, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng4 = Rng4 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng4 <> "" Then
Rng4 = Left(Rng4, Len(Rng4) - 1)
Range(Rng4).Select
Range("A1").Select
Union(Range("A1"), Range(Rng4), Range(Rng1)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_UTC.Value = "" And TextBox_Location.Value = "" Then
With Sheets("Issued Items").Range("D4:D500")
Set c = .Find(what:=FindString2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng2 = Rng2 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng2 <> "" Then
Rng2 = Left(Rng2, Len(Rng2) - 1)
Range(Rng2).Select
End If
With Sheets("Issued Items").Range("F4:F500")
Set c = .Find(what:=FindString3, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng3 = Rng3 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng3 <> "" Then
Rng3 = Left(Rng3, Len(Rng3) - 1)
Range(Rng3).Select
Range("A1").Select
Union(Range("A1"), Range(Rng3), Range(Rng2)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_UTC.Value = "" And TextBox_Name.Value = "" Then
With Sheets("Issued Items").Range("D4:D500")
Set c = .Find(what:=FindString2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng2 = Rng2 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng2 <> "" Then
Rng2 = Left(Rng2, Len(Rng2) - 1)
Range(Rng2).Select
End If
With Sheets("Issued Items").Range("G4:G500")
Set c = .Find(what:=FindString4, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng4 = Rng4 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng4 <> "" Then
Rng4 = Left(Rng4, Len(Rng4) - 1)
Range(Rng4).Select
Range("A1").Select
Union(Range("A1"), Range(Rng4), Range(Rng2)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_UTC.Value = "" And TextBox_Desc.Value = "" Then
With Sheets("Issued Items").Range("F4:F500")
Set c = .Find(what:=FindString3, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng3 = Rng3 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng3 <> "" Then
Rng3 = Left(Rng3, Len(Rng3) - 1)
Range(Rng3).Select
End If
With Sheets("Issued Items").Range("G4:G500")
Set c = .Find(what:=FindString4, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng4 = Rng4 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng4 <> "" Then
Rng4 = Left(Rng4, Len(Rng4) - 1)
Range(Rng4).Select
Range("A1").Select
Union(Range("A1"), Range(Rng4), Range(Rng3)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_Location.Value = "" Then
With Sheets("Issued Items").Range("C4:C500")
Set c = .Find(what:=FindString1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng1 = Rng1 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng1 <> "" Then
Rng1 = Left(Rng1, Len(Rng1) - 1)
Range(Rng1).Select
End If
With Sheets("Issued Items").Range("D4:D500")
Set c = .Find(what:=FindString2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng2 = Rng2 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng2 <> "" Then
Rng2 = Left(Rng2, Len(Rng2) - 1)
Range(Rng2).Select
Union(Range(Rng2), Range(Rng1)).Select
End If
With Sheets("Issued Items").Range("F4:F500")
Set c = .Find(what:=FindString3, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng3 = Rng3 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng3 <> "" Then
Rng3 = Left(Rng3, Len(Rng3) - 1)
Range(Rng3).Select
Range("A1").Select
Union(Range("A1"), Range(Rng3), Range(Rng2), Range(Rng1)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_Name.Value = "" Then
With Sheets("Issued Items").Range("C4:C500")
Set c = .Find(what:=FindString1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng1 = Rng1 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng1 <> "" Then
Rng1 = Left(Rng1, Len(Rng1) - 1)
Range(Rng1).Select
End If
With Sheets("Issued Items").Range("D4:D500")
Set c = .Find(what:=FindString2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng2 = Rng2 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng2 <> "" Then
Rng2 = Left(Rng2, Len(Rng2) - 1)
Range(Rng2).Select
Union(Range(Rng2), Range(Rng1)).Select
End If
With Sheets("Issued Items").Range("G4:G500")
Set c = .Find(what:=FindString4, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng4 = Rng4 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng4 <> "" Then
Rng4 = Left(Rng4, Len(Rng4) - 1)
Range(Rng4).Select
Range("A1").Select
Union(Range("A1"), Range(Rng4), Range(Rng2), Range(Rng1)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_Desc.Value = "" Then
With Sheets("Issued Items").Range("C4:C500")
Set c = .Find(what:=FindString1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng1 = Rng1 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng1 <> "" Then
Rng1 = Left(Rng1, Len(Rng1) - 1)
Range(Rng1).Select
End If
With Sheets("Issued Items").Range("F4:F500")
Set c = .Find(what:=FindString3, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng3 = Rng3 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng3 <> "" Then
Rng3 = Left(Rng3, Len(Rng3) - 1)
Range(Rng3).Select
Union(Range(Rng3), Range(Rng1)).Select
End If
With Sheets("Issued Items").Range("G4:G500")
Set c = .Find(what:=FindString4, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng4 = Rng4 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng4 <> "" Then
Rng4 = Left(Rng4, Len(Rng4) - 1)
Range(Rng4).Select
Range("A1").Select
Union(Range("A1"), Range(Rng4), Range(Rng3), Range(Rng1)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
ElseIf TextBox_UTC.Value = "" Then
With Sheets("Issued Items").Range("D4:D500")
Set c = .Find(what:=FindString2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng2 = Rng2 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng2 <> "" Then
Rng2 = Left(Rng2, Len(Rng2) - 1)
Range(Rng2).Select
End If
With Sheets("Issued Items").Range("F4:F500")
Set c = .Find(what:=FindString3, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng3 = Rng3 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng3 <> "" Then
Rng3 = Left(Rng3, Len(Rng3) - 1)
Range(Rng3).Select
Union(Range(Rng3), Range(Rng2)).Select
End If
With Sheets("Issued Items").Range("G4:G500")
Set c = .Find(what:=FindString4, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng4 = Rng4 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng4 <> "" Then
Rng4 = Left(Rng4, Len(Rng4) - 1)
Range(Rng4).Select
Range("A1").Select
Union(Range("A1"), Range(Rng4), Range(Rng3), Range(Rng2)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
Else
With Sheets("Issued Items").Range("C4:C500")
Set c = .Find(what:=FindString1, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Application.Goto c, True
ActiveWindow.ScrollColumn = 1
Do
Rng1 = Rng1 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng1 <> "" Then
Rng1 = Left(Rng1, Len(Rng1) - 1)
Range(Rng1).Select
End If
With Sheets("Issued Items").Range("D4:D500")
Set c = .Find(what:=FindString2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng2 = Rng2 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng2 <> "" Then
Rng2 = Left(Rng2, Len(Rng2) - 1)
Range(Rng2).Select
Union(Range(Rng2), Range(Rng1)).Select
End If
With Sheets("Issued Items").Range("F4:F500")
Set c = .Find(what:=FindString3, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng3 = Rng3 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng3 <> "" Then
Rng3 = Left(Rng3, Len(Rng3) - 1)
Range(Rng3).Select
Union(Range(Rng3), Range(Rng2), Range(Rng1)).Select
End If
With Sheets("Issued Items").Range("G4:G500")
Set c = .Find(what:=FindString4, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveWindow.ScrollColumn = 1
Do
Rng4 = Rng4 & c.Address & ","
Set c = .FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> firstAddress)
End If
End With
If Rng4 <> "" Then
Rng4 = Left(Rng4, Len(Rng4) - 1)
Range(Rng4).Select
Range("A1").Select
Union(Range("A1"), Range(Rng4), Range(Rng3), Range(Rng2), Range(Rng1)).Select
End If
Unload UserForm10
Application.ScreenUpdating = True
End If
End Sub
