Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA-Count and Loop (Excel2000)

    Attatched is the original file

  13. #13
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA-Count and Loop (Excel2000)

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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #15
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 LastLast

Posting Permissions

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