Working through a Range backwards

[damonpc]

I have devised the piece of cade below to calculate avererages based on the last 9 valid values.

Unfortunately i need the code to work backwards throught the range. For example in the rane A1:C10, i need the cells to be checked starting at C10, then b10, a10 then c9, b9, a9, etc.

Hope someone can help otherwise i need to restructure the whole workbook.

Cheers

Damon

***CODE***

Public Function TEST(rg As Range, Optional lCondf As Long = 1) As Long
Dim lTempCount, ltempvalue As Long
Dim cl As Range

For Each cl In rg
If cl.Value <> "NB" Then
lTempCount = lTempCount + 1
ltempvalue = ltempvalue + cl.Value
End If

If lTempCount = 9 Then
GoTo 10
End If
Next cl

10


TEST = ltempvalue / lTempCount
End Function

 

[al_b_cnu]

Hi, Welcome to the board.

This code might do what you require:

Function xxx(ByVal Rangex As Range) As Double
Dim iColFr As Integer, iColTo As Integer, iCol As Integer
Dim lTempCount As Long, lTempValue As Long
Dim lRowFr As Long, lRowTo As Long, lRow As Integer
Dim vCur As Variant
Dim WS As Worksheet

Set WS = Sheets(Rangex.Parent.Name)
lRowFr = Rangex.Row
lRowTo = lRowFr + Rangex.Rows.Count - 1
iColFr = Rangex.Column
iColTo = iColFr + Rangex.Columns.Count - 1

For lRow = lRowTo To lRowFr Step -1
    For iCol = iColTo To iColFr Step -1
        vCur = WS.Cells(lRow, iCol).Text
        If vCur <> "NB" Then
            lTempCount = lTempCount + 1
            lTempValue = lTempValue + Val(vCur)
        End If
        If lTempCount > 8 Then Exit For
    Next iCol
    If lTempCount > 8 Then Exit For
Next lRow

xxx = lTempValue / lTempCount
End Function

 

[damonpc]

Range Backwards

You are a God, thank you.

I will post a link if a I may on Utter Access as i had posed the same query there.

Thanks again

Damon