Worksheet Codename - Crazy Error!

**EphesiansSix** · 06-23-2006, 12:48 PM

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 · 06-23-2006, 01:10 PM

Works fine. Sure your variables are correctly set?

--
HTH

Bob Phillips

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

"EphesiansSix" <EphesiansSix.29v6hb_1151081437.902@excelforum-nospam.com>
wrote in message
news:EphesiansSix.29v6hb_1151081437.902@excelforum-nospam.com...
>
> 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!
>
>
> --
> EphesiansSix
> ------------------------------------------------------------------------
> EphesiansSix's Profile:
http://www.excelforum.com/member.php...o&userid=35721
> View this thread: http://www.excelforum.com/showthread...hreadid=555046
>

Jim Thomlinson · 06-23-2006, 01:35 PM

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)
--
HTH...

Jim Thomlinson

"EphesiansSix" wrote:

>
> 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!
>
>
> --
> EphesiansSix
> ------------------------------------------------------------------------
> EphesiansSix's Profile: http://www.excelforum.com/member.php...o&userid=35721
> View this thread: http://www.excelforum.com/showthread...hreadid=555046
>
>

**EphesiansSix** · 06-23-2006, 05:22 PM

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 · 06-23-2006, 08:55 PM

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" <EphesiansSix.29vj7n_1151097902.0449@excelforum-nospam.com>
wrote in message
news:EphesiansSix.29vj7n_1151097902.0449@excelforum-nospam.com...
>
> 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!
>
>
> --
> EphesiansSix
> ------------------------------------------------------------------------
> EphesiansSix's Profile:
http://www.excelforum.com/member.php...o&userid=35721
> View this thread: http://www.excelforum.com/showthread...hreadid=555046
>