Range to work from a string.

Philosophaie · Sep 14th 2015

I have made a function that converts column numbers to column letters, alphacol.

I am having trouble reading the Range, rng1 after it has been set.

There is an error: "Object does not support this property or method"
on line: "Sheets("Graph").ChartObjects("Chart 1").Chart.SeriesCollection(k).XValues = Sheets("PlotData").rng1"

If I change rng1 to Range(rng1) I get this error: "Application-defined or object-defined error"
on this line: "Sheets("Graph").ChartObjects("Chart 1").Chart.SeriesCollection(k).XValues = Sheets("PlotData").Range(rng1)"

How do I get the Range to work with the function?

Code

nn=3
     Dim rng1 As Range, rng2 As Range
     For k=1 To 10
            string1 = alphacol(nn) & "4:" & alphacol(nn) & "25"
            Set rng1 = Range(string1)
            Sheets("Graph").ChartObjects("Chart 1").Chart.SeriesCollection(k).XValues = Sheets("PlotData").rng1
            nn = nn + 1
            string2 = alphacol(nn) & "4:" & alphacol(nn) & "25"
            Set rng2 = Range(string2)
            Sheets("Graph").ChartObjects("Chart 1").Chart.SeriesCollection(k).Values = Sheets("PlotData").rng2
            nn = nn + 1
     Next k

Display More

Smallman · Sep 14th 2015

Re: Range to work from a string.

The Char function turns numbers into letters. The letter "A" starts at 65. Would this help?

A =CHAR(65)

Just trap the column number and add 64 to get the real col Number.

Take care

Smallman

**rory** · Sep 14th 2015

Re: Range to work from a string.

Rng is a variable, not a property of a sheet. When you assign a range to it, the range is specific to a sheet, so you don't need to try and qualify the variable with a sheet too. Anyway, I'd just use the address string directly

Code

Sheets("Graph").ChartObjects("Chart 1").Chart.SeriesCollection(k).XValues = "=PlotData!" & String1

Range to work from a string.

Participate now!

Share