Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-17
7
Medium Priority
?
262 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 240 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 360 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

664 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