Finding value of textbox in specified range or ranges

[mysticmario]

Hi, I have an issue with this code that i cannot resolve.
Although the code is big I am mainly concerned about this line:
Set dRng = Sheets(sheetName).Range("D7:J7").Find(What:=Me.DateRange.Value, LookIn:=xlValues)
In this case range D7:J7 corresponds to dates incremented by one so e.g. D7 = 23.02.2023, D8 = 24.02.2023 and so on
let's say Me.DateRange.Value = 23.02.2023
This line of code finds the value in this range without a problem and executes the rest of the code "almost" perfectly, but thats another topic.
As you can tell this is a weekly set of dates and ends on Sunday. When the week ends I create a new "page" with new set of data moving the previous set of excel lines with previosu week range of dates below
so now Me.DateRange.Value = 23.02.2023 can now eb found in D67, and week afetr that it will be D127 so on and so forth.
I cannot set up the .Find to fidn this date on sheet no matter what.
It's worth noting that I ahve some merged cells on that sheet, so when I use D7:J it gets lost.
Tried many approaches, but I just can't seem to find this value on sheet no matter what.
here's the code:
also, here's the screen shot of the sheet where "older" values have been already moved below.

Private Sub Submit_Click()
Dim emp As String
Dim cell As Range
Dim i As Integer
'check if Holiday or Sick is checked'
If Me.Holiday.Value = True And Me.Sick.Value = True Then
    MsgBox "Please select only one checkbox", vbCritical, "Error"
    Exit Sub
End If

'check if Holiday is checked'
If Me.Holiday.Value = True Then
    If Len(Me.DateRange.Value) = 0 Then
        MsgBox "Please enter a Date Range", vbCritical, "Error"
        Exit Sub
    End If
    emp = Me.employee.Value
    Set cell = Sheets("MKP_" & emp).Range("A10:A40").Find(Me.DateRange.Value)
    If Not cell Is Nothing Then
        cell.Offset(0, 2).Value = "UW"
    Else
        MsgBox "Update MKP with current dates", vbCritical, "Error"
        Exit Sub
    End If
End If


'check if Sick is checked'
If Me.Sick.Value = True Then
    If Len(Me.DateRange.Value) = 0 Then
        MsgBox "Please enter a Date Range", vbCritical, "Error"
        Exit Sub
    End If
    emp = Me.employee.Value

    Set cell = Sheets("MKP_" & emp).Range("A10:A40").Find(Me.DateRange.Value)
    If Not cell Is Nothing Then
        cell.Offset(0, 2).Value = "CH"
    Else
        MsgBox "Update MKP with current dates", vbCritical, "Error"
        Exit Sub
    End If
End If


'check if either EndedProjectNo combobox or ActiveProjectNo combobox has a value, if yes, determine the sheet to update
    If Not IsEmpty(EndedProjectNo.Value) Or Not IsEmpty(ActiveProjectNo.Value) Then
        Dim ans As String
        Dim sheetName As String
        Dim dRng As Range
 
               
        If Len(Me.ActiveProjectNo.Value) > 5 Then
            Me.EndedProjectNo.Value = ""
            ans = Me.ActiveProjectNo.Value
            sheetName = "Godziny" + Left(ans, 5)
        ElseIf Len(Me.EndedProjectNo.Value) > 5 Then
            ActiveProjectNo.Value = ""
            ans = Me.EndedProjectNo.Value
            sheetName = "Godziny" + Left(ans, 5)
        End If
    End If

Set dRng = Sheets(sheetName).Range("D7:J7").Find(What:=Me.DateRange.Value, LookIn:=xlValues)

If Not dRng Is Nothing Then
    Dim emptyCell As Range
    Dim a As Integer
    
    For a = dRng.Row + 1 To 39
        If Cells(a, dRng.Column).Value = "" Then
            Set emptyCell = Cells(a, dRng.Column)
            Exit For
        End If
    Next a
    If Not emptyCell Is Nothing Then
        emptyCell = Me.HoursCount.Value
        Cells(emptyCell.Row, "C").Value = Me.JobType.Value
        Cells(emptyCell.Row, "B").Value = Me.employee.Value
    Else
        MsgBox "No empty cell available below " & dRng.Address
    End If


    ' Find next empty cell for JobType2 and HoursCount2
    For a = dRng.Row + 1 To 39
        If Cells(a, dRng.Column).Value = "" Then
            Set emptyCell = Cells(a, dRng.Column)
            Exit For
        End If
    Next a
    If Not emptyCell Is Nothing Then
        If Not IsEmpty(Me.JobType2.Value) And Not IsEmpty(Me.HoursCount2.Value) Then
            emptyCell = Me.HoursCount2.Value
            Cells(emptyCell.Row, "C").Value = Me.JobType2.Value
            Cells(emptyCell.Row, "B").Value = Me.employee.Value
        End If
    Else
        MsgBox "No empty cell available below " & dRng.Address
    End If

    ' Find next empty cell for JobType3 and HoursCount3
    For a = dRng.Row + 1 To 39
        If Cells(a, dRng.Column).Value = "" Then
            Set emptyCell = Cells(a, dRng.Column)
            Exit For
        End If
    Next a
    If Not emptyCell Is Nothing Then
        If Not IsEmpty(Me.JobType3.Value) And Not IsEmpty(Me.HoursCount3.Value) Then
            emptyCell = Me.HoursCount3.Value
            Cells(emptyCell.Row, "C").Value = Me.JobType3.Value
            Cells(emptyCell.Row, "B").Value = Me.employee.Value
        End If
    Else
        MsgBox "No empty cell available below " & dRng.Address
    End If

    ' Find next empty cell for JobType4 and HoursCount4
    For a = dRng.Row + 1 To 39
        If Cells(a, dRng.Column).Value = "" Then
            Set emptyCell = Cells(a, dRng.Column)
            Exit For
        End If
    Next a
    If Not emptyCell Is Nothing Then
        If Not IsEmpty(Me.JobType4.Value) And Not IsEmpty(Me.HoursCount4.Value) Then
            emptyCell = Me.HoursCount4.Value
            Cells(emptyCell.Row, "C").Value = Me.JobType4.Value
            Cells(emptyCell.Row, "B").Value = Me.employee.Value
        End If
    Else
        MsgBox "No empty cell available below " & dRng.Address
    End If

    ' Find next empty cell for JobType5 and HoursCount5
    For a = dRng.Row + 1 To 39
        If Cells(a, dRng.Column).Value = "" Then
            Set emptyCell = Cells(a, dRng.Column)
            Exit For
        End If
    Next a
    If Not emptyCell Is Nothing Then
        If Not IsEmpty(Me.JobType5.Value) And Not IsEmpty(Me.HoursCount5.Value) Then
            emptyCell = Me.HoursCount5.Value
            Cells(emptyCell.Row, "C").Value = Me.JobType5.Value
            Cells(emptyCell.Row, "B").Value = Me.employee.Value
        End If
    Else
        MsgBox "No empty cell available below " & dRng.Address
    End If

    ' Find next empty cell for JobType5 and HoursCount5
    For a = dRng.Row + 1 To 39
        If Cells(a, dRng.Column).Value = "" Then
            Set emptyCell = Cells(a, dRng.Column)
            Exit For
        End If
    Next a
    If Not emptyCell Is Nothing Then
        If Not IsEmpty(Me.JobType6.Value) And Not IsEmpty(Me.HoursCount6.Value) Then
            emptyCell = Me.HoursCount6.Value
            Cells(emptyCell.Row, "C").Value = Me.JobType6.Value
            Cells(emptyCell.Row, "B").Value = Me.employee.Value
        End If
    Else
        MsgBox "No empty cell available below " & dRng.Address
    End If
Else
    MsgBox "Value not found"
End If
End Sub

I would appreciate some help in this matter

 

[mysticmario]

bump.

 

[mysticmario]

new week new attempt.

 

[mohadin]

Hi
Replace That line with

With Sheets(sheetName)
Set dRng = .Range("D7:J" & .Cells(Rows.Count, 8).End(xlUp).Row).Find(Me.DateRange.Value, , , 1)
End With

 

[mysticmario]

Hi
Replace That line with

With Sheets(sheetName)
Set dRng = .Range("D7:J" & .Cells(Rows.Count, 8).End(xlUp).Row).Find(Me.DateRange.Value, , , 1)
End With

Still not finding the value.
I have to try different approach. Maybe I'll try with arrays.