issues with Run time error 9 on vba code

Question

I need help debugging a weird bug i found on one of my workbooks. I wrote some vba code for an excel file and it somehow calling a different workbook. Ill give an example lets say i have an excel file called "test1", This file has some references from another workbook called "test2" The following code only applies from the workbook from "test1"

   lastRow = Cells(Rows.Count, 1).End(xlUp).Row ' will get the last row
   MsgBox (lastRow) 'wrote this for debugging purposes
   Worksheets("SalesContest").Range("A3:A" & lastRow).Clear

I have this code so it can clear the contents for row A only in "test1" file But the problem comes when I randomly open "test2" and somehow it runs the code above on "test2" even though its suppose to run only on "test1" So when i open "test2" there is a messagebox that says the number of rows in "test2" and it also gives me the following error message "RUn time error 9" Subscript out of range" Is there a way i can fix this so it doesnt call "test2" but only "test1"

Patrick Honorez · Accepted Answer · 2013-08-19 20:31:22Z

1

You can use ThisWorkbook.Worksheets("SalesContest").Range("A3:A" & lastRow).Clear to make sure it's applying to the workbook where the code is running.

answered Aug 19, 2013 at 20:31

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

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

1 Comment

Some user Over a year ago

it fixed that error message. but i have a code on "test1" that output results to col A on "test1" now it outputs the code on "test2" ..

Collectives™ on Stack Overflow

issues with Run time error 9 on vba code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related