Fundamental problem with Indirect function

[JohnM]

This is my first post.

I don't seem to have the indirect function included in my library of
objects. The following code gets me an error "Object doesn't support
this porperty or method".

Sub Hithere()
x = WorksheetFunction.INDIRECT(C2)
MsgBox x
End Sub

If I change the second line to x = INDIRECT(C2), I get "Compiler error:
Sub or Function not defined."

I can't find INDIRECT anywhere in the Excel Visual Basic help. And it
is not listed among the "List of Worksheet Functions Available to
Visual Basic" in my Visual Basic Help(though I can use it fine within
Excel by itself). But this seems to be a commonly used function as
judged by my perusal of the messages in this group. Am I doing
something wrong? Is something wrong with my excel or Visual Basic?
Please help.

I'm using Excel 2003 and with Microsoft Visual Basic 6.3.

Thank you,
JohnM

 

[Niek Otten]

Hi John,

No. it's not there, probably because you don't need it in VBA.

x = range(range("c2")).value

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| This is my first post.
|
| I don't seem to have the indirect function included in my library of
| objects. The following code gets me an error "Object doesn't support
| this porperty or method".
|
| Sub Hithere()
| x = WorksheetFunction.INDIRECT(C2)
| MsgBox x
| End Sub
|
| If I change the second line to x = INDIRECT(C2), I get "Compiler error:
| Sub or Function not defined."
|
| I can't find INDIRECT anywhere in the Excel Visual Basic help. And it
| is not listed among the "List of Worksheet Functions Available to
| Visual Basic" in my Visual Basic Help(though I can use it fine within
| Excel by itself). But this seems to be a commonly used function as
| judged by my perusal of the messages in this group. Am I doing
| something wrong? Is something wrong with my excel or Visual Basic?
| Please help.
|
| I'm using Excel 2003 and with Microsoft Visual Basic 6.3.
|
| Thank you,
| JohnM
|

 

[Don Guillett]

why not
x=range("c2")
or
x=cells(2,3)
x=cells(2,"c")

 

[semiopen]

This is my first post.

I don't seem to have the indirect function included in my library of
objects. The following code gets me an error "Object doesn't support
this porperty or method".

Sub Hithere()
x = WorksheetFunction.INDIRECT(C2)
MsgBox x
End Sub

If I change the second line to x = INDIRECT(C2), I get "Compiler error:
Sub or Function not defined."

I can't find INDIRECT anywhere in the Excel Visual Basic help. And it
is not listed among the "List of Worksheet Functions Available to
Visual Basic" in my Visual Basic Help(though I can use it fine within
Excel by itself). But this seems to be a commonly used function as
judged by my perusal of the messages in this group. Am I doing
something wrong? Is something wrong with my excel or Visual Basic?
Please help.

I'm using Excel 2003 and with Microsoft Visual Basic 6.3.

Thank you,
JohnM

Indirect does not appear in the list of worksheet functions available
to VBA. Presumably, in your example above "C2" is the name of a cell
which contans a string pointing to another cell. To access *that*
cell's value you could use two applications of the Range function:

Sub Hithere()
Dim x as Variant 'You should *always* declare!
x = Range(Range("C2").Value).Value
MsgBox x
End Sub

Hope that helps

-semiopen

 

[semiopen]

Indirect does not appear in the list of worksheet functions available
to VBA. Presumably, in your example above "C2" is the name of a cell
which contans a string pointing to another cell. To access *that*
cell's value you could use two applications of the Range function:

Sub Hithere()
Dim x as Variant 'You should *always* declare!
x = Range(Range("C2").Value).Value
MsgBox x
End Sub

Hope that helps

A quick follow up - your remark that

If I change the second line to x = INDIRECT(C2), I get "Compiler error:
Sub or Function not defined."

made me think - well then, why not define it?

Function Indirect(CellAddress as String) as Variant
Indirect = Range(Range(CellAddress).Value).Value
End Function

Then x = Indirect("C2") would work and be a bit more readable if you
were doing that a lot in your code.

-semiopen

 

[Jim Cone]

I like your function approach.
For what it is worth, the following also works...

Application.Evaluate("INDIRECT(C2)")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"semiopen" <[email protected]>
wrote in message
A quick follow up - your remark that

If I change the second line to x = INDIRECT(C2), I get "Compiler error:
Sub or Function not defined."

made me think - well then, why not define it?

Function Indirect(CellAddress as String) as Variant
Indirect = Range(Range(CellAddress).Value).Value
End Function

Then x = Indirect("C2") would work and be a bit more readable if you
were doing that a lot in your code.
-semiopen

 

[Jim Cone]

This is usually helpful to first timers...
http://www.cpearson.com/excel/newposte.htm
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"JohnM" <[email protected]>
wrote in message
This is my first post.
-snip-

 

[JohnM]

Niek

My real problem is that I am trying to modify the reference cell's
color formatting. So for example on Sheet 2 I have a cell "A2" with
the formula "=Sheet1!A2". Likewise Sheet2--"A3" could have a formula
"=Sheet1!B9" and so on. So, depending on which Sheet2 cell in the A
column is modified I am trying to figure out how to manipulate the cell
in Sheet1 that it corresponds to depending on the formula within that
cell. I expect that it should be as simple as something like this if
only I knew what to make bRange equal to:

Given a couple range aRange and bRange--
bRange.Interior.ColorIndex = aRange.Interior.ColorIndex

I realize I need to be able to express the reference cell in terms of
the original cell in Sheet2, but don't know how to do this.

Any ideas?

Thanks again,
JohnM

 

[JohnM]

I've solved my main issue now. Thanks for everyone's help and
comments. I was able to use ".Formula", and the substitute function to
do it. See below for my solution:

bRange = aRange.Formula
bRange = WorksheetFunction.Substitute(bRange, "=", "")
Range(Addr1).Interior.ColorIndex = cRange.Interior.ColorIndex

Best regards,
John