Solved

Visual Basic for Excel - Tough time selecting cells in a worksheet

Posted on 2008-10-17
7
261 Views
Last Modified: 2013-11-26
I'm sure i'll kick myself when I get the answer, but this is driving me CRAZY!!!

The code is run via a button on the sheet "Time Sheets"

Here is the code...
Sheets("Cumulative Hours").Range("D1").Value = k
Sheets("Cumulative Hours").Range("E1").Value = a
Sheets("Cumulative Hours").Range("F1").Value = b
   
Sheets("Cumulative Hours").Range(Range("A1").Offset(0, 1 + a * 3), Range("A1").Offset(0, 3 + a * 3)).EntireColumn.Select

When I run the code, the first 3 lines work... k = 11 so in D1 i get a 11, E1 = 10, F1 = 9
I'm calling the sheet correctly and the values are valid for a, b & k

but it hangs on the last line with the error...

Runtime error 1004 - Application-defined or object-defined error

If I remove the code "Sheets("Cumulative Hours")." from the statement... the columns are highlighted in the "Time Sheets" worksheet with no problem. I don't get it!!!

Thanks in advance for you help!

Dean
0
Comment
Question by:challenged
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 59

Assisted Solution

by:Saurabh Singh Teotia
Saurabh Singh Teotia earned 80 total points
ID: 22741218
Dean...two things....

frist move your code from the sheet module to a new module which you need to do by insert module..and paste the code there...

second break your this line into two which is..

Sheets("Cumulative Hours").Select
Range(Range("A1").Offset(0, 1 + a * 3), Range("A1").Offset(0, 3 + a * 3)).EntireColumn.Select

This will do what you are looking for...

Saurabh...
0
 
LVL 18

Accepted Solution

by:
mdougan earned 120 total points
ID: 22741280
Sounds like you just need to qualify the Range statements with the Sheet reference.  So, change this:

Sheets("Cumulative Hours").Range(Range("A1").Offset(0, 1 + a * 3), Range("A1").Offset(0, 3 + a * 3)).EntireColumn.Select

To this:

Sheets("Cumulative Hours").Range(Sheets("Cumulative Hours").Range("A1").Offset(0, 1 + a * 3), Sheets("Cumulative Hours").Range("A1").Offset(0, 3 + a * 3)).EntireColumn.Select
0
 

Author Comment

by:challenged
ID: 22741388
Right now you are right its in my sheet module... i moved it, but how to I reference it in terms of the button on the sheet "Time Sheets" (it is on there for a reason, and there are a number of lines that pertain to that sheet within this code)

Thanks!!!
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 22741642
Well you can right use button which is given in forms toolbar in which you can assigned to any macro that you want to....

Also lines pretating to that sheet you can always select the respective sheet before the macro is run...

Saurabh...
0
 

Author Comment

by:challenged
ID: 22741860
Not sure if yours would have worked Saurabh, didn't try it... turns out this worked...

Sheets("Cumulative Hours").Select
Sheets("Cumulative Hours").Range(Sheets("Cumulative Hours").Range("A1").Offset(0, 1 + a * 3), Sheets("Cumulative Hours").Range("A1").Offset(0, 3 + a * 3)).EntireColumn.Select

I tried adding in "Sheets("Cumulative Hours")." before as mdougan suggested, but it didn't work without the "Sheets("Cumulative Hours").Select" before it.

Thanks all for the help!
0
 

Author Closing Comment

by:challenged
ID: 31507133
see my last comment
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22742056
In the future, one thing you can try is to turn the Macro recorder on, then manually select the range of cells you are interested in, then stop the recorder and look at the VB script that it generated.  It will probably use slightly different sheet references, perhaps ActiveSheet instead of the named sheet, however, all the intermediate steps in the code should be there so you know what you have to do and in what order.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question