-
trouble with .Resize
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.
[vba]
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
[/vba]
-
I'm not sure why that is. This seems to work.
Code:
WkSht.Range("C" & DataSt).Resize((LastRow + 1) - DataSt).Formula = _
"=" & WkSht.Range("C" & DataSt).Offset(, -2).Address(False, False) & "*25.4"
-
You code worked OK for me.
BTW, to get the last cell, you can simplify with
[VBA]
LastRow = WkSht.Range("A" & Rows.Count).End(xlUp).Row
[/VBA]
-
GTO - thanks that seems to be working great, thank you
mdmackillop - thank you for the tip also, not sure why but when I run it with that line I get the Run-time error '1004.
Method 'Rows' or object' _Global' failed
-
Try
[vba]
LastRow = WkSht.Range("A" & WkSht.Rows.Count).End(xlUp).Row
[/vba]
-
