Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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

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
challenged
Asked:
challenged
  • 3
  • 2
  • 2
2 Solutions
 
Saurabh Singh TeotiaCommented:
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
 
mdouganCommented:
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
 
challengedAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Saurabh Singh TeotiaCommented:
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
 
challengedAuthor Commented:
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
 
challengedAuthor Commented:
see my last comment
0
 
mdouganCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now