Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 29

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

  1. #1
    VBAX Newbie Rady's Avatar
    Joined
    Oct 2004
    Posts
    3
    Location

    Question 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.

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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

  3. #3
    VBAX Newbie Rady's Avatar
    Joined
    Oct 2004
    Posts
    3
    Location

    Question 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:

    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.

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Try this:

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

  5. #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:

    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.

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

  7. #7
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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

  8. #8
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Radka, what operating system are you using? What version of Excel?

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  9. #9
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    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.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  10. #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:

    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

  11. #11
    I use WinNT, Office 97.

  12. #12
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    I just moved this to the Excel (General Forum) since it is not Mac specific.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  13. #13
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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

  14. #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.

  15. #15
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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

  16. #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.

  17. #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!

  18. #18
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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.

  19. #19
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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

  20. #20

    Question 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.

Posting Permissions

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