leal72
07-06-2010, 09:42 AM
When I run this code the Resize line seems to goof up. It should be using the number from the same row, 2 columns back. So for C6 it should be using A6 but when I run the code it is trying to use XFC1 rather than A6.
the workbook has 2 chart sheets and the total number of Sheets will vary.
Sub Convert2Metric()
Dim AllRow As Long
Dim LastRow As Long
Dim DataSt As Long
Dim WkSht As Worksheet
Dim Cht2 As Chart
Dim RngMM As Range
Dim RngN As Range
Application.ScreenUpdating = False
ActiveWorkbook.Charts.Add
' Rename Chart2
ActiveSheet.Name = "SummaryChart(Metric)"
Set Cht2 = Charts("SummaryChart(Metric)")
For Each WkSht In ActiveWorkbook.Worksheets
' find last row on this version of Excel
AllRow = WkSht.Range("AA1").End(xlDown).Row
' find last row with data in column A
LastRow = WkSht.Range("A" & AllRow).End(xlUp).Row
DataSt = 6
WkSht.Range("C" & DataSt).Resize((LastRow + 1) - DataSt).FormulaR1C1 = "=RC[-2]*25.4"
WkSht.Range("D" & DataSt).Resize((LastRow + 1) - DataSt).FormulaR1C1 = "=RC[-2]*4.44822"
Set RngMM = WkSht.Range("C6", "C" & LastRow)
Set RngN = WkSht.Range("D6", "D" & LastRow)
' plot chart data for file
With Cht2.SeriesCollection.NewSeries
.Name = WkSht.Name
.Values = Sheets(WkSht.Name).Range(RngN.Address)
.XValues = Sheets(WkSht.Name).Range(RngMM.Address)
End With
Next
End Sub
the workbook has 2 chart sheets and the total number of Sheets will vary.
Sub Convert2Metric()
Dim AllRow As Long
Dim LastRow As Long
Dim DataSt As Long
Dim WkSht As Worksheet
Dim Cht2 As Chart
Dim RngMM As Range
Dim RngN As Range
Application.ScreenUpdating = False
ActiveWorkbook.Charts.Add
' Rename Chart2
ActiveSheet.Name = "SummaryChart(Metric)"
Set Cht2 = Charts("SummaryChart(Metric)")
For Each WkSht In ActiveWorkbook.Worksheets
' find last row on this version of Excel
AllRow = WkSht.Range("AA1").End(xlDown).Row
' find last row with data in column A
LastRow = WkSht.Range("A" & AllRow).End(xlUp).Row
DataSt = 6
WkSht.Range("C" & DataSt).Resize((LastRow + 1) - DataSt).FormulaR1C1 = "=RC[-2]*25.4"
WkSht.Range("D" & DataSt).Resize((LastRow + 1) - DataSt).FormulaR1C1 = "=RC[-2]*4.44822"
Set RngMM = WkSht.Range("C6", "C" & LastRow)
Set RngN = WkSht.Range("D6", "D" & LastRow)
' plot chart data for file
With Cht2.SeriesCollection.NewSeries
.Name = WkSht.Name
.Values = Sheets(WkSht.Name).Range(RngN.Address)
.XValues = Sheets(WkSht.Name).Range(RngMM.Address)
End With
Next
End Sub