changing cell address to row/column address

Is there a simple way to change a cell address, i.e. a2 to a row/column value, i.e. r2c1 using vba code?

I'm trying to automate a plot scheme and the input for the xvalues and yvalues is based on row/column nomenclature versus address nomenclature..

Example for grabbing b1:b10, the code must look like this (I think):

ActiveChart.SeriesCollection(5).XValue = "=Sheet1!r1c2:r10c2"

whereas, I would love to be able to put this:

ActiveChart.SeriesCollection(5).XValue = "=Sheet1!$b$1:$b$10" - THIS FAILS

Best regards,

Mike

Hi Pike,


I actually got it to work. For some reason if I put the cell index - i.e. $b$1:$b$12 as the SeriesCollection.XValue, it wouldn't work. So, I made a fix that is somewhat tedious, but it works.

xr1 = ActiveCell.Row
xc1 = ActiveCell.Column
x1 = "r" & xr1 & "c" & xc1
Selection.End(xlDown).Select
xr2 = ActiveCell.Row
xc2 = ActiveCell.Column
x2 = "r" & xr2 & "c" & xc2
ActiveCell.Offset(0, 1).Select
yr2 = ActiveCell.Row
yc2 = ActiveCell.Column
y2 = "r" & yr2 & "c" & yc2
Selection.End(xlUp).Select
yr1 = ActiveCell.Row
yc1 = ActiveCell.Column
y1 = "r" & yr1 & "c" & yc1

Sheets("Graphical Results").Select
With ActiveChart.SeriesCollection.NewSeries
.Name = ln
.Values = "=" & pr & y1 & ":" & y2
.XValues = "=" & pr & x1 & ":" & x2
End With
ActiveChart.SeriesCollection(5).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = True
.MarkerSize = 5
.Shadow = False
End With

pr is the sheet where the data is located.

Thanks,

Mike

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I apologize for this... I am an new member. I've reposted in the correct format.

Best,

Mike


I actually got it to work. For some reason if I put the cell index - i.e. $b$1:$b$12 as the SeriesCollection.XValue, it wouldn't work. So, I made a fix that is somewhat tedious, but it works.

Please Login or Register  to view this content.

pr is the sheet where the data is located.

Thanks,

Mike
Edit/Delete Message

Originally Posted by arthurbr

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here