Obtaining cell reference from index function

[MH UK]

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.

 

[Bernie Deitrick]

How about just:

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

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

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.

 

[MH UK]

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.