Excel VBA - selecting a cell in different sheet using GoTo and IF statement giving error.

Question

Hi all,

I am trying to select a cell in different sheet in my code using nested IF and GoTo, however, I am getting Runtime error:1004

Code is as below.

I apologise, It might look very amateur :) 

Answer 1

Hello I am an independent advisor for the Microsoft community. I've noticed on the highlighted line of code, you put the name of the range in quotation marks. You didn't do that for the other two names of the range. Is there a reason why you did that differently? I did a search and the "Runtime Error 1004" isn't specific enough to pinpoint the cause of the problem. I hope this helps you but try removing the quotation marks from the line of code giving you the problem.

Answer 2

Thanks for your reply.

I am constantly checking the code in Debug stage. So I knew that, the code was going to jump over to this line, rather than the others when I was running the code in Debug,  Correct way is to use speech marks in giving the range.

But, when I use  ""   Range("Staff_Name").Select   ""   in any other VBA code, it follows it through and it doesn't here.  Can I not use Range().Select within If statement or GoTo statement?

Answer 3

Yes you can use the Range().Select command within IF and GoTo statements. Can I ask why you even used GoTo statements? Why not just Select the range from within the IF statement?

Answer 4

This  macro is for selection of other procedures in different sheets.  I have 6 other processes, which start with taking data from users, putting them in their respective lists and sorting the lists.  

So, this little procedure is supposed to get the user to select a process, compare with names available and go to the process which will let them enter data and do the rest.

Answer 5

Hi Abraham,

I found it and it was staring me in the face.

Instead of writing Sheets("sheetName").Range(RangeName).Select

It wants me to write

Sheets(xxx).Select

Range(xxx).Select

Thanks for your interest :)