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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,146
Members
430,342
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top