VBA Runtime error 424 - Error when using array for cells()

Question

I've got an Excel Spreadsheet with multiple Sheets containing various Information from our company's employees (mitarbeiter). Now, I created an array containing their names, which are the same as the Worksheet names. I tought of using this to loop through all sheets one by one, retrieving some information and print it on another sheet i prepared.

At last, I was trying to select each worksheet to see if it was selecting the right sheets....which ended up with "Runtime Error 424-object required"

Sub Makro3()
Dim mitarbeiter As Variant
Dim lastrow As Long
Dim lastrowcol As Long

ReDim mitarbeiter(0 To 12) As Variant
mitarbeiter = Split(*censored*)
    
For i = LBound(mitarbeiter) To UBound(mitarbeiter)
   
With Sheets(mitarbeiter(i))
lastrow = .Cells(Rows.count, 13).End(xlUp).Row
lastrowcol = .Cells(Rows.count, 13).End(xlUp).Column
End With

ThisWorkbook.Worksheets(mitarbeiter(i).Cells(lastrow, lastrowcol)).Select 'crashes here
MsgBox mitarbeiter(i)

Next i
End Sub

Darrell H · Accepted Answer · 2020-09-08 14:08:58Z

2

It is your parenthesis, s/b Worksheets(mitarbeiter(i)).Cells(lastrow, lastrowcol).Select

answered Sep 8, 2020 at 14:08

Darrell H

1,8961 gold badge11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leon S Over a year ago

okay, seemed to have worked; why does it say runtime error 9 on the "With" part? (Index out of bounds) –

Darrell H Over a year ago

Runtime error 9 means you are calling something that doesn't exist. It is possible there are not enough elements in your split to fill the array and you are calling a blank at the end. A good way to check is to step through using F8 and watch the variables.

Collectives™ on Stack Overflow

VBA Runtime error 424 - Error when using array for cells()

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related