PDA

View Full Version : trouble with .Resize



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

GTO
07-07-2010, 12:09 AM
I'm not sure why that is. This seems to work.


WkSht.Range("C" & DataSt).Resize((LastRow + 1) - DataSt).Formula = _
"=" & WkSht.Range("C" & DataSt).Offset(, -2).Address(False, False) & "*25.4"

mdmackillop
07-07-2010, 08:20 AM
You code worked OK for me.

BTW, to get the last cell, you can simplify with

LastRow = WkSht.Range("A" & Rows.Count).End(xlUp).Row

leal72
07-08-2010, 10:06 AM
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

Bob Phillips
07-08-2010, 10:37 AM
Try



LastRow = WkSht.Range("A" & WkSht.Rows.Count).End(xlUp).Row

leal72
07-08-2010, 02:12 PM
Thank you