PDA

View Full Version : Code not working when click button is moved



mdavid800
08-07-2011, 06:12 AM
Hi there

I have the VBA code which is located below to average every 12 cells in my worksheet titled “clear”.


Sub Average_2()
Dim i As Long
For i = 35 To 8663 Step 12
Range("BT" & Rows.Count).End(xlUp).Offset(1).Value = WorksheetFunction.Average(Range("BS" & i).Resize(12))
Next i
End Sub


However I would like to put a button on another sheet were the user inputs are this seems to be causing me problems, can anybody suggest how I would change the code above so that the click button will work as it does when the button is placed on the “clear” worksheet. I have tried


Sub Average_2()
Dim i As Long
For i = 35 To 8663 Step 12
Range("BT" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("Clear"). WorksheetFunction.Average(Range("BS" & i).Resize(12))
Next i
End Sub


To no avail

Thank you in advance

David

shrivallabha
08-07-2011, 07:06 AM
If you are trying to refer the data on other sheet than the active then you will have refer explicitly. Otherwise, VBA will refer to the same range on the activesheet. So you have to change this line:
Range("BT" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("Clear"). WorksheetFunction.Average(Range("BS" & i).Resize(12))

To:
Range("BT" & Rows.Count).End(xlUp).Offset(1).Value = WorksheetFunction.Average(Worksheets("Clear").Range("BS" & i).Resize(12))

vzachin
08-07-2011, 07:11 AM
see if this makes a difference
with sheets("clear")
Dim i As Long
For i = 35 To 8663 Step 12
.Range("BT" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("Clear"). WorksheetFunction.Average(.Range("BS" & i).Resize(12))
Next i
end with