-
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]
-
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!
-
You're Welcome
Take Care
P.S. The number of averaged cells is i.
-
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]
-
Hi,
You can try your average function something like this ...
[vba]
Range("B1:B" & i)
[/vba]
-
The last cell is LastRow.
[VBA]Range("A1:A" & LastRow)[/VBA]
-
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.
-
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?
-
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
-
Forum Rules