Need help converting Range value in Excel

  • Thread starter Thread starter BCS
  • Start date Start date
B

BCS

I have a macro that takes select information from an Excel worksheet and
then creates a Chart. A problem I am running into is that one of the
ActiveChart objects requires that the range be in a specific format. For
example, early in my macro I have an array variable for the range "B8:J8".
Is there a command I can use within my macro to change this to "R8C2:R8C10"?

Thanks,

Barry
 
You can use the Range object's Address property to get an R1C1 style address
for a range. Here's an example:

MsgBox Range("B8:J8").Address(ReferenceStyle:=xlR1C1)
 
Here's one way. It puts each cell reference into A1 as a formula, then reads
A1's FormulaR1C1 property.
Trim the extra ='s, add a colon, and you have a string containing the R1C1
version of the range.
There must be a simpler way, and I don't think this one will work for
relative ranges.

Sub test()
Dim temp As String
Dim r As Range
Dim strResult As String
Set r = [A1]
temp = r.Formula 'preserve orig data in A1
r.Formula = "=" & [B8].Address
strResult = Right(r.FormulaR1C1, Len(r.FormulaR1C1) - 1)
r.Formula = "=" & [J8].Address
strResult = strResult & ":" & _
Right(r.FormulaR1C1, Len(r.FormulaR1C1) - 1)
MsgBox strResult
r.Formula = temp 'restore orig data to A1
End Sub

HTH
 
Back
Top