I want to enhance search Result of Excel Sheet.
Sea Attachment with Instructions
Here is the Source Code
Code:
Private Sub Close_cmd_Click()
Unload Me
End Sub
Private Sub Save_cmd_Click()
Dim ws2 As Worksheet
Dim lastRow As Long
Dim x As Long
Set ws2 = Sheets(3)
With ws2
lastRow = .Range("E:K").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("E" & lastRow + 1).Value = Me.Text_AN.Value 'Applicant_Name'
.Range("F" & lastRow + 1).Value = Me.Text_FN.Value 'Father_Name'
.Range("G" & lastRow + 1).Value = Me.Text_CNIC.Value 'CNIC_No'
.Range("H" & lastRow + 1).Value = Me.Combo_RE.Value 'Address'
.Range("I" & lastRow + 1).Value = Me.Text_PN.Value 'Phone No'
.Range("J" & lastRow + 1).Value = Me.Combo_VP.Value 'Visit Purpose'
.Range("K" & lastRow + 1).Value = Me.Combo_RE.Value 'Rev_Estate'
lastRow = lastRow + 1
'For x = 0 To Me.lb_Region.ListCount - 1
' If Me.lb_Region.Selected(x) = True Then
' If .Range("E" & lastRow).Value = "" Then
' .Range("E" & lastRow).Value = Me.lb_Region.List(x)
' Else
' .Range("E" & lastRow).Value = .Range("E" & lastRow).Value & ", " & Me.lb_Region.List(x)
' End If
'End If
'Next x
End With
'Unload Me
End Sub
Private Sub Search_Cmd_Click()
Dim rng As Range
Dim strStates As String
Dim x As Long
If Me.Text_CNIC.Value = "" Then
MsgBox "Please enter a Name/ID to find!", vbExclamation
Me.Text_CNIC.SetFocus
Exit Sub
End If
With Sheets(4)
Set rng = .Range("G:G")
Set rng = rng.Find(What:=Me.Text_CNIC, After:=.Range("G1"), LookAt:=xlWhole)
If rng Is Nothing Then
MsgBox "Name/ID not found!", vbExclamation
Me.Text_CNIC.SetFocus
Exit Sub
End If
Me.Text_AN.Value = rng.Offset(0, 1).Value 'Applicant Name'
Me.Text_FN.Value = rng.Offset(0, 2).Value 'Father Name'
Me.Text_CNIC.Value = rng.Offset(0, 3).Value 'CNIC'
Me.Combo_RE.Value = rng.Offset(0, 4).Value 'Revenue Estate'
Me.Text_PN.Value = rng.Offset(0, 6).Value 'Phone No'
Me.Combo_VP.Value = rng.Offset(0, 7).Value 'Visit Purpose'
'strStates = rng.Offset(0, 1).Value
'For x = 0 To Me.lb_Region.ListCount - 1
' Me.lb_Region.Selected(x) = (InStr(strStates, Me.lb_Region.List(x)) > 0)
'Next x
End With
End Sub
Bookmarks