Predict Date of Enlistment

[HTSCF Fareha]

Still plodding my way through.

The enlistment date enquiry function needs to enable the user to select Regiment and Battalion and a Soldier Number to enquire about.

If the Soldier number exists, then the result of the Soldier Number and Enlistment Date need to be displayed in txtEstEnlistmentDateResult with a short message indicating that this is the case.

If the Soldier Number doesn't exist, then the txtEstEnlistmentDateResult box should show five enlistment numbers and their respective enlistment dates either side of the entered Soldier Number. Again, ideally with a message to explain that an exact Soldier Number / Enlistment Date combination was not found.

Private Sub cmbEnquiry_Click()

    Dim LastRow As Integer, LastCol As Integer, Cnt As Integer, Cnt2 As Integer
    Dim Sht As Worksheet, SortRange As Range, Solnumberdate As Variant

    ' Enlistment date query
    ' If number already exists, then show it and the date, plus 5 either side in txtEstEnlistmentDateResult
    ' If number doesn't exist, then show 5 nearest numbers and their date either side in txtEstEnlistmentDateResult

    If frmEnlistment.lboRegiment.ListIndex = -1 Then
        MsgBox "Select Regiment!", vbExclamation + vbOKOnly, "Soldier Enlistment"
        Exit Sub
    End If
    If frmEnlistment.lboBattalion.ListIndex = -1 Then
        MsgBox "Select Battalion!", vbExclamation + vbOKOnly, "Soldier Enlistment"
        Exit Sub
    End If
    If Not IsNumeric(frmEnlistment.txtSoldierNumberEnq.Value) Or _
       frmEnlistment.txtSoldierNumberEnq.Text = vbNullString Then
        MsgBox "Input Soldier Number to Query!", vbExclamation + vbOKOnly, "Soldier Enlistment"
        Exit Sub
    End If
    
    For Each Sht In ThisWorkbook.Sheets
        If frmEnlistment.lboRegiment.List(frmEnlistment.lboRegiment.ListIndex) = Sht.Name Then
            With Sheets(Sht.Name)
                LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                For Cnt = 1 To LastCol
                    If Sheets(Sht.Name).Cells(1, Cnt) = frmEnlistment.lboBattalion.List(frmEnlistment.lboBattalion.ListIndex) Then
                        LastRow = .Cells(.Rows.Count, Cnt).End(xlUp).Row
                        For Cnt2 = 3 To LastRow
                            If CInt(Sheets(Sht.Name).Cells(Cnt2, Cnt)) = CInt(frmEnlistment.txtSoldierNumberEnq.Value) Then
                                frmEnlistment.txtEnlistmentDate.Text = vbNullString
                                ' Display existing Soldier Number and Enlistment Date in txtEstEnlistmentDateResult
                            
                                frmEnlistment.txtSoldierNumberEnq.txtEstEnlistmentDateResult
                                
                                
                                Exit Sub
                            End If
                        Next Cnt2
                        
                        Exit For
                    End If
                Next Cnt
                Exit For
            End With
        End If
    Next Sht
    
    frmEnlistment.txtSoldierNumberEnq.Text = vbNullString
    frmEnlistment.txtEstEnlistmentDateResult.Text = vbNullString
    frmEnlistment.lboRegiment.ListIndex = -1
    frmEnlistment.lboBattalion.ListIndex = -1
End Sub

I was wondering if the Offset or Resize method would help with selecting a range of five Soldier Number / Enlistment Dates?

Really struggling to sort this so help would be very much appreciated! I keep looking and trying things without any sucess. Leave it for a while and research / enter a few more known Soldier Numbers / Enlistment Dates, then go back to it again.