How to work with every cell in an array?

[Rady]

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.

[Jacob Hilderbrand]

Can you use a formula and then fill it down?

=AVERAGE($B$1:B1)

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

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

[Rady]

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:

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.

[Jacob Hilderbrand]

Try this:

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

[Radka]

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:

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.

[Radka]

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

[Jacob Hilderbrand]

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

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

i.e.,

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

[shades]

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

[shades]

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.

[Radka]

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:

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

[Radka]

I use WinNT, Office 97.

[shades]

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

[johnske]

Hi Radka,

Try this

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

[Radka]

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.

[johnske]

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

[Radka]

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.

[Radka]

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!

[Zack Barresse]

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.

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.

[johnske]

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.

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

[Radka]

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.