vipa2000
03-11-2022, 09:57 AM
Hi, I have the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
selectedNa = Target.Value
If Target.Column >= 6 And Target.Column <= 12 And Target.Row >= 5 And Target.Row <= 150 Then
Set xRg = ActiveWorkbook.Names("DropDown").RefersToRange
selectedNum = Application.VLookup(selectedNa, xRg, 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub
In my sheet, if I copy one cell down all is OK. If i try and drag and copy a cell down over many it crashes with the error - Method value of object range failed. It's like it cannot write the data to the new cells fast enough before it errors.
Thanks in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
selectedNa = Target.Value
If Target.Column >= 6 And Target.Column <= 12 And Target.Row >= 5 And Target.Row <= 150 Then
Set xRg = ActiveWorkbook.Names("DropDown").RefersToRange
selectedNum = Application.VLookup(selectedNa, xRg, 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub
In my sheet, if I copy one cell down all is OK. If i try and drag and copy a cell down over many it crashes with the error - Method value of object range failed. It's like it cannot write the data to the new cells fast enough before it errors.
Thanks in advance.