L
Laura L
I'm trying to modify the code below so that 1) when using the pull down menu,
it populates the cell below it , and 2) it only applies to one column. Right
now, when you use the pull down menu it just populates that same cell, not
the one below it. Any help is greatly appreciated.
Thanks,
Laura
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
lCol = Target.Column 'column with data validation cell
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 7
If Target.Offset(0, 1).Value = "" Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1
End If
Cells(lRow, lCol).Value = Target.Value
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
it populates the cell below it , and 2) it only applies to one column. Right
now, when you use the pull down menu it just populates that same cell, not
the one below it. Any help is greatly appreciated.
Thanks,
Laura
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
lCol = Target.Column 'column with data validation cell
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 7
If Target.Offset(0, 1).Value = "" Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1
End If
Cells(lRow, lCol).Value = Target.Value
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub