PDA

View Full Version : [SOLVED] Adding continuous Series Collections with loop



mattreingold
06-19-2018, 01:08 PM
Hello everyone, I am having quite the issue today. I have a workbook in which the first sheet is a data table, the second is a chart, and the third is a template for which this code copies per sample to analyze. Everything is working quite nice (I know some of this code is literally atrocious).

Below is a section of my code. The chart begins blank, however formatted.

I want with each sample run (each run through the 'm' loop) to add a data series. What is interesting is that the legend updates with each run, populating the legend with a new line and new name, however, for some reason the previous data series x and y values get overwritten with the most recent loop values.

If interested, I attached the full workbook, however I tried to upload a few files to play around with (they are from a materials testing machine sourced .mtwData - my code opens it without displaying alerts, forcing it to open in the read only format, as it should) but the cite didnt allow me, so I uploaded a couple here:
http://www.mediafire.com/file/ldhaaq39tdkv81f/030011B-08-1A-2018-06-18-11-25-58_1.mtwData.
http://www.mediafire.com/file/3jy4k4g8dx1cq95/030011B-10_2A-2018-06-18-11-20-01_1.mtwData

I also attached a picture to show how it is overwriting each data series.

I would very much appreciate some help with this, I feel as if I made a simple mistake, however I cannot find where....


Sub RunReport()


' Establish All Workbook and Worksheet Variables


Dim WBT As Workbook ' This Workbook
Dim WBD As Workbook ' Data Workbook
Dim WSD As Worksheet ' Data Sheet from data workbook
Dim WPN As Worksheet ' Report, in WBT
Dim NewSheet As Worksheet ' Sheet being created


Set WBT = Workbooks("RunCompressionTestingReport.xlsm")


Dim specimenName As String, specimenSheetNameMTW As String, specimenNameTesting() As String, specimenNameNoFileType() As String


Dim xlFileFirst As Variant, xlFileSecond As Variant
Dim fso As New FileSystemObject


Counter = 0
numberOfFiles = InputBox("Enter Number of Samples to Analyze (1-30)")


'Execute for every sample
For m = 1 To numberOfFiles


If checkRow = 3 Then
Set NewSheet = WBT.Sheets(WBT.Sheets.Count)
NewSheet.Name = specimenName
Else
WBT.Sheets(Counter + 2).Copy WBT.Sheets(WBT.Sheets.Count)
Set NewSheet = WBT.Sheets(WBT.Sheets.Count)
NewSheet.Range("C36:D15000").ClearContents
NewSheet.Name = specimenName
FixPreviousName() = Split(WBT.Sheets(WBT.Sheets.Count - 1).Name, " (2)")
FixedName = FixPreviousName(0)
WBT.Sheets(WBT.Sheets.Count - 1).Name = FixedName
End If


'''''''''''''


Set WBD = Workbooks(fileNameWBD)


' Establish and Copy Data Sheet from .mtwData Workbook to RunReport Workbook
Set WSD = WBD.Worksheets(specimenSheetNameMTW)


' Sets Sheet 1 of Report Workbook to WPN variable
Set WPN = WBT.Worksheets("Sheet1")

' Create arrays and find starting points for load/time etc.
Dim loadArr(), areaArr(), stressArr(), strainArr(), lengthArr(), changeLengthArr(), percPosArr(), timeArr()
Dim firstRow As Long, lastRow As Long, columnIdx As Long


' Establish begining and end of data columns in .mtwData workbook
firstRow = 3 ' Row after header in .mtwData file
lastRow = WSD.Cells(Rows.Count, "A").End(xlUp).Row



'''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''


' Format by closing Data workbooks, deleting temporary data sheets and setting focus to 'Report' tab
Application.DisplayAlerts = False
Workbooks(fileNameWBD).Close

With Chart1
.ChartType = xlXYScatterLinesNoMarkers
.SeriesCollection.Add _
Source:=Sheets(Counter + 3).Range("C36:C15000")
.SeriesCollection(Counter + 1).XValues = _
Sheets(Counter + 3).Range("D36:D15000")
.SeriesCollection(Counter + 1).Name = specimenName
End With

' Reactivate alerts and set workbook focus to the first sheet
Application.DisplayAlerts = True
Counter = Counter + 1

Next m


' Name Chart Sheet to 'Data Plots' And Activate Sheet 1
Chart1.Name = "Data Plots"
Worksheets("Sheet1").Activate


End Sub


NOTE: If you do try to run the code, a prompt should ask you how many specimens to run, enter 2 to see how it overwrites.



2245422453

mattreingold
06-19-2018, 01:12 PM
Also, the macro of interest is 'RunReport' not 'AddSample' if you do try to run it.

mattreingold
06-20-2018, 09:04 AM
Issue diagnosed, needed to use arrays to hold the data ranges (somehow they were getting overwritten).

Code that worked for me:

Dim YValueArray(), XValueArray()
YValueArray = WBT.Sheets(m + 2).Range("C36:C15000").Value
XValueArray = WBT.Sheets(m + 2).Range("D36:D15000").Value
With Chart1
.ChartType = xlXYScatterSmoothNoMarkers
.SeriesCollection.NewSeries
.SeriesCollection(m).Name = specimenName
.SeriesCollection(m).XValues = XValueArray
.SeriesCollection(m).Values = YValueArray
End With


Reference: https://stackoverflow.com/questions/24757100/vba-multiple-series-to-a-chart (not my post)