# Thread: VBA-Count and Loop (Excel2000)

1. ## VBA-Count and Loop (Excel2000)

Hello, can you point me in the right direction in creating the following subroutine..
I am trying to get moving averages and moving standard deviations for each individual name in the list.
Sub proCalcAvgStDevTestIndex()
Dim x%,A as Range
i=1
Set A=Columns(1)
SetB=Columns(2)

For x=1 to 80
If A.Cells(i)=A.Cells(i+1) Then
Range("C2.C12").Formula="Average(B\$1:B2)"
Range("D2.D12").Formula="StDev(B\$1:B2)"
Range("D2.D12").NumberFormat="00.00"
End If
Next x

How do I get the subRoutine to goto A13 and run the averages for Baker, Casey,Delta?
This is my test program. The actual worksheet has over 65000 rows with each name between 5 and 40 rows.

Paul B

2. ## Re: VBA-Count and Loop (Excel2000)

Try this:

Sub proCalcAvgStDevTestIndex()
Dim r As Long
Dim m As Long
Dim s As Long
m = Range("A65536").End(xlUp).Row
Cells(1, 3).Value = "AVERAGE"
Cells(1, 4).Value = "STDEV"
s = 1
For r = 2 To m
If Not Cells(r, 1) = Cells(r - 1, 1) Then
s = r
Cells(r, 3).Value = "AVERAGE"
Cells(r, 4).Value = "STDEV"
Else
Cells(r, 3).Formula = "=AVERAGE(\$B\$" & s & ":\$B" & r & ")"
Cells(r, 4).Formula = "=STDEV(\$B\$" & s & ":\$B" & r & ")"
End If
Next r
End Sub

3. ## Re: VBA-Count and Loop (Excel2000)

Hi Hans, a couple of little tweaks and the code works great. Thanks again for your help.

Paul B

4. ## Re: VBA-Count and Loop (Excel2000)

Hello Hans, I applied the code to my application---65536 rows and 4 columns. I ran the macro for the four rows and the program crashed. So I ran the macro for the first column and It worked Ok. I saved it and went to the second column and that also worked ok. The problem is the third and fourth columns. If I run the macro for the third column the calculations are entered the the spreadsheet but the macro crashes--I cannot save the worksheet. The only thing I can do Is Ctrl-Alt-Delete. So the bottom line is the only way I can get this macro to work is the StepInto F8 Key. I held that key down and came back four hours later. I saved the worksheet and did the same for column four. I will want to run this macro for other databases but would like it to work properly. Do you have any idea why this macro Crashes?
Paul B

5. ## Re: VBA-Count and Loop (Excel2000)

P.S. When In ran column 3 I used the comment block for the other three columns so that only 1 column was active.

PaulB

6. ## Re: VBA-Count and Loop (Excel2000)

<P ID="edit" class=small>(Edited by JohnBF on 19-Sep-07 11:55. Code corrected)</P>Can you describe the error message, line of code where it happens, and cell where it happens? (Always include the first two items when you need help with code that errors out.) I made the following small modifications to Hans code, and it ran fine against your original workbook (though you may want to test it to ensure it does what you intend):

Sub proCalcAvgStDevTestIndex()
Dim r As Long
Dim m As Long
Dim s As Long
m = Range("A65536").End(xlUp).Row
Cells(1, 3).Value = "AVERAGE"
Cells(1, 4).Value = "STDEV"
s = 1
For r = 2 To m
If Not Cells(r, 1).Value = Cells(r - 1, 1).Value Then
s = r
Cells(r, 3).Value = "AVERAGE"
Cells(r, 4).Value = "STDEV"
Else
Cells(r, 3).FormulaR1C1 = "=AVERAGE(R" & s & "C[-1]:RC[-1])"
Cells(r, 4).FormulaR1C1 = "=STDEV(R" & s & "C[-2]:RC[-2])"
End If
Next r
End Sub

7. ## Re: VBA-Count and Loop (Excel2000)

Hi JohnBF, I don't get an error message. The macro just hangs up.
Taking this one step further I have to resort the worksheet but the cells in the range P3:S65535 are functions so I wrote the following code to convert each cell to a "Value"

Sub ConvertAverages()

Range("P3:S65535").Copy
Selection.PasteSpecial Paste:=xlValues
End Sub

I get a runtime error 438 "Object doesn't support this property or method

8. ## Re: VBA-Count and Loop (Excel2000)

Is the worksheet you are having problems with exactly the same layout as you originally posted? The code runs fine on the test worksheet, provided the worksheet with the data is the active sheet when the code is run. To convert to values, try this:

Sub proCalcAvgStDevTestIndex2()
' preselect sheet with data
Dim r As Long
Dim m As Long
Dim s As Long

With ActiveSheet
m = .Range("A65536").End(xlUp).Row
.Cells(1, 3).Value = "AVERAGE"
.Cells(1, 4).Value = "STDEV"
s = 1
For r = 2 To m
If Not .Cells(r, 1).Value = .Cells(r - 1, 1).Value Then
s = r
.Cells(r, 3).Value = "AVERAGE"
.Cells(r, 4).Value = "STDEV"
Else
.Cells(r, 3).Value = Application.WorksheetFunction.Average(.Range(.Cell s(s, 2), .Cells(r, 2)))
.Cells(r, 4).Value = Application.WorksheetFunction.StDev(.Range(.Cells( s, 2), .Cells(r, 2)))
End If
Next r
Intersect(.UsedRange, Columns("C")).NumberFormat = "#,##0.00"
End With
End Sub

9. ## Re: VBA-Count and Loop (Excel2000)

The following code works:

SubConvertAverages()
Range("P3.S65535").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
End Sub

All 65535 cells were converted from math functions to values in each column.

but I still can't figure why the application hangs up with no error messages. It took over 8 hours to do the computations with the F8 StepInto.

10. ## Re: VBA-Count and Loop (Excel2000)

There are no problems when we run the code against the workbook you attached, so perhaps you could post a more representative workbook.

11. ## Re: VBA-Count and Loop (Excel2000)

Every bit of code Hans and I posted also works. Attached is my working model, perhaps you are working with something we don't see.

12. ## Re: VBA-Count and Loop (Excel2000)

Attatched is the original file

13. ## Re: VBA-Count and Loop (Excel2000)

Enclosed is a partial worksheet. The original is too large.

14. ## Re: VBA-Count and Loop (Excel2000)

The structure of this worksheet is quite different from the one you originally posted. Of what exactly do you want to calculate averages and standard deviations? Please try to provide complete and accurate information.

15. ## Re: VBA-Count and Loop (Excel2000)

What I did was:
compute the moving averages from columns I and J and posted the results to columns P,Q,R,S
Column P(Avg_Art) is the moving average and column Q (StDev) is the moving standard deviation.
Column R(Avg_Odds) is the moving average and column S (SD_Odds) is the moving standard deviation.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•