# Thread: How to work with every cell in an array?

1. This line calculates that last row you have (for Column B).
[vba]LastRow = ActiveSheet.Range("B65536").End(xlUp).Row [/vba]
Try this macro:
[vba]
Option Explicit

Sub Voltage()

Dim Voltage As Double
Dim Difference As Double
Dim i As Long
Dim AvrPoints As Boolean
Dim LastRow As Long

LastRow = Range("B65536").End(xlUp).Row
For i = 2 To LastRow
Voltage = Application.WorksheetFunction.Average(Range("B1:B" & i))
Difference = Cells(i + 1, 2) - Voltage
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox Voltage
Else
MsgBox "Criteria was not met.", vbInformation
End If

End Sub
[/vba]

2. Thank you very much, Jacob!
I should add "ActiveSheet" before "Range" and all is OK.

Now I have to try to put one parameter, which calculates the number of averaged cells.

Thank you once again!

3. You're Welcome

Take Care

P.S. The number of averaged cells is i.

4. ## new question

now I should average the cells in the columnB, but I have to beginn with the last cell(LastRow). I have tryied follow code, but it doesn't work. [VBA]Sub VoltN()

Dim VoltN As Double
Dim Difference As Double
Dim i As Long
Dim AvrPoints As Boolean
Dim LastRow As Long

LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = LastRow To 1
VoltN = Application.WorksheetFunction.Average(ActiveSheet.Range("(B & i):B1"))
Difference = Cells(i - 1, 2) - VoltN
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox i
MsgBox VoltN

End If

ActiveSheet.Cells(2, 4).Value = i
ActiveSheet.Cells(2, 5).Value = VoltN
End Sub[/VBA]

5. Hi,

You can try your average function something like this ...

[vba]
Range("B1:B" & i)
[/vba]

6. The last cell is LastRow.
[VBA]Range("A1:A" & LastRow)[/VBA]

7. your suggestions work, but when I use them, my Sub calculates the average, started with first cell(B1). I need to take last row(B&LastRow) and calculate the average started from last cell to first cell. And I have to use the same criteria i.e. comparison between the average and the cell before it(for example, average(B100:B90) compares to the value of B89). And if the difference between the cell value and average is greater than 0,004, the Loop stop, and I have to obtain the average and the number of averaged cells.

8. I have this code:[VBA] Sub VoltN()

Dim VoltN As Double
Dim Difference As Double
Dim i As Long
Dim n As Long
Dim AvrPoints As Boolean
Dim LastRow As Long

LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 1 To LastRow
n = LastRow - i
VoltN = Application.WorksheetFunction.Average(ActiveSheet.Range("B&n:B" & LastRow))
Difference = Cells(n - 1, 2) - VoltN
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox n
MsgBox VoltN

End If
[/VBA]
The "Range" on row "VoltN=....." is not correct. I want to take a range (Bn:B&LastPow). How can I define this range?

9. If you want Bn and BLastRow then:

[VBA] Range("B" & n & ":B" & LastRow)[/VBA]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•