Method value of object range failed.

[vipa2000]

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.

[vipa2000]

Also, I tried changing the target.column to a range i.e. C4:I56 with no joy.

[SamT]

Only works on changed cells within Range("F5:L150"). Works on all changed Cells in that Range. Prevents Change Events occurring while Cells are rechanged.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MainRng As Range
Set MainRng = Range("F5:L150") 'Prevent Excel looking at the Workbook Twice. Also helps when expanding this sub.

Application.EnableEvents = False

If Not intersect(Target, MainRng) is Nothing Then SetTargetValues Intersect(Target, MainRng)

Application.EnableEvents = True
End Sub


Private Sub SetTargetValues(ByVal Target As Range)
Dim Cel As Range
Dim DP As Range
Set DP = Range("DropDown") 'Prevent Excel from looking at the Workbook every iteration.

For Each Cel in Target
Cel = Application.VLookUp(Cel, DP, 2, False)
Next Cel
End Sub
Although I would use the actual Lookup Table Range instead of a Range Name

[Paul_Hossler]

Something like this should handle more than one cell being changed at once




Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range, R As Range
Dim selectedNum As Variant, selectedNA

Set xRg = ActiveWorkbook.Names("DropDown").RefersToRange


Application.EnableEvents = False


For Each R In Target.Cells
With R
selectedNA = .Value

If .Column >= 6 And .Column <= 12 And .Row >= 5 And .Row <= 150 Then
selectedNum = Application.VLookup(selectedNA, xRg, 2, False)
If Not IsError(selectedNum) Then .Value = selectedNum
End If
End With
Next


Application.EnableEvents = True
End Sub

[vipa2000]

Thanks Sam and Paul for the quick response. Stuck with the slightly expanded solution from Paul. Works prefectly.