Column range input as a number, so it can be changed

user8I4l1 0
Viewed 37

I have 25 spreadsheets that have pie charts. I need to loop through the spreadsheets and change where the pie charts get the information from. But I can't get the code to work. I am coming from this:

Sub ChangePieValues()

Dim sheetno As Integer

sheetno = 14

Sheets(sheetno).Activate
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).XValues = "=Worksheets(2)$BA$3:$BA$6"
    ActiveChart.FullSeriesCollection(1).Values = "=Worksheets(2)$BB$3:$BB$6"

End Sub

I haven't added the loop yet as this is the easy part.

My problem is in the following line:

ActiveChart.FullSeriesCollection(1).XValues = "=Worksheets(2)$BA$3:$BA$6"

I thought I could write something like:

ActiveChart.FullSeriesCollection(1).XValues = "=Worksheets(2).((Range(55,3)):(Range(55,6))"

I am trying to change the Range of the XValues to a number so I can change that number as I go through the different spreadsheets.

How can I change the way I input the column index, so it can be changed by loops.

vba excel
2 Answers

Based on your question and example code - where you're providing a String to Excel that refers to the range - I would suggest the following solution:

ActiveChart.FullSeriesCollection(1).XValues = _
    "'" & Worksheets(2).Name & "'!" & Cells(3, 53).Address & ":" & Cells(6, 53).Address

However, a better way would be to provide the property with a Range object (instead of a string that refers to the range object). This should also work, like so:

ActiveChart.FullSeriesCollection(1).XValues = _
   Worksheets(2).Range(Worksheets(2).Cells(3, 53), Worksheets(2).Cells(6, 53))

To make that tidier to read though, I'd use:

With Worksheets(2)
    ActiveChart.FullSeriesCollection(1).XValues = _
        .Range(.Cells(3, 53), .Cells(6, 53))
End With

You were very close to achieving this in your example attempts, but as you were wrapping the location in double-quotes - Excel took them as if a String referencing a Range.

CLR0

VBA ChartObjects: Change Source Data (SetSourceData)

  • In the workbook containing this code (ThisWorkbook), this will change the source data in the charts named Chart 1 of 25 consecutive worksheets, starting with the 14th worksheet, to the values in 25 consecutive columns in the 2nd worksheet, starting with column BA3:BA6.
Sub ChangeSourceData()
    
    ' Define constants.
    
    ' Source
    Const sId As Variant = 2 ' name or index ('As Variant')
    Const sFirstColumnAddress As String = "BA3:BA6"
    ' The number of source columns ('scCount') is equal
    ' to the number of destination worksheets.
    Const scCount As Long = 25
    ' Destination
    Const dFirstIndex As Long = 14 ' First Destination Worksheet
    Const dChartName As String = "Chart 1" ' Each Worksheet's Chart Name
    
    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the source range ('srg').
    Dim sws As Worksheet: Set sws = wb.Worksheets(sId)
    Dim srg As Range: Set srg = sws.Range(sFirstColumnAddress).Resize(, scCount)
    
    ' Declare additional variables.
    
    ' Source
    Dim sc As Long
    ' Destination
    Dim dws As Worksheet
    Dim dch As Chart
    Dim dcho As ChartObject
    Dim dIndex As Long
    
    ' Loop and apply.
    
    ' Loop through the columns ('sc') of the source range, indirectly...
    For sc = 1 To scCount
        ' ... looping through the destination worksheets ('dIndex').
        dIndex = dFirstIndex + sc - 1
        ' Reference the destination worksheet ('dws') by index,...
        Set dws = wb.Worksheets(dIndex)
        ' ... to reference its 'ChartObject' ('dcho') by name ...
        Set dcho = dws.ChartObjects(dChartName)
        ' ... to reference its 'Chart' ('dch') avoiding 'Activate'.
        Set dch = dcho.Chart
        ' Set the new (one-column) data range for the chart.
        dch.SetSourceData srg.Columns(sc)
        'dch.FullSeriesCollection(1).ApplyDataLabels
    Next sc

    ' Inform.
    MsgBox "Source data changed.", vbInformation

End Sub
VBasic20080
Related