Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Thanked 0 Times in 0 Posts

    VBA Data Storage (2003 Excel VBA)

    Here is what I would like to do but need help getting started.

    I have numbers stored in column A, the amount of numbers could change each time I run this, no more than 500 to 1000.

    I what to check each number to find which number has the max deviation from the average of those numbers and then flag that number.

    I then want to continue doing this with that number removed for a predetermined amount of times.

    I would then paster the numbers in column B with the flagged numbers (ex. 54.2 Flag).

    What is the best way and cleanest way to do this. Should I store the numbers in some sort of array for should I just paste in a column to store the newest data.

    Hope this makes sense.


  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: VBA Data Storage (2003 Excel VBA)

    Does the code below do what you want? The code expects you to put the "predetermined amount of times" in cell C1.

    Public Sub MaxDev()
    Dim I As Long, J As Long, lMax As Long, lLast As Long
    Dim dMaxDif As Double, dAve As Double
    lLast = Range("A65536").End(xlUp).Row - 1
    dAve = Application.WorksheetFunction.Average(Range(Range( "A1"), Range("A1").Offset(lLast, 0)))
    Range("A:A").Interior.ColorIndex = xlColorIndexNone
    For I = 1 To Range("C1").Value
    dMaxDif = 0
    lMax = 0
    For J = 0 To lLast
    If Range("A1").Offset(J, 1).Value = "" Then
    If Abs(dAve - Range("A1").Offset(J, 0).Value) > dMaxDif Then
    dMaxDif = Abs(dAve - Range("A1").Offset(J, 0).Value)
    lMax = J
    End If
    End If
    Next J
    Range("A1").Offset(lMax, 1).Value = dMaxDif
    Range("A1").Offset(lMax, 0).Interior.ColorIndex = 3
    Next I
    End Sub
    Legare Coleman

Posting Permissions

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