+ Reply to Thread
Results 1 to 15 of 15

VBA code to divide the last row from column A-J by the last row from column K.

  1. #1
    Registered User
    Join Date
    03-27-2015
    Location
    USA
    MS-Off Ver
    ms office 360
    Posts
    34

    VBA code to divide the last row from column A-J by the last row from column K.

    Hi,

    I have a worksheet with a dynamic range. I am trying to create a macro that would calculate the percentage of total. I am having trouble creating a macro that would divide the last row of column A-J by the last row of column K.

    Can someone please provide me with a code that allow me to do this.

    Thanks!

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    You could use names

    Name: LastRow
    RefersTo: =MATCH(99E+99, Sheet1!$K:$K)


    And then the formula =INDEX(A:A, LastRow,1)/INDEX($K:$K, LastRow,1) dragged right.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Here you go,

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-27-2015
    Location
    USA
    MS-Off Ver
    ms office 360
    Posts
    34

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Thanks for the reply. I appreciate your help.

  5. #5
    Registered User
    Join Date
    03-27-2015
    Location
    USA
    MS-Off Ver
    ms office 360
    Posts
    34

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Dave, the code did not work. I received an error and highlighted

    x = c / Range("K" & c.Row).

    Thanks.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Are you going to share what the error is?

  7. #7
    Registered User
    Join Date
    03-27-2015
    Location
    USA
    MS-Off Ver
    ms office 360
    Posts
    34

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Sorry, I am a beginner in excel.

    I received a Run-time error'13': Type mismatch.

  8. #8
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Are there numbers in all the ranges or is there text as well, did you really mean columns A:J or do they start at a different row.

  9. #9
    Registered User
    Join Date
    03-27-2015
    Location
    USA
    MS-Off Ver
    ms office 360
    Posts
    34

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    You are correct. I should have been more specific. Column A has text, and the numbers are in column B-K starting from row 6
    Last edited by psingh2688; 04-26-2015 at 12:38 PM.

  10. #10
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Revised to start at row(6).

    This should resolve the error, if you have a problem still , attach a sample workbook.

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Are the Columns staggered in length?

    Another possibility. It'll put the result 2 Rows down from the last used cell.


    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    03-27-2015
    Location
    USA
    MS-Off Ver
    ms office 360
    Posts
    34

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Dave, It divided all the rows in the columns B-J by columns k. I just need it to divide the last row of the columns B-J by last row of column K.

    Jolivanes, the code worked perfectly, but i just need it to divide it by 100.

    Attached is a sample worksheet

    Once again thank you for your time and help.
    Last edited by psingh2688; 04-26-2015 at 02:21 PM.

  13. #13
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Please Login or Register  to view this content.
    Edit to
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    03-27-2015
    Location
    USA
    MS-Off Ver
    ms office 360
    Posts
    34

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Both codes worked. Thanks you! I appreciate it very much.

  15. #15
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: VBA code to divide the last row from column A-J by the last row from column K.

    Re: but i just need it to divide it by 100


    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    If you use this last one, your cell formatting will be set to percentage so you'll need to set them back to General.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 03-25-2015, 06:24 PM
  2. Replies: 1
    Last Post: 03-13-2015, 03:47 AM
  3. [SOLVED] Macro/VB Code to Divide Worksheet into several New Files By Column
    By mabiem in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-30-2013, 12:50 PM
  4. Divide column A/2.2046 if column B and C is not equal
    By cckonline in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2013, 04:57 AM
  5. Divide one column from another (numbers) and paste answer in new column
    By nachousa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2010, 12:39 PM

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