Working through a Range backwards

damonpc

New Member
Joined
Nov 25, 2005
Messages
2
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi, Welcome to the board.

This code might do what you require:
Code:
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

New Member
Joined
Nov 25, 2005
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,323
Messages
5,571,544
Members
412,402
Latest member
xam99
Top