Worksheet Codename - Crazy Method Error!

E

EphesiansSix

Hello All -

I'm a VBA beginner, and new to the boards. I've written a program in
VBA (Excel) and everything is going well...except one thing, even after
searching all over the place.
I refer to my worksheets by their codename ( as a good practice).
Excel gives me a 1004 error, "Application-defined or object-defind
error" on both of these these lines (not together in the code):

shInput.Cells(15, 2).Offset(0, varEnabledCount).Select

varNumber = shInput.Cells(18, varActiveCellCol).Value

where shInput is a sheet codename in the workbook where the module
resides, and I already used ThisWorkbook.Activate. However, this line
of code works juuust fine:

shInput.Cells(65,2).Value = 777

Furthermore, they -all- work if I have the shInput sheet activated
before I run the procedure! Why won't they work if I am looking at any
sheet while executing the procedure? Am I nuts? Is VBA broken? Am I
missing something very simple? (I really hope it's the last one).

::kicks Excel::

Thanks in advance!
 
J

Jim Thomlinson

You can not select a cell on a sheet that is not active. That expalins the
first error. The second error is (as Bob points out) most likely a function
of your variable being not a valid number (such as a decimal, or 0 or greater
than 256)
 
E

EphesiansSix

Thanks so much to both of you - you were both right. It makes sense
(though quirky) that I have to activate the sheet first to select the
cell. If I don't select the cell correctly, the variabe
varActiveCellCol which grabs the column of the active (seleted) cell is
not filled properly.

Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top