CODEWITHSUNDEEP
excelvba
Fwafa
Fwafa

Reputation: 107

Excel VBA manipulate array data

I have 2 worksheets, Main and Return. I have the values in Main and the results in Return. I am trying to find a particular position in an array containing an index value (the data comes from Main sheet) e.g. 10, 20, 40, 50, 60 etc...then take the 5 values above and 5 values below this index including the index value I am searching for and do an average of it returning the average to a cell on the sheet (to the Return sheet), thus doing an average of 11 values. So far I have managed to store the range in the array using: 

Public Sub myArray()
Dim myArr() As Variant
Dim R As Long
Dim C As Long
myArr = Range("C6:D1126")
For R = 1 To UBound(myArr, 1)
For C = 1 To UBound(myArr, 2)
    Debug.Print myArr(R, C)
Next C
Next R
End Sub

The search/find of value within the array and averaging has left me scratching my head...

Please help...thank you. Help with the code in the array or manipulating the data from the worksheet itself works fine by me :)

Sample file --> http://www.filedropper.com/indexes

Upvotes: 0

Views: 2376

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You could use this UDF:

Function avrg(indx, rng As Range)
    Dim i, minI As Long, maxI As Long

    i = Application.Match(indx, rng.Columns(2), 0)

    If IsError(i) Then
        avrg = CVErr(xlErrNA)
        Exit Function
    End If

    With WorksheetFunction
        minI = .Max(1, i - 5)
        maxI = .Min(rng.Rows.Count, i + 5)

        avrg = .Average(rng.Cells(1, 1).Offset(minI - 1).Resize(maxI - minI + 1))
    End With
End Function

This UDF finds first entry of value (say 10 or 20) in Index column (Main sheet) takes 5 values above and 5 below it and returns average of corresponding values of column Value (Main sheet). If you need to take average of values from column Index, change rng.Cells(1, 1) to rng.Cells(1, 2)

Also note at this lines in UDF:

minI = .Max(1, i - 5)
maxI = .Min(rng.Rows.Count, i + 5)

if we can't take 5 values below and 5 values above index i (e.g. if index of target value equals to 2) we take in first case all values from start and in second case all values untill end of range.

Then you can call it either from worksheet: enter this formula in sheet Dash cell C4: =avrg(C3,Main!$C$6:$D$1126) and drag it across.

either from VBA:

Sub test()
    Dim rng As Range
    Dim rngInd As Range
    Dim cell As Range

    Set rng = ThisWorkbook.Worksheets("Main").Range("C6:D1126")
    Set rngInd = ThisWorkbook.Worksheets("Dash").Range("C3:L3")

    For Each cell In rngInd
        cell.Offset(1).Value = avrg(cell.Value, rng)
    Next cell
End Sub

In both cases function returns #N/A if indx value not found.

Upvotes: 1

Related Questions