VBA update graph range from variable rows

Question

I have a program which is supposed to read values from a file, generate graphs and perform calculations.

It imports a two column / 17000+ rows set of data and it needs to generate graphs from specific ranges. First column contains displacement data that keeps cycling from -5 to 5mm back and forth. I need to analyse the results from -2.5 to -4.5 and from 2.5 to 4.5mm in each cycle.

The problem is that for different sets of data the number of the row containing the extremes of the ranges can vary.

I'm using the MATCH function to get the row number of start and end of the range. For example:

=MATCH(2.5,Data!B200:B230,1)+199
=MATCH(4.5,Data!B260:B290,1)+260

In my case here it yields 216 and 272 respectively.

My question is: How can I use these values in the VBA code to automatic update my graphs with the correct set of data? Currently the range is fixed as below:

Sheets("Main").ChartObjects("Chart 12").Activate 'Cycle 1 - Evaluation(tension)
ActiveChart.SeriesCollection(1).XValues = "=Data!$B$216:$B$272"
ActiveChart.SeriesCollection(1).Values = "=Data!$C$216:$C$272"

Thanks/Regards

Robin Mackenzie · Accepted Answer · 2017-01-10 13:31:34Z

1

Dim two variables like i and j and then use them in string variables to create the values for XValues and Values property of your Series. For example:

Dim i As Long, j As Long
Dim ref1 As String, ref2 As String

i = 216 'update with your MATCH formula
j = 272 'update with your MATCH formula

' create the references as string variables
ref1 = "=Data!$B$" & i & ":$B$" & j
ref2 = "=Data!$C$" & i & ":$C$" & j

Sheets("Main").ChartObjects("Chart 12").Activate 'Cycle 1 - Evaluation(tension)
ActiveChart.SeriesCollection(1).XValues = ref1
ActiveChart.SeriesCollection(1).Values = ref2

answered Jan 10, 2017 at 13:31

Robin Mackenzie

19.4k7 gold badges42 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA update graph range from variable rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related