Efficient Copying Problem

Posted by Yasmin Thomas on November 26, 2001 11:40 AM

Hello,

I have a very big worksheet with about 2000 lines. In every 20th line is a value that i want to work with. I have to calculate the deviation of these values, but because i cant give excel the formula =dev(b10:b2000) (there are other values between the interesting ones) it would be =dev(b10;b30;b50;b70...) with 100 arguments and excel cant take this.

Is there an easy way to gather the values of each 20th cell in a tight new area on the worksheet?

Tank you in advance!
Yasmin

Posted by John on November 26, 2001 2:13 PM

This will give you every 20th row in Column A, just copy down as needed

=OFFSET($A$20,ROW()*20-20,0)

This will give you every 20th row in an array formula (enter using Control+Shift+Enter):

=OFFSET($A$20,ROW(A1:A84)*20-20,0)

Posted by Bariloche on November 26, 2001 9:13 PM

Yasmin,

The following code will take every 20th value in column B, starting with cell B10, and put it on a new sheet starting in column A1. The one assumption I've made is that the name of your data sheet is "data".

Sub StepByTwenty()

Dim i As Double
Dim k As Double

Application.ScreenUpdating = False

Sheets.Add
ActiveSheet.Name = "CalcDeviation"
k = 1

For i = 10 To 2000 Step 20
Sheets("CalcDeviation").Cells(k, 1).Value = Sheets("data").Cells(i, 2).Value
k = k + 1
Next i

Application.ScreenUpdating = True

End Sub

Hopefully this helps.

enjoy