Solved

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

Posted on 2008-10-17
7
245 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
  • 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now