Run-time error '1004': Method 'Range' of object '_worksheet' failed

[lukman4068]

Hi

Hope you can help with this pesky VBA problem!

I don't know how to use VBA but I was sent a spreadsheet to update employee notes

There's an input console to type in the data but every time I choose anything with a validation list I get the message:

"Run-time error '1004': Method 'Range' of object '_worksheet' failed"

This is the code that comes up when I click to Debug - I've highlighted in Red where Excel shows the error:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Set rngA = ActiveCell
If Target.Address = Me.Range("CurrRec").Address _
Or Target.Address = Me.Range("OrderSel").Address Then
Application.EnableEvents = False

If Target.Address = Me.Range("OrderSel").Address Then
Me.Range("CurrRec").Value = Me.Range("SelRec").Value
End If
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("PartsData")

With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With

With inputWks
lRec = .Range("CurrRec").Value
If lRec > 0 And lRec <= lLastRec Then
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 6)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
rngA.Select
End If
End With
Application.EnableEvents = True
End If
End Sub




I've got no idea how to fix it so any help to just make it go away would be appreciated! It was working fine on Excel 2010 but it's only now I'm trying to open it in Excel 2003 that its causing problems.

The excel file has only 2 tabs -
1 named "Input"
the other "Database"

Any help much appreciated as ever!

Thanks

 

[JLeno]

The line that gives the error uses two named ranges (CurrRec, and OrderSel). If these named ranges do not exist in the worksheet, that is the source of your error. You can check all named ranges using Ctrl + F3 when you're in the sheet.

If that's the problem, give the relevant cells the appropriate names, or give the sender of the code a call. If that's not the problem, please let me / us know!

 

[lukman4068]

Thanks so much - but :/ I don't have cells with those names - is there a way to just delete the named ranges? Or is it easier just to get those cells back

 

[Firefly2012]

If you open the Input sheet and go into Insert>Names>Define via the xl2003 menu, do you see any names listed there namd CurrRec and OrderSel?

 

[lukman4068]

Hi Thanks for all your help here - I just selected a random cell from the CTRL F3 box and its got rid of the error and doesn't seem to have had any detrimental impact

Thank youuu everyone! Much appreciated