Agnarr
New Member
- Joined
- Jan 15, 2023
- Messages
- 29
- Office Version
- 365
- Platform
- Windows
Hello everyone.
I made an excel sheet for a work schedule. Under each day one can choose a drop down menu to choose a specific time or phrase and through vba it automatically shows the end time on the adjacent cell.
My problem is that when i delete one cell or change the time to nothing (it's an option from the drop down list), it erases everything on the row. How could i stop that from happening?
I made an excel sheet for a work schedule. Under each day one can choose a drop down menu to choose a specific time or phrase and through vba it automatically shows the end time on the adjacent cell.
My problem is that when i delete one cell or change the time to nothing (it's an option from the drop down list), it erases everything on the row. How could i stop that from happening?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputR As Range, codeR As Range, cell As Range
Dim f, fnd As Range
If Intersect(Target, Range("c4:ad50")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set fnd = Sheets("Holidays").Range("f:f").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
Target.Offset(, 1) = fnd.Offset(, 2)
End If
Application.ScreenUpdating = True
Set inputR = Range("c4:ad50")
Set codeR = Worksheets("Holidays").Range("f:f")
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, inputR) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
For Each cell In Target
Set f = codeR.Find(cell.Value, , , xlWhole)
If Not f Is Nothing Then cell.Value = f.Offset(, 1).Value
Next
.EnableEvents = True
End With
End Sub