VBA code for finding date sometimes doesn't work.

[mysticmario]

Hi, I have this bit of code:

    'find date'
    dateValue = Val(Me.DateRange.Value)
    Set dRng = Range("D7:J7")
    For a = 0 To 14 'range of 15 arrays of dates
        Set emptyCell = dRng.Find(What:=CDate(dateValue), LookIn:=xlValues, LookAt:=xlWhole)
        Exit Sub
            Dim colNum As Integer
        colNum = emptyCell.Column 'get the column number where the date was found
        For b = emptyCell.Row + 1 To emptyCell.Row + 32 'start the loop from the next row of the found date cell
            If Cells(b, colNum).Value = "" Then 'check if the cell is empty in the same column where the date was found
                Set emptyCell = Cells(b, colNum)
                    ' Add jobtype and hours
                    emptyCell.Value = Me.HoursCount.Value
                    Cells(emptyCell.Row, "C").Value = Me.JobType.Value
                    Cells(emptyCell.Row, "B").Value = Me.employee.Value
                    Exit For
                End If
            Next b        
            If Not emptyCell Is Nothing Then Exit For
        Else
            Set dRng = dRng.Offset(60, 0)
        End If
    Next a
    If emptyCell Is Nothing Then
        MsgBox "No empty cell available below " & dRng.Address
        Exit Sub
    End If

I encounter the problem that for some reason this code sometimes stops working and acts as if the DateValue does not exist on the sheet.
So for example I hzve a date 23.02.2023 as DateValue I press the submit button and it finds the date then finds the next emptyCell in the column where the date was found and in the end adds value to this empty cell and 2 other cells in column B and C.
Then all of a sudden I peess submit after few minutes of coding and the same date is not found and goes straight to he else statement sayign there is not value like this on this sheet.
When I switch to that sheet the date is there of course, but I delete the formula that results in this date, in this example it's a D67 cell with formula "=C64" inside, retype it again restart excel and the code works fine again.
I dont knwo if it a result of shared workboook being worked in VBa on the backend that breaks it. But Iw ouldnt wnat this to be a thing at all.

Can anyone help to fix it or make a workaround to not have this issue?

 

[mysticmario]

Get rid of the "exit sub"

If the date only appears once you might be better off using application.match instead of a loop.

never used that, I always relied on loops.
However I did spot the prroblem with exit sub under msgbox, but now I always get the msgbox so I ahev to move it outside the loop.

 

[mysticmario]

Ye it works now, thanks for your help. initial problem has been solved.