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

1. ## Solved: 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?  Reply With Quote

2. 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:
[VBA]
Dim LastRow As Long

LastRow = Range("B65536").End(xlUp).Row[/VBA]  Reply With Quote

3. ## invalid character

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:
[VBA] 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
[/VBA]
But this doesn't work.   Reply With Quote

4. Try this:
[VBA]
voltage = Application.WorksheetFunction.Average(Range("B1:B" & i))

[/VBA]  Reply With Quote

5. 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:
[VBA]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[/VBA]

But it doesn't work again.  Reply With Quote  Reply With Quote

7. Hmm... It could be a Mac issue, I am not sure of the proper syntax. Also shouldn't the column be B in:
[VBA]Diference = Cells(i + 1, 0) - Voltage[/VBA]
i.e.,
[VBA]Diference = Cells(i + 1, 2) - Voltage[/VBA]  Reply With Quote

8. Radka, what operating system are you using? What version of Excel?  Reply With Quote

9. 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.  Reply With Quote

10. 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:
[VBA] 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
[/VBA]  Reply With Quote

11. I use WinNT, Office 97.  Reply With Quote

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

Try this[vba]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[/vba]It works for me   Reply With Quote

14. 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.  Reply With Quote

15. 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   Reply With Quote

16. 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.  Reply With Quote

17. 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!  Reply With Quote

18. 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.  Reply With Quote

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.[vba]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[/vba]John   Reply With Quote

20. ## new problem

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.   Reply With Quote

#### Posting Permissions

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