none
How to increase the column in source data everytime I run the vba RRS feed

  • Question

  • Hi,

    How to run a vba and make the formula which changes $F$7 to $G$7, then $G$7 to $H$7 for the second time i run the VBA, and so on? ie Adding one column when running vba once.

    From

    ActiveChart.SeriesCollection(2).XValues = "='source data 2'!$B$7:$F$7"

    To

    ActiveChart.SeriesCollection(2).XValues = "='source data 2'!$B$7:$G$7"

     

    Thanks!

    Saturday, October 15, 2011 4:55 PM

All replies

  • That sort of code is risky, because if the VBA code crashes or is stopped halfway thru you get a mess!

     

    I would have column titles and search the titles row for the required column. I would have the code re-build the formulae each time using teh column number found above.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Saturday, October 15, 2011 8:08 PM
  • Hi Rod,

    Thanks for your reply. I am new to VBA. I know it could've be done by searching number of Cells by the xltoleft formula, and then using the R1C1 to loop . But I have no idea how to write it.

    Sunday, October 16, 2011 5:21 AM
  • This is my code, I seriously need to adjust it but it doesn't seem to work.

    Dim c as Integer
    Dim s1 as range
    Sheets("Charts").Select

    With Sheets("Data")
    c = .Cells(9, .Columns.Count).End(xlToLeft).Column
    s1 = Range(Cells(11, 2), Cells(11, c))

    ActiveChart.SeriesCollection(1).Values = "Data! & s1"
       
    End With

    Sunday, October 16, 2011 11:17 AM