Run-time error '1004 Method range of object worksheet failed

[Dan Swartz]

So I have the following code and it does exactly what I need. However, if I move, copy or delete contents in more than one cell at a time, I get a run time error, and no matter if I select "End" or "Debug", It crashes Excel. I'm assuming I'm missing some error handling code, but I'm an amateur with code. I have searched and searched and I find no answer to my problem.

Private Sub Worksheet_Change(ByVal Target As Range)

WoodSpecies = Target.Value
If Target.Column = 5 Then
WoodSpeciesNumber = Application.VLookup(WoodSpecies, Sheet20.Range("WoodSpeciesClazakNumber"), 2, False)
If Not IsError(WoodSpeciesNumber) Then
Target.Value = WoodSpeciesNumber
End If
End If
End Sub

Basically, this code allows me to select a wood species from a dropdown list, then will change it to a part number after it's entered.

 

[Fluff]

One of the problems is that you have what is effectively a permanent looping running. Try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 5 Then
      WoodSpecies = Target.Value
      WoodSpeciesNumber = Application.VLookup(WoodSpecies, Sheet20.Range("WoodSpeciesClazakNumber"), 2, False)
      If Not IsError(WoodSpeciesNumber) Then
         On Error GoTo Xit
         Application.EnableEvents = False
         Target.Value = WoodSpeciesNumber
      End If
   End If
Xit:
   Application.EnableEvents = True
End Sub

 

[Marc L]

​

Add at the beginning of this event procedure this codeline : If Target.CountLarge > 1 Then Exit Sub …​

 

[Dan Swartz]

You guys are amazing! that worked! thanks guy!

 

[Fluff]

Glad we could help & thanks for the feedback.