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
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