VBA Modyfing loop to create a new line on specific conditions

[mysticmario]

Hello again good people of MrExcel thsi is the loop:

For Each emptyCell In dRng
    If emptyCell.Value2 = CLng(FindDate) 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
                If Cells(emptyCell.Row, "B").Value = Me.employee.Value And Cells(emptyCell.Row, "C").Value = Me.JobType.Value Then
                emptyCell.Value = Me.HoursCount.Value
                emptyCellFound = True 'set the flag to indicate that an empty cell has been found
                Exit For
                Else
                    emptyCell.Value = Me.HoursCount.Value
                    Cells(emptyCell.Row, "C").Value = Me.JobType.Value
                    Cells(emptyCell.Row, "B").Value = Me.employee.Value
                    emptyCellFound = True 'set the flag to indicate that an empty cell has been found
                    Exit For 'exit the inner loop if an empty cell is found
                End If
            End If
        Next b
   
    End If

I want else statement of this code to apply to an empty cell in collumn but only if cell in B and C column of this row is also empty, not the first empty cell it encounters in this column.
so the loops has to find the first empty cell in a row where B and C are also empty for this else statement.
Visual reference:
I have this set of data created by the else statement of my given code:

I run the loop using the same JobType.values but with a newer date,
and I get an update to jobtype with new set of hours but for the next day:

Now the next day my employee did none of these jobtypes, he in fact did new jobtype "M",
So when i try to use this loop as it is I get:

as you can see it replaced the first jobtype with new jobtype cause it doesn't know that it should use different empty cell.
I want to get this instead:


I cant wrap my head around how to this loops to act this way I tried using nested loop to find new empty cell ina row where B and C is also empty but it only broke the code completely.
Maybe someone has an idea how make it happen?
Thank you in advance!

 

[mysticmario]

bump

 

[mysticmario]

Mayeb an 'if' statement that lets 'Else' statement be executed only when cells in B and C column on the saem row are empty, else loop again to find new empty cell? But how to make this loop functional?

 

[dmt32]

Hi,
untested but try this addition to your code & see if it will do what you want

For Each emptyCell In dRng
        If emptyCell.Value2 = CLng(FindDate) 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
               
                'check if columns B  & C are both empty
               If Not CBool(Cells(b, 2) & Cells(b, 3) = "") Then
                   
                    Set emptyCell = Cells(b, colNum)
                    ' Add jobtype and hours
                    If Cells(emptyCell.Row, "B").Value = Me.employee.Value And Cells(emptyCell.Row, "C").Value = Me.JobType.Value Then
                        emptyCell.Value = Me.HoursCount.Value
                        emptyCellFound = True        'set the flag to indicate that an empty cell has been found
                        Exit For
                    Else
                        emptyCell.Value = Me.HoursCount.Value
                        Cells(emptyCell.Row, "C").Value = Me.JobType.Value
                        Cells(emptyCell.Row, "B").Value = Me.employee.Value
                        emptyCellFound = True        'set the flag to indicate that an empty cell has been found
                        Exit For        'exit the inner loop if an empty cell is found
                    End If
                   
                Else
                    'both cells empty - do stuff here
                   
                End If
               
            End If
        Next b
        
    End If

 

[mysticmario]

Before applying this I'm trying to understand it.
How this block woudl work?
If b,2 and b, 3 is not empty then execute:

                  If Cells(emptyCell.Row, "B").Value = Me.employee.Value And Cells(emptyCell.Row, "C").Value = Me.JobType.Value Then
                        emptyCell.Value = Me.HoursCount.Value
                        emptyCellFound = True        'set the flag to indicate that an empty cell has been found
                        Exit For
                    Else
                        emptyCell.Value = Me.HoursCount.Value
                        Cells(emptyCell.Row, "C").Value = Me.JobType.Value
                        Cells(emptyCell.Row, "B").Value = Me.employee.Value
                        emptyCellFound = True        'set the flag to indicate that an empty cell has been found
                        Exit For        'exit the inner loop if an empty cell is found
                    End If

?

 

[dmt32]

If B and or C are not empty, line should return False and execute your block otherwise, True for you to do whatever in the Else statement.

Dave

 

[mysticmario]

If B and or C are not empty, line should return False and execute your block otherwise, True for you to do whatever in the Else statement.

Dave

Ok, this is the code I ended up with, hopefully it will work as expected it will show with more testing.

'find date'
Sheets(sheetName).Activate
FindDate = CDate(Me.DateRange.Value)
Set dRng = Range("D7:J7,D67:J67,D127:J127,D187:J187,D247:J247,D307:J307,D367:J367,D427:J427,D487:J487,D547:J547,D607:J607")
Dim emptyCellFound As Boolean 'variable to track if an empty cell has been found
emptyCellFound = False

For Each emptyCell In dRng
    If emptyCell.Value2 = CLng(FindDate) 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
                If Not CBool(Cells(b, 2) & Cells(b, 3) = "") Then 'return fals and executes  below
                    If Cells(emptyCell.Row, "B").Value = Me.employee.Value And Cells(emptyCell.Row, "C").Value = Me.JobType.Value Then
                    emptyCell.Value = Me.HoursCount.Value
                    emptyCellFound = True 'set the flag to indicate that an empty cell has been found
                    Exit For
                    End If
                Else 'if cBool returns True executes below
                    emptyCell.Value = Me.HoursCount.Value
                    Cells(emptyCell.Row, "C").Value = Me.JobType.Value
                    Cells(emptyCell.Row, "B").Value = Me.employee.Value
                    emptyCellFound = True 'set the flag to indicate that an empty cell has been found
                    Exit For 'exit the inner loop if an empty cell is found
                End If
            End If
        Next b
   
    End If
   
    If emptyCellFound Then 'exit the outer loop if an empty cell has been found
        Exit For
    End If
Next emptyCell

If Not emptyCellFound Then 'check if an empty cell was found
    MsgBox "No empty cell available below " & dRng.Address
    Exit Sub
End If

Thank you for your assistance!
Have a great day!

 

[dmt32]

Thank you for your assistance!
Have a great day!

welcome glad resolved & appreciate feedback

Dave