Run Time Error 1004

[jmd78]

When opening a workbook for the first time I receive the following Run time Error 1004, Select method of Range Class Failed. Anyone have a suggestion what may be causing this error? My code is below. Any suggestions would be helpful and appreciated. Glad to upload the workbook, if needed, just tell me how. Thank you for your consideration and help.


Dim aTabOrd As Variant
Dim iTab As Long
Dim nTab As Long


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim iNew As Long

If IsEmpty(aTabOrd) Then
aTabOrd = Array("C3", "C5", "C7", "G7", "C9", "F15", "L3", "L5", "L7", "M11", "J13", "C11", "E16", "H18", "F20", "I20", "G22", "G24", "M27", "H27", "H29", "H31", "H33", "H39", "H41", "H43", "H45", "H47", "H49", "G51", "H51", "H53")
nTab = UBound(aTabOrd) + 1
iTab = 0
Else
On Error Resume Next
iNew = WorksheetFunction.Match(Target(1, 1).Address(False, False), aTabOrd, 0) - 1
If Err Then
iTab = (iTab + 1) Mod nTab
Else
iTab = iNew
End If
On Error GoTo 0
End If

Application.EnableEvents = False
Range(aTabOrd(iTab)).Select
Application.EnableEvents = True

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range("C11")) Is Nothing Then
Application.EnableEvents = False
Range("C11").Value = Range("L9").Value + Range("C11").Value
Application.EnableEvents = True
End If


If Intersect(Target, Range("H18, G51")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value > 0 Then Target = Target.Value * -1


End Sub

 

[Logit]

.
That error message usually refers to a range reference in the code. But, you need to narrow down the issue.

In your macro, there are two lines that start with ON ERROR .... immediately in front of each of these lines, place a quotation mark '
This is referred to as 'commenting out' the line.

Run your code again and see if a specific line in your macro is highlighted when the error code appears. Report that code line here so we know which line it is.

Another way of doing the same thing is to open the VBE so you can see the code, place your cursor at the top of the macro and press F8. The code will execute
one line at a time until it gets to the line causing the error. Report that line back to the FORUM.

 

[NoSparks]

Another way of doing the same thing is to open the VBE so you can see the code, place your cursor at the top of the macro and press F8. The code will execute

@ Logit
You should try that. Think you'll find a sub with arguments in the brackets won't run that way.
@ jmd78
Because breakpoints aren't maintained when a workbook is closed, put a STOP instruction as the first line in each of those subs, close the workbook and re-open it. When the code stops and shows the VBE, Stop will be highlighted, use the F8 key to step through the code from there.

Also, your Worksheet_Change code disables and re-enables events for the first possible writing to the sheet, but not the second.

 

[jmd78]

.
That error message usually refers to a range reference in the code. But, you need to narrow down the issue.

In your macro, there are two lines that start with ON ERROR .... immediately in front of each of these lines, place a quotation mark '
This is referred to as 'commenting out' the line.

Run your code again and see if a specific line in your macro is highlighted when the error code appears. Report that code line here so we know which line it is.

Another way of doing the same thing is to open the VBE so you can see the code, place your cursor at the top of the macro and press F8. The code will execute
one line at a time until it gets to the line causing the error. Report that line back to the FORUM.

The offending line appears to be at: Range(aTabOrd(iTab)).Select

 

[jmd78]

The offending line appears to be at: Range(aTabOrd(iTab)).