Excel VBA_ Method Range failed for Object Worksheet (Runtime Error 1004)

Question

I coded some vba procedures in different modules. Then, there is a UserForm and in the UserForm there are Subs like this within I call the subs in the modules:

Sub uf2_imp_new_Click()
 Call a_xxx_delete '[Sub in Module A]
 Call a_xxx_import '[Sub in Module A]
 Call b_TransXXX    '[Sub in Module B]
 Call c_plist_reset_all '[Sub in Module C]
 Call c_xxx_listfrom_transANB '[Sub in Module C]
End Sub

When Executing I get a runtime error 1004 "Method Range failed for Object Worksheet" in Module C Sub c_xxx_listfrom_transANB:

Option Explicit
Sub c_xxx_listfrom_transANB()
 Dim wb As Workbook
 Dim ws_trans_anb As Worksheet
 Dim ws_plist As Worksheet
 Dim zeile_trans As Integer  
 Dim zeile_plist As Integer

 Set wb = ThisWorkbook
 Set ws_trans_anb = wb.Worksheets("Trans_XXX")       
 Set ws_plist = wb.Worksheets("PROJEKTLISTE")  

 letztezeile = ws_trans_anb.Cells(Rows.Count, 1).End(xlUp).Row

 For zeile_trans = 2 To letztezeile
  [...]
  ws_plist.Range(Cells(zeile_plist, 2), Cells(zeile_plist, 16)).Borders(xlEdgeBottom).LineStyle = xlContinuous
 Next
EndSub

The highlighted Row when the error occurs is

ws_plist.Range(Cells(zeile_plist, 2), Cells(zeile_plist, 16)).Borders(xlEdgeBottom).LineStyle = xlContinuous

Does anyone has an idea how to fix that? I coded the module c days ago and there never was an error. But now when Calling the sub via User form it doesnt work anymore.. And its strange: the I execute the Sub in Module C by klicking "Play" in VBA, the error occurs. But when I execute the Sub in Module C Step by Step there is no error. Thats too much for me.. :) Can anyone help?

ws_plist.Range(Cells does not mean ws_plist.Range(ws_plist.Cells so you need to fully qualify all of your cell references like that too — braX
ws_plist.Range(Cells actually means ws_plist.Range(ActiveSheet.Cells - so when wsp_plist is the same as ActiveSheet it will work, and when it's not, it won't. — braX

Variatus Variatus · Accepted Answer · 2020-06-25T06:57:14

Your code is imprecise. You are counting the rows in one sheet (the ActiveSheet) and then use that number in another sheet. You also presume that the cells you are defining are on the same sheet as the range for which you use them. The result shouldn't crash VBA but then you are faced with a situation where VBA doesn't behave as you expect. So, increase the precision as shown below.

Sub c_xxx_listfrom_transANB()
    ' 056
    
     Dim Wb             As Workbook
     Dim Ws_trans_anb   As Worksheet
     Dim Ws_plist       As Worksheet
     Dim zeile_trans    As Integer      ' should be Long
     Dim zeile_plist    As Integer      ' should be Long
     Dim Rng            As Range
    
     Set Wb = ThisWorkbook
     Set Ws_trans_anb = Wb.Worksheets("Trans_XXX")
     Set Ws_plist = Wb.Worksheets("PROJEKTLISTE")
    
     With Ws_trans_anb
        ' count the rows in the same worksheet where you define the last cell
        letztezeile = .Cells(.Rows.Count, 1).End(xlUp).Row
     End With
    
     For zeile_trans = 2 To letztezeile
     ' [...]
     With Ws_plist
        Set Rng = .Range(Cells(zeile_plist, 2), Cells(zeile_plist, 16))
         Rng.Borders(xlEdgeBottom).LineStyle = xlContinuous
     Next
End Sub

In the code above you will be able to step up to the faulty line with F8 and examine the components before the crash happens.

You may find that zeile_plist isn't what you think it is. Logically, it should be zeile_trans, using the loop counter? It might be 0 and that would cause a cash.

I think you need to work on your variable naming. Snaking names with underscores makes them less readable. Your use of lower case only has the same effect. ZeilePlist and ZeileTrans would be an improvement. But the word Zeile isn't what differentiates these names. I would probably use Zplist and Ztrans, perhaps with a better chance of not falling into the trap you found yourself in.

Excel VBA_ Method Range failed for Object Worksheet (Runtime Error 1004)

2 Answers