mysticmario
Active Member
- Joined
- Nov 10, 2021
- Messages
- 323
- Office Version
- 365
- Platform
- Windows
I have this huge block of code, where The date is being found based on userform input, then the empty cell is being found in the same row to add values to it and soem 2 more values to column C and B on the same row.
When i run this and lets say input 22.02.2023 as date to be found I get something like this which is great:
but then I run the loop again with different date, and accordingly to what I want - if the employee value and Jobtype value is already there, just update the row with new hours
but for some reason i get the new row with Me.employee.value, Me.JobType.value and Me.HoursCount.value in this example it would be" Janusz", "SK," and "1", but the rest of the values from Jobtype2, jobtype3 and so on are being updated in the same row as I wanted.
Can you help em spot the issue? Here's the code:
When i run this and lets say input 22.02.2023 as date to be found I get something like this which is great:
but then I run the loop again with different date, and accordingly to what I want - if the employee value and Jobtype value is already there, just update the row with new hours
but for some reason i get the new row with Me.employee.value, Me.JobType.value and Me.HoursCount.value in this example it would be" Janusz", "SK," and "1", but the rest of the values from Jobtype2, jobtype3 and so on are being updated in the same row as I wanted.
Can you help em spot the issue? Here's the code:
VBA Code:
'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 Cells(emptyCell.Row, "B").Value = Me.employee.Value And Cells(emptyCell.Row, "C").Value = Me.JobType.Value Then
emptyCell.Value = Me.HoursCount.Value
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
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
' Find next empty cell for JobType2 and HoursCount2
If Len(Me.JobType2.Value) > 0 And Len(Me.HoursCount2.Value) > 0 Then
emptyCellFound = False 'reset the flag
For Each emptyCell In dRng
If emptyCell.Value2 = CLng(FindDate) Then
colNum = emptyCell.Column 'get the column number where the date was found
For b = emptyCell.Row + 1 To emptyCell.Row + 32
If Cells(b, colNum).Value = "" 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.JobType2.Value Then
emptyCell.Value = Me.HoursCount2.Value
Else
emptyCell.Value = Me.HoursCount2.Value
Cells(emptyCell.Row, "C").Value = Me.JobType2.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
End If
End If
Next b
End If
If emptyCellFound Then 'exit the 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
End If
'find next emoty cell for JobType3 and HoursCount3
If Len(Me.JobType3.Value) > 0 And Len(Me.HoursCount3.Value) > 0 Then
emptyCellFound = False 'reset the flag
For Each emptyCell In dRng
If emptyCell.Value2 = CLng(FindDate) Then
colNum = emptyCell.Column 'get the column number where the date was found
For b = emptyCell.Row + 1 To emptyCell.Row + 32
If Cells(b, colNum).Value = "" 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.JobType3.Value Then
emptyCell.Value = Me.HoursCount3.Value
Else
emptyCell.Value = Me.HoursCount3.Value
Cells(emptyCell.Row, "C").Value = Me.JobType3.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
End If
End If
Next b
End If
If emptyCellFound Then 'exit the 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
End If
'find next emoty cell for JobType4 and HoursCount4
If Len(Me.JobType4.Value) > 0 And Len(Me.HoursCount4.Value) > 0 Then
emptyCellFound = False 'reset the flag
For Each emptyCell In dRng
If emptyCell.Value2 = CLng(FindDate) Then
colNum = emptyCell.Column 'get the column number where the date was found
For b = emptyCell.Row + 1 To emptyCell.Row + 32
If Cells(b, colNum).Value = "" 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.JobType4.Value Then
emptyCell.Value = Me.HoursCount4.Value
Else
emptyCell.Value = Me.HoursCount4.Value
Cells(emptyCell.Row, "C").Value = Me.JobType4.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
End If
End If
Next b
End If
If emptyCellFound Then 'exit the 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
End If
'find next emoty cell for JobType5 and HoursCount5
If Len(Me.JobType5.Value) > 0 And Len(Me.HoursCount5.Value) > 0 Then
emptyCellFound = False 'reset the flag
For Each emptyCell In dRng
If emptyCell.Value2 = CLng(FindDate) Then
colNum = emptyCell.Column 'get the column number where the date was found
For b = emptyCell.Row + 1 To emptyCell.Row + 32
If Cells(b, colNum).Value = "" Then
Set emptyCell = Cells(b, colNum)
' Add jobtype and hours
emptyCell.Value = Me.HoursCount2.Value
If Cells(emptyCell.Row, "B").Value = Me.employee.Value And Cells(emptyCell.Row, "C").Value = Me.JobType5.Value Then
emptyCell.Value = Me.HoursCount5.Value
Else
emptyCell.Value = Me.HoursCount5.Value
Cells(emptyCell.Row, "C").Value = Me.JobType5.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
End If
End If
Next b
End If
If emptyCellFound Then 'exit the 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
End If
'find next emoty cell for JobType6 and HoursCount6
If Len(Me.JobType6.Value) > 0 And Len(Me.HoursCount6.Value) > 0 Then
emptyCellFound = False 'reset the flag
For Each emptyCell In dRng
If emptyCell.Value2 = CLng(FindDate) Then
colNum = emptyCell.Column 'get the column number where the date was found
For b = emptyCell.Row + 1 To emptyCell.Row + 32
If Cells(b, colNum).Value = "" 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.JobType6.Value Then
emptyCell.Value = Me.HoursCount6.Value
Else
emptyCell.Value = Me.HoursCount6.Value
Cells(emptyCell.Row, "C").Value = Me.JobType6.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
End If
End If
Next b
End If
If emptyCellFound Then 'exit the 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
End If