Obtaining cell reference from index function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MH UK - 21 Feb 2006 14:36 GMT

I am attemtping to build custom screen tips for a scatter graph and already
been given some help. However I am stuck again. The following code extract

myX = WorksheetFunction.Index _
(.SeriesCollection(Arg1).XValues, Arg2)
' Extract y value from array of y values
myY = WorksheetFunction.Index _
(.SeriesCollection(Arg1).Values, Arg2)

populates two variables (myX and myY) which correspond to the x and y
values. What I am having trouble with is obtaining the cell reference to
either offset from to obtain the data I wish to display in my custom screen
tip.
If for example, myX extracts a value from cell B3, i need to access B5 for
my custom data.
I must be getting close.

Thanks to anyone who helps me in my (hopefully) final hurdle.

Reply to this Message

Bernie Deitrick - 21 Feb 2006 15:37 GMT

How about just:

myX = .SeriesCollection(Arg1).XValues(Arg2+2).Value

HTH,
Bernie
MS Excel MVP

>I am attemtping to build custom screen tips for a scatter graph and already
> been given some help. However I am stuck again. The following code extract
[quoted text clipped - 14 lines]
>
> Thanks to anyone who helps me in my (hopefully) final hurdle.

Reply to this Message

Tom Ogilvy - 21 Feb 2006 15:41 GMT

You are extracting a value that is derived from the value in B3. There is
nothing in your code that makes reference to B3.

Perhaps take a look at this page by John Walkenbach

http://www.j-walk.com/ss/excel/tips/tip83.htm
A Class Module to Manipulate a Chart SERIES

The code he offers shows how to get the range reference and then you can
offset from there.

Signature

Regards,
Tom Ogilvy

> I am attemtping to build custom screen tips for a scatter graph and already
> been given some help. However I am stuck again. The following code extract
[quoted text clipped - 14 lines]
>
> Thanks to anyone who helps me in my (hopefully) final hurdle.

Reply to this Message

MH UK - 22 Feb 2006 13:58 GMT

Thanks for your help, I have worked a solution though not quite as either
response suggested.
Basically I created a new variable equal to the X Value & Y Value and
performed a look-up on a column in my data-set where the x and y values are
merged. I could not quite get to be able to offset from the series range to
my required data. My solution my not be the neatest but works. It will mean
changes to my data-set but I can live with that.
Though I am of course happy to receive a better solution.

> You are extracting a value that is derived from the value in B3. There is
> nothing in your code that makes reference to B3.
[quoted text clipped - 27 lines]
> >
> > Thanks to anyone who helps me in my (hopefully) final hurdle.

Reply to this Message