Worksheet Codename - Crazy Method Error!

[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!

 

[Bob Phillips]

Works fine. Sure your variables are correctly set?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"EphesiansSix" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

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)

 

[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!

 

[Bob Phillips]

More reasons for not selecting cells in code, never have to worry what is
active then.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"EphesiansSix" <[email protected]>
wrote in message
news:[email protected]...