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

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

"JohnM" wrote in message ups.com...
| 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
|

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

--
Don Guillett
SalesAid Software

"JohnM" wrote in message
ups.com...
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

JohnM wrote:
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 wrote:
JohnM wrote:
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

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


-semiopen

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"
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

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"
wrote in message
This is my first post.
-snip-

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

Niek Otten wrote:

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

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

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