PDA

View Full Version : [SOLVED] How to work with every cell in an array?



Rady
11-03-2004, 01:06 AM
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
11-03-2004, 01:59 AM
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
11-03-2004, 03:00 AM
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. :dunno

Jacob Hilderbrand
11-03-2004, 03:20 AM
Try this:


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

Radka
11-03-2004, 06:12 AM
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
11-03-2004, 06:15 AM
P.S. I had a problem with log on, because of this now I am Radka.(Radka=Rady) :-)

Jacob Hilderbrand
11-03-2004, 06:51 AM
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
11-03-2004, 06:57 AM
Radka, what operating system are you using? What version of Excel?

shades
11-03-2004, 06:58 AM
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
11-03-2004, 07:00 AM
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
11-03-2004, 07:03 AM
I use WinNT, Office 97.

shades
11-03-2004, 07:06 AM
I just moved this to the Excel (General Forum) since it is not Mac specific.

johnske
11-03-2004, 07:44 AM
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 :bink:

Radka
11-03-2004, 08:01 AM
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
11-03-2004, 08:33 AM
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:

Radka
11-03-2004, 08:49 AM
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
11-03-2004, 08:56 AM
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
11-03-2004, 09:18 AM
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.

johnske
11-03-2004, 01:56 PM
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 :bink:

Radka
11-04-2004, 12:50 AM
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

Jacob Hilderbrand
11-04-2004, 01:06 AM
This line calculates that last row you have (for Column B).
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
Try this macro:

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

Radka
11-04-2004, 02:41 AM
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
11-04-2004, 03:59 AM
You're Welcome

Take Care

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

Radka
11-04-2004, 08:58 AM
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. 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

Zack Barresse
11-04-2004, 09:09 AM
Hi,

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



Range("B1:B" & i)

Jacob Hilderbrand
11-04-2004, 03:09 PM
The last cell is LastRow.
Range("A1:A" & LastRow)

Radka
11-05-2004, 12:34 AM
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
11-05-2004, 01:02 AM
I have this code: 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

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
11-05-2004, 02:18 AM
If you want Bn and BLastRow then:

Range("B" & n & ":B" & LastRow)