Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to compute average till blank cell appears in adjacent column

    Hi All,

    I am newbie to macros. There are 2 columns in my excel sheet. As part of the bigger project, I need to calculate the average% of below % till a blank cell appears in ID column. I am attaching an excel sheet with dummy data to explain better. Really appreciable if any excel champ could be able to provide a macro to do this. Thank you very much in Advance.

    Best Regards,
    Abhishek
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Abhishek,

    This should do what you want:

    Code:
    Option Explicit
    
    Sub MyPercents()
    
    
       Dim lLastRow   As Long
       Dim lNextBlank As Long
    
       
       lLastRow = Cells(Rows.Count, 1).End(xlUp).Row  '*** Find last used row
        
       lNextBlank = Cells(lLastRow, 1).End(xlUp).Row  '*** Find row Before blank!
       
       If lNextBlank = 1 Then Exit Sub
       
       Do
       
         Cells(lNextBlank - 1, 2).Formula = "=Average(" & _
                           Cells(lNextBlank, 2).Address(, , xlA1) & ":" & _
                           Cells(lLastRow, 2).Address(, , xlA1) & ")"
       
         lLastRow = lNextBlank - 2     '*** Move up to next non-blank entry
         lNextBlank = Cells(lLastRow, 1).End(xlUp).Row  '*** Get new top row
       Loop Until (lNextBlank = 1)
    
    End Sub    'MyPercents
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many Thanks for your response. I believe I failed to mention that there are multiple columns between A & B. Also I have to update the data from row number 25. There are some other stuff above row 25 which I need not to update.

    Request if you modify the macro accordingly. Sorry for the inconvenience caused. Thanks a lot.

    Best Regards,
    Abhishek

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Abhishek

    You could get what you need using a named formula.
    In the attached example file, the named formula is myAvg
    Just put
    =myAvg
    to get the result.

    To see the formula definition, look in the Name Manager.
    I assumed that there are up to a max of 100 rows to be included for any calculation of the average. You can adjust the formula definition o match your requirements.
    You can insert columns and rows etc etc, and the calculated result will adjust etc etc etc

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2016-06-10 at 15:50.

  5. #5
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Zeddy for your reply. Its working in the Dummy Data Sheet. But once I put the myAvg formula into my excel sheet, it is opening up the window to update the values. If I cancel it, it is showing the error N/A.

    Request your support please.

    Best Regards,
    Abhishek

  6. #6
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Zeddy. It is working perfectly fine. My mistake.

    Best Regards,
    Abhishek

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Abhishek

    I am very pleased it worked for you.
    Amazing.

    ..and thanks again to RG for a macro way of doing it.

    zeddy

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Abhishek,

    Here's the modified code:

    Code:
    Option Explicit
    
    Sub MyPercents()
    
       Dim lLastRow    As Long
       Dim lNextBlank  As Long
       Dim lStopHere   As Long
       Dim lDataCol    As Long
       Dim lFormulaCol As Long
    
    '****  Setup your parameters ****
       lStopHere = 25   '*** Don't process above this line
       lDataCol = 1     '*** Column with the raw Data to be averaged
       lFormulaCol = 8  '*** Column to contain the Formula
    
    '**** Initialize the variables ****
       lLastRow = Cells(Rows.Count, 1).End(xlUp).Row  '*** Find last used row
       lNextBlank = Cells(lLastRow, 1).End(xlUp).Row  '*** Find row Before blank!
       
       If lNextBlank < -lStopHere Then Exit Sub  '**** Nothing to Process
       
       Do
       
         Cells(lNextBlank - 1, lFormulaCol).Formula = "=Average(" & _
                           Cells(lNextBlank, lFormulaCol).Address(, , xlA1) & ":" & _
                           Cells(lLastRow, lFormulaCol).Address(, , xlA1) & ")"
       
         lLastRow = lNextBlank - 2     '*** Move up to next non-blank entry
         lNextBlank = Cells(lLastRow, lDataCol).End(xlUp).Row  '*** Get new top row
       Loop Until ((lNextBlank <= lStopHere) Or (Cells(lNextBlank, lDataCol).Value = "ID"))
    
    End Sub    'MyPercents
    Sample Run:

    Percents.PNG

    Of course you'll have to adjust the Setup Values to fit your actual data but this is not very easy to modify.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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