How to work with every cell in an array?

[Jacob Hilderbrand]

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]

[Radka]

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!

[Jacob Hilderbrand]

You're Welcome

Take Care

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

[Radka]

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]

[Zack Barresse]

Hi,

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


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

[Jacob Hilderbrand]

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

[Radka]

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.

[Radka]

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?

[Jacob Hilderbrand]

If you want Bn and BLastRow then:

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