Data Cycles Macro

[RyanITSC]

Hello all,

I am trying to create a macro that would cycle thru some data and give me a count of how many cycles are in this data set.

Here is some psuedo code that I created but don't know where to start after this.

Loop through data in column B until cell >= .0008

Then

MyRange = current cell Col B+ next 169 cells of Col B

Average of Range
*****
Oh and start the loop at row 1473 where column A = 2360, end loop where column A = 17010

I also need to count the number of times the value exceeds .0006 in the range.

If anyone could either create this or help me out on this I would greatly appreciate it.


Thank you!
Ryan

 

[mrmmickle1]

Maybe try something like this:

[COLOR=#0000ff]Option Explicit
[/COLOR]
[COLOR=#0000ff]Sub [/COLOR]Test()[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] Cll [COLOR=#0000ff]As [/COLOR]Range
[COLOR=#0000ff]    Dim[/COLOR] LastRow [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] AvgRng [COLOR=#0000ff]As Double[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] Start [COLOR=#0000ff]As Long[/COLOR]

    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    
[COLOR=#0000ff]    For Each[/COLOR] Cll [COLOR=#0000ff]In [/COLOR]Range("B2:B" & LastRow)[COLOR=#008000] 'If you want to start at 1473 Change To Range("B1473:B" & LastRow)[/COLOR]
    [COLOR=#0000ff]   If[/COLOR] Cll.Value >= 0.0008 [COLOR=#0000ff]Then[/COLOR]
            Start = Cll.Row
            AvgRng = Application.WorksheetFunction.Average(Range(Cells(Start, "B"), Cells(Start + 169, "B"))) [COLOR=#008000]'Or Use Range ("B" & Start & ":B" & Start + 169)[/COLOR]
[COLOR=#0000ff]            Exit For[/COLOR]
[COLOR=#0000ff]       End If[/COLOR]
  [COLOR=#0000ff]  Next[/COLOR] Cll
    
    MsgBox Format(AvgRng, "0.00000")

[COLOR=#0000ff]End Sub[/COLOR]

You can also use

OtherVariable = Application.WorksheetFunction.Countif([COLOR=#ff0000]Your Expression[/COLOR])

For your question regarding numbers over .0006 in your range....

 

[RyanITSC]

Thanks worked like a charm.

 

[mrmmickle1]

Glad you were able to get it working.