Hi,

Is there a way that I can define a variable for the actual cell name?

I'm trying to automate a process where I'm charting some data in a sheet and in the vba code, I want to select, for example, cells b12:c24 for a scatterplot.

what I'd like to do is something like this:

find top cell corresponding to the range of interest (i.e. cell a11 = Mike)... so

rough code:

dim xblah1 as string
dim xblah2 as string
dim yblah1 as string
dim yblah2 as string
dim nm as string

nm = Mike

sheets("Data").select

cells.find(what:=" & nm & "....).Activate ' this activates cell a11 which has the name Mike in it.

activecell.offset(1,1).select ' this will be cell b12 and the first xValue for a plot
xblah1 = activecell.name ' now blah should be set to "b12" as its value
selection.end(xlDown).select ' this will be b24 and the last xvalue
xblah2 = activecell.name ' xblah2 is set to "b24" as it's value
activecell.offset(0,1).select ' this will be cell c24 and the yValues for a plot
yblah2 = activecell.name ' yblah2 is set to "c24" or last yValue for plot
selection.end(xlUp).Select ' this will be cell c12 and the first yValue
yblah1 = activecell.name ' yblah1 is set to "c12" or first yValue

the code doesn't work for activecell.name but because the a11 "Mike" variable will be used to find the starting point in the sheet (there are many more locations with "john", "fred", "joe", etc. where offsetting by 1 row and 1 column will get me to my start point for the data to be plotted...

thus for plotting the data, I want to do the following:

ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).XValues = "=" & xblah1 & ":" & xblah2 & "
ActiveChart.SeriesCollection(5).Values = "=" & yblah1 & ":" & yblah2 & "
ActiveChart.SeriesCollection(5).Name = "=" & nm & "

and so on...

Any help would be greatly appreciated.

Best,

Mike