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]

I'm loosing my mind over this it been almost 3 hours trying different data manipulation and nothin still the same problem.

 

[RockEd]

Are you exiting the sub too soon, i've removed that below?

Try this instead - couple of other changes msgbox if it doesn't find a date, and hopefully a better way of calculating datevalue (fingers crossed not sure whether this will work i haven't tested it).

'find date'
    dateValue = DateValue(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)
        If Not emptyCell Is Nothing Then
            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
            Exit For
        Else
            Set dRng = dRng.Offset(60, 0)
        End If
    Next a
    If emptyCell Is Nothing Then
        MsgBox "Date not found on this sheet."
        Exit Sub
    End If

 

[mysticmario]

DateValue declared as Date?

 

[mysticmario]

Are you exiting the sub too soon, i've removed that below?

Try this instead - couple of other changes msgbox if it doesn't find a date, and hopefully a better way of calculating datevalue (fingers crossed not sure whether this will work i haven't tested it).

'find date'
    dateValue = DateValue(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)
        If Not emptyCell Is Nothing Then
            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
            Exit For
        Else
            Set dRng = dRng.Offset(60, 0)
        End If
    Next a
    If emptyCell Is Nothing Then
        MsgBox "Date not found on this sheet."
        Exit Sub
    End If

currently i have dateValue declared as Variant, i get type missmatch, what should id eclare it as, I've never seen that way of coding [QUOTE] dateValue = DateValue(Me.DateRange.Value) [/QUOTE]

 

[dmt32]

Hi,
Just be aware that DateValue is a VBA function name and using it as a variable in your procedure may be causing a conflict.
Try renaming the variable to something like FindDate & see if this will resolve your issue.

Dave

 

[RockEd]

Sorry try just: dateValue = Me.DateRange.Value
edit as dmt32 said that's probably where i was getting confused i thought datevalue was a function name but wasn't really thinking!

 

[mysticmario]

I relaized i tried to call a function that does not exist

 

[mysticmario]

Date not found.

 

[mysticmario]

Hi,
Just be aware that DateValue is a VBA function name and using it as a variable in your procedure may be causing a conflict.
Try renaming the variable to something like FindDate & see if this will resolve your issue.

Dave

I changed the name and it started working, but few minutes later stopped...