# Working through a Range backwards

#### damonpc

##### New Member
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

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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``````

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

Replies
5
Views
172
Replies
10
Views
466
Replies
1
Views
458
Replies
2
Views
274
Replies
4
Views
246

1,203,064
Messages
6,053,320
Members
444,653
Latest member
Curdood

### 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.

### Which adblocker are you using?

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

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