Solved

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

Posted on 2008-10-17
7
255 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

726 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