+ Reply to Thread
Results 1 to 13 of 13

Making last cell in vba code with sumifs

  1. #1
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2013
    Posts
    311

    Making last cell in vba code with sumifs

    Hi Guys
    I have some code I have been given. From this forum but unusually I have not kept the member's name. It works great but does not allow me to add more rows unless I alter the last row number. I have tried fiddling with it without any luck.

    Here it is
    Please Login or Register  to view this content.
    The code is on a sheet called Summary. It looks up data in a sheets called Transactions within data parameters which are fine. 2033 is the hard wired last row. I could easily just change it each time but would love to have it clean.

    Any help greatly appreciated

    All the best

    Mark
    Overcast here in London but may get better later

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,851

    Re: Making last cell in vba code with sumifs

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2013
    Posts
    311

    Re: Making last cell in vba code with sumifs

    Hi TMS

    Too much for a Saturday morning. Made me laugh way before looking at your code. 'Being unapologetic means never having to say you're sorry' John Cooper Clarke. I will be using it though I won't pretend its mine.
    “Always forgive your enemies; nothing annoys them so much.”
    – Oscar Wilde

    I have several sheets all linked and use a control sheet where there are tables. In this instance I have B3 to B62, I deactivated my original B3 line and popped in your code. I am getting the dreaded #VALUE!. Not sure why. I will keep looking about but if I am not successful could i Please send you a few sheets. It is no doubt a simple alteration but my knowledge is limited.
    Thank you
    Mark

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,851

    Re: Making last cell in vba code with sumifs

    Can you post a sample workbook with any sensitive information removed. It does not need all the data, just the relevant sheet(s) and enough sample data to test.

  5. #5
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2013
    Posts
    311

    Re: Making last cell in vba code with sumifs

    Hi TMS
    My original workbook was way too large. Took me time to strip it. Note I have spent summary as summaryy intentionally.
    The function is on the Summarry screen. Hopefully it will open on that screen. B3.
    I have left the VBA as I had it save that I have removed Transactions to make the file smaller. Now only BDC2.
    If you open the VBA you will see the specific code two thirds way down immediately below 8 rows of ++++.
    Hope this makes sense.
    Please feel free to make any critique of the VBA as you think fit. You will see it has be cobbled together indicating the level of my experience. This year I intend to improve.

    Mark

    AHHH Can't upload my file. I have opened advanced and the paper clip is there but it only opens a tiny box below it not an upload dialogue ox of my files. Tried googling for a solution but none.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,851

    Re: Making last cell in vba code with sumifs

    The paperclip hasn't worked for quite some time


    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  7. #7
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2013
    Posts
    311

    Re: Making last cell in vba code with sumifs

    Can't see where the attachment should show. Unless you are supposed to do the same. I expect the alteration is related to the security issue.
    Mark
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,851

    Re: Making last cell in vba code with sumifs

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2013
    Posts
    311

    Re: Making last cell in vba code with sumifs

    Hi TMS
    Its been a while since I was able to get back to this. Thank you very much but I'm not sure this is what I am looking for. I had entered row 2033 'R2033C5' in my VBA as I could not work out how to enter the last row. What you have here is row 6. This is part of my accounts system and I know that I could buy one but prefer to make it. I need a function / formula that accepts varying number of rows. I must have asked my question incorrectly when I said last cell. Perhaps I should have said last row.

    Mark

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,851

    Re: Making last cell in vba code with sumifs

    Seems I've slept a few times since you last updated the thread

    Last cell ... last row ... whatever. The code determines the last row of data of data on Sheet BDC2 based on column E:

    Please Login or Register  to view this content.
    Your code, in the original post:

    Please Login or Register  to view this content.
    generates this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    So, if I edit that to match the structure in the sample workbook, with sheet BDC2, thus:

    Please Login or Register  to view this content.
    it generates this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note that, in both cases, the range to be summed is different to the criteria ranges ... the range to be summed includes only rows 2:3, whereas the criteria ranges include rows 2:2033.

    The amended code that I provided first determines the number of rows on sheet BDC2 and then uses that in generating the formula:

    Please Login or Register  to view this content.
    It determines the last row of data on Sheet BDC2 which, in your sample workbook, is 6.

    Hence, this gives:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    As you can see, this uses rows 2:6 for the range to be summed and for the criteria range(s)

    This returns a value of 0 because column AE has no values/data in it.

    Not sure what it is you are looking for.
    Last edited by TMS; 03-02-2017 at 07:05 AM. Reason: Fix a couple of typos

  11. #11
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2013
    Posts
    311

    Re: Making last cell in vba code with sumifs

    Hi TMS
    Reading your text I can see that you are on the same page and it is me that is not able to get it .... as yet. But I will. Have people staying for the next few days so will not be able to get back to this till they have gone. I am though blown over by your generosity in the completeness of your response.
    I have so much to learn.
    So an interim thank you.
    Mark

  12. #12
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2013
    Posts
    311

    Re: Making last cell in vba code with sumifs

    Hi TMS
    You must have thought me dead. Well here I am back from the grave. This is great. Obviously because it works beautifully. But also because of the excellent way you have explained it in so much detail. I had 2016 in the drop down but 2015 in the sheet so of course it was always a Zero.
    Again thank you.

    Mark

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,851

    Re: Making last cell in vba code with sumifs

    You're welcome. Thanks for the rep.

+ 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. Convert Sumifs to vba code with loop
    By lakamas in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-13-2015, 06:33 PM
  2. Making CountIFS and SUMIFS Formula Dynamic
    By HangMan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2015, 04:58 AM
  3. Slow code- making code more efficient
    By ammartino44 in forum Excel General
    Replies: 4
    Last Post: 05-06-2015, 12:47 PM
  4. [SOLVED] SUMIFs Code
    By fahmiafk in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-04-2014, 02:16 AM
  5. [SOLVED] making sumifs work in VBA
    By abordeau in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-20-2014, 01:24 PM
  6. My code is making cell entry freeze! Help.
    By jakedeg in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-13-2012, 07:53 PM
  7. Code for making a cell chage color by dbl clicking, with either a
    By Zan Dorris in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2006, 12:03 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