How to work with every cell in an array?

Hello! I am new in VBA and I would like to ask a simple question.
I have the row data obtained from measurement. The data are in column A and B of the worksheet. But I don't know how many rows are there.
I have to take cells of column B and calculate the average, but the problem is that I have to use a loop, which at first calculates the average of first two cells, after that 3 cells and etc.(for example first loop execution calculate average of B1, second loop execution-average of B1 and B2, third loop execution-average of B1,B2 and B3 and s.o.). Every time the average have to be compared to the value of the next cell(example averager of B1:B10 to be compared ti B11). The loop have to be executed until the difference between the average and next cell value(B11-avrg(B1:B10)) becomes greater than 0,0004.
As the result I have to obtain the average and the number ot averaged cells.
Can anybody suggest me the solution of my problem?
Thanks in advance.

Can you use a formula and then fill it down?

Code:

---

=AVERAGE($B$1:B1)

---

Or you can determine the last row for you loop with something like this:

Code:

---

Dim LastRow As Long
LastRow = Range("B65536").End(xlUp).Row

---

Dear DRJ, thanks a lot for your help.
I have tryed this, but VBA said me, that $B$1 is a "invalid character"

Here is the code, I have written:

Code:

---

Sub Voltage()
Dim Voltage
Dim Difference
Dim i As Integer
Dim AvrPoints As Boolean
AvrPoints = False
ActiveCell.Offset(0, 1).Select
Dim LastRow As Long
LastRow = Range("B65536").End(xlUp).Row
Do Until AvrPoints = True
For i = 1 To LastRow
Voltage = AVERAGE($B$1:Bi)
Diference = Cells(i + 1, 0) - Voltage
If Difference > 0.0004 Then
AvrPoints = True
End If
Next i
Loop
MsgBox Voltage
End Sub

---

But this doesn't work. :dunno

Try this:

Code:

---

voltage = Application.WorksheetFunction.Average(Range("B1:B" & i))

---

Jacob, I have changed my Sub, but everey time when I put "Range(...)" in my Sub, the message is appeared "Wrong number of arguments or invalid property assigment".

My new Sub is:

Code:

---

Sub Voltage()
    Dim Voltage
    Dim Difference
    Dim i As Integer
    Dim AvrPoints As Boolean
    AvrPoints = False
    ActiveCell.Offset(0, 1).Select
    Dim LastRow As Long
      LastRow = Range("B65536").End(xlUp).Row
Do Until AvrPoints = True
For i = 1 To LastRow
Voltage = Application.WorksheetFunction.Average(Range("B1:B" & i))
Diference = Cells(i + 1, 0) - Voltage
If Difference > 0.0004 Then
      AvrPoints = True
  End If
  Next i
Loop
    MsgBox Voltage
End Sub

---

But it doesn't work again.

P.S. I had a problem with log on, because of this now I am Radka.(Radka=Rady) :-)

Hmm... It could be a Mac issue, I am not sure of the proper syntax. Also shouldn't the column be B in:

Code:

---

Diference = Cells(i + 1, 0) - Voltage

---

i.e.,

Code:

---

Diference = Cells(i + 1, 2) - Voltage

---

Radka, what operating system are you using? What version of Excel?

Quote:

---

Originally Posted by DRJ

Hmm... It could be a Mac issue, I am not sure of the proper syntax.

---

I will check this on XL2004 tonight, if needed. In my experience so far, this shouldn't be an issue.

I have started to execute the Sub by F8 and on "8 row of the Sub" the message above is appeared. And this is happend with another Sub, which consists "Range(...)".
My last Sub is:

Code:

---

Sub Voltage()
Dim Voltage
Dim Difference
Dim i As Integer
Dim AvrPoints As Boolean
AvrPoints = False
Dim LastRow As Long
LastRow = Range("B65536").End(xlUp).Row
Do Until AvrPoints = True
For i = 1 To LastRow
Voltage = Application.WorksheetFunction.Average(Range("B1:B" & i))
Diference = Cells(i + 1, 2) - Voltage
If Difference > 0.0004 Then
AvrPoints = True
End If
Next i
Loop
MsgBox Voltage
End Sub

---

I use WinNT, Office 97.

I just moved this to the Excel (General Forum) since it is not Mac specific.

Hi Radka,

Try this

Code:

---

Sub Voltage()
      Dim Voltage
      Dim Difference
      Dim i As Integer
      Dim AvrPoints As Boolean
AvrPoints = False
      ActiveCell.Offset(0, 1).Select
      Dim LastRow As Long
      LastRow = Range("B65536").End(xlUp).Row
Do Until AvrPoints = True
            For i = 1 To LastRow
                  Voltage = Application.WorksheetFunction.Average(Range("B1:B" & i))
                  Difference = Cells(i + 1, 2) - Voltage '<<< (Spelling of difference)
                  If Difference > 0.0004 Then
                        AvrPoints = True
                  End If
            Next i
      Loop
      MsgBox Voltage
End Sub

---

It works for me :bink:

Hi Johnske, thank you for code, but it does't work for me.
I don't know why, but on row9 of the Sub (LastRow=Range("B65536").....) VBA select the word "Range" and message is appeared "Compile error: Wrong number of arguments or invalid property assigment".
This is happened with other Subs, which consists the word "Range(...)".
I am using Win NT and Office 97.

Hmm, it shouldn't make any difference, but perhaps 97 is more literal and requires a sheet to be specified in conjunction with a range.

Try the attached, I know it works ok on mine (2000), I have just included "ActiveSheet" and some data

John :bink:

John, thank you for all, but it doesn't work again.
Now, the row9 is OK, but the same error is appeared on row12
Voltage = Application.WorksheetFunction.Average(Range("B1:B" & i))

VBA select the word "Range(...)" and said the above message.
I have tryed to put "ActiveSheet" before "Average"(or before "WorksheetFunction, or before "Application") but it doesn't work.

John, now it works! :-))
I put the "ActiveSheet" before "Range" i.e. :

Voltage = Application.WorksheetFunction.Average(ActiveSheet.Range("B1:B" & i))

Thank you so much!

Great Radka! Did you know you can mark your own threads solved? Click Thread Tools at the top of the thread, select Mark Solved -> Perform Action. :yes

And btw, if you are having issues with your other user name and would like to use it, let me know and we'll straighten it out for you.

Glad to be able to help Radka.

Just one last thing...If you use option Explicit, the original mis-spelling I pointed out will be picked up by Excel and you will be told you have an undeclared variable, i.e.

Code:

---

Option Explicit
Sub Voltage()
      Dim Voltage, Difference As Variant
      Dim i As Integer, AvrPoints As Boolean
      Dim LastRow As Long
      AvrPoints = False
      ActiveCell.Offset(0, 1).Select
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
      Do Until AvrPoints = True
            For i = 1 To LastRow
                  Voltage = Application.WorksheetFunction.Average(ActiveSheet.Range("B1:B" & i))
                  Difference = Cells(i + 1, 2) - Voltage
                  If Difference > 0.0004 Then
                        AvrPoints = True
                  End If
            Next i
      Loop
      MsgBox Voltage
End Sub

---

John :bink:

Hello!
I have new problem with my code. I calculates the average of all cell, i.e. if I have 100row, it calculates the average of 100cells.
But my task is: I have to calculate average at first of B1; next B1 and B2; next B1,B2 and B3; next B1,B2,B3 and B4;.......and every time I have to compare every average to the value of the next cell. For example, average(B1:B2) compares to the value of B3. And if the difference between the value of the next cell and average is greater than 0.0004, the Loop will stop, and I have to obtain average and the number of averaged cell.
Sometime my data consists 100 row, sometime 700rows, but I never know how many rows are there. And everytime, the number of averaged cell is different.
I should change something in my Sub, but I don't know what. I have done some changes, but it doesn't work.
:help

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.

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]