Obtaining cell reference from index function

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.

How about just:

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

HTH,
Bernie
MS Excel MVP


"MH UK" <MHUK@discussions.microsoft.com> wrote in message
news:A59B09AF-9D12-46EC-A15D-740B8F710046@microsoft.com...
>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.

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.

--
Regards,
Tom Ogilvy


"MH UK" <MHUK@discussions.microsoft.com> wrote in message
news:A59B09AF-9D12-46EC-A15D-740B8F710046@microsoft.com...
> 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.

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.

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "MH UK" <MHUK@discussions.microsoft.com> wrote in message
> news:A59B09AF-9D12-46EC-A15D-740B8F710046@microsoft.com...
> > 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.
>
>
>