Run-time error '91: Object variable or with block variable not set

[cboz]

in excel 2007

i want to be able to enter a single cell address then execute some code. here is what i have tried.

Dim MyCell As Range

MyCell = InputBox("Enter Beginning Cell i.e. A1")

Range("MyCell").Select 'this is where the error occurs

 

[SkipVought]

hi,

Dim MyCell As [b]String[/b]

MyCell = InputBox("Enter Beginning Cell i.e. A1")

Range(MyCell).Select   'no QUOTES

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cboz]

thanks that worked. i had tried String earlier but i still had the quotes.

 

[MakeItSo]

Hi cboz,

that is because you dim MyCell as a Range, i.e. as an Object, but try to assign a string value to it.
Then you put this MyCell as argument into Range(), and you do so enclosing it with quotes.
That however means that you try to select a Range that is named "MyCell" - and this Range does not exist.

1.) try this instead:

Dim MyCell as Range, inp as String
inp=InputBox("Enter Beginning Cell i.e. A1")
[b]Set MyCell=Range(inp)[/b]

2.) what are you trying to achieve? You should avoid SELECTing ranges within code when possible. Most things can be achieved without selecting the range.

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Oopsy, took too long it seems.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

I agree with MakeItSo item 2) also.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}