+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

Fixed lastrow in percentage formula

  1. #1
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    134

    Fixed lastrow in percentage formula

    Respected MVPs, experts and gurus,

    I have written the following code to calculate the % out of total for all the rows. I am sure there is a better way to do it.

    Please Login or Register  to view this content.
    I wrote this code as I cannot make the denominator fixed. For 2,000 rows it is not a big deal but some sheets have 100,000 rows which might take significant time to execute this loop. Instead of this loop, copying the formula from third row and pasting it from fourth row to lastrow3 (fixed with "$" sign) should be the simple way to execute the code quickly.

    I have thought the following code but could not fathom "..................." :

    Please Login or Register  to view this content.
    The Total is at lastrow3 plus one cell down.

    Thank you
    Roshan Shakya
    Last edited by Roshan.Shakya; 10-17-2019 at 01:53 PM. Reason: for clarity

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    XLS 2016
    Posts
    3,924

    Re: Fixed lastrow in percentage formula

    What is lastrow3 ???
    What is the data layout ... an Excel sample perhaps !!
    Last edited by PCI; 10-17-2019 at 02:24 PM.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,620

    Re: Fixed lastrow in percentage formula

    I don't understand what "...lastrow3 (fixed with "$" sign)..." means.

    You can fetch the last row in column F with:
    Please Login or Register  to view this content.

    Please post a workbook...
    Ben Van Johnson

  4. #4
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    134

    Re: Fixed lastrow in percentage formula

    lastrow3 is like any other last row where the data ends. I have assigned different last rows (in the same sheet) as I need them referring in multiple occasions and the different lastrows (lastrow, lastrow1, .....) allows me to work on different part of excel sheet.

    lastrow3 plus one cell down has the sum total from start cell to lastrow cell.

    Thank you
    Roshan Shakya

  5. #5
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    134

    Re: Fixed lastrow in percentage formula

    Let's assume lastrow3 as 3125th row and the cell having SUM Total is D3126.

    If I copy =D3/D3126 from Cell F3 and paste it all the way down through to F3125 then the last cell F3125 will have =D3125/D6249. My requirement is to make the denominator fixed such that F3125 will have D3125/$D$3126.

    The data are dynamic and lastrow keeps on changing while processing the data and for this purpose I have used lastrow3 (assigned variable for last row) to identify the last row before executing my Loop code. In my Loop code the use of "i" subtracting from the lastrow3 +1 offsets this one cell down movement.
    Last edited by Roshan.Shakya; 10-17-2019 at 02:40 PM.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,620

    Re: Fixed lastrow in percentage formula

    Please post a workbook. You can/should use *AUTOFILL" vs. looping....

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    XLS 2016
    Posts
    3,924

    Re: Fixed lastrow in percentage formula

    Perhaps ...!
    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,620

    Re: Fixed lastrow in percentage formula

    something like:
    Please Login or Register  to view this content.
    But we're still guessing...

  9. #9
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    134

    Re: Fixed lastrow in percentage formula

    Here you go. I have attached the file.

    Column F has the output I desired.

    Thank you so much!!!
    Roshan Shakya
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    XLS 2016
    Posts
    3,924

    Re: Fixed lastrow in percentage formula

    Another one more funny
    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    XLS 2016
    Posts
    3,924

    Re: Fixed lastrow in percentage formula

    THe macro seems working
    See your file attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    134

    Re: Fixed lastrow in percentage formula

    Thanks PCI for the solution. It's a nice code to get a solution. I have a solution using formula For LOOP and my intention is to have the formula in it and not the absolute value.

    Can I have something like this in Cell 'F25' "= D25/$D$3126"?

    For this example, my lastrow3 is row 3125 and it may change. The SUM total formula was written like this:

    Please Login or Register  to view this content.
    Thank you
    Roshan Shakya

  13. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,620

    Re: Fixed lastrow in percentage formula

    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    XLS 2016
    Posts
    3,924

    Re: Fixed lastrow in percentage formula

    You were complainng that it takes time to scroll down the formula ... the same if you keep formulas you slowing down Excel ...!
    For last row see next code
    Please Login or Register  to view this content.
    Well now you have enough code sample to be happy.
    Last edited by PCI; 10-17-2019 at 04:40 PM.

  15. #15
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    134

    Re: Fixed lastrow in percentage formula

    New thing to learn today. This is absolutely superb.

    Thanks protonLeah.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1