Changing x-axis of a ChartObject in VBA

Question

If I want to change my x-axis to the data inside the range G5:G105 over Chart 2 then I put this into my VBA subroutine:

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).XValues = "='Q1'!$G$5:$G$105"

How do I make it such that I can have some arbitrary range INSTEAD OF the fixed $G$5:$G$105. I've tried to use

ActiveChart.SeriesCollection(1).XValues = "='Q1'!Range("G5").Resize(I, 1)"

where I is some Integer variable defined in a preceding part of the subroutine.

However it doesn't work.

Answer 1

I'm afraid your range "='Q1'!Range("G5").Resize(I, 1)" is an illegal mix of a strings and code. Try this range instead: Range("Q1!G5").Resize(i, 1).

Full example:

Option Explicit

Sub ChartTest()

    Dim i As Integer

    i = 2

    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(1).XValues = Range("Q1!G5").Resize(i, 1)

End Sub