+ Reply to Thread
Results 1 to 9 of 9

Get last value in a dynamic range and use value in a messagebox

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    North Carolina
    MS-Off Ver
    Office 365
    Posts
    60

    Get last value in a dynamic range and use value in a messagebox

    i have a dynamic range in Sheet 3. I need to grab the value in the last row of Column M and then place this value in a message box. i can do it by worksheet formula, but striking out trying to perform this task using VBA. And this needs to be in a VBA subroutine triggered by clicking on a command button.

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Get last value in a dynamic range and use value in a messagebox

    Try:

    Please Login or Register  to view this content.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    11-30-2012
    Location
    North Carolina
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Get last value in a dynamic range and use value in a messagebox

    Sorry, but either I am doing something wrong or that is not the answer.

    The named range is FAC located on a sheet named FAC Data.

    I have this line in the VBA sub:

    Please Login or Register  to view this content.
    Not sure what the error is.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Get last value in a dynamic range and use value in a messagebox

    Hi,
    Try
    Please Login or Register  to view this content.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    11-30-2012
    Location
    North Carolina
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Get last value in a dynamic range and use value in a messagebox

    Sorry, but that only generates a blank message box.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Get last value in a dynamic range and use value in a messagebox

    If that creates a blank message box then you have something in the last row of column M that is blank.
    Perhaps you have a formula that calculates to " " or something else.

    The above does the following. It goes to the very last row in your worksheet which is different depending on which version of Excel you have. To get there do this. Click in M1 and then Ctrl+DownArror many times until it will go no further. My last row is 10848576. Now do a single Ctrl+UpArrow. That is the last row with data in your worksheet in Column M. Look at that cell and find what is in it. It isn't completely blank.

  7. #7
    Registered User
    Join Date
    11-30-2012
    Location
    North Carolina
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Get last value in a dynamic range and use value in a messagebox

    Your reply has now shown me a logic error - there is a formula in cell M100 but that formula works on values from columns G,H, and I. In this case, there are no values in G100, H100, and I100, so nothing in the cell but the cell is not blank. Thus, your answers are correct, but i am guilty of faulty planning.

    i guess i need to copy the data over to this sheet and do the math in VBA, rather than embed the formula in cells in Column M. Unless there is a better suggestion?

    Thanks for helping me realize the real issue here.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Get last value in a dynamic range and use value in a messagebox

    Try
    Please Login or Register  to view this content.

  9. #9
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Get last value in a dynamic range and use value in a messagebox

    Quote Originally Posted by lateniteNC1 View Post
    Sorry, but either I am doing something wrong or that is not the answer.
    The named range is FAC located on a sheet named FAC Data.
    Please Login or Register  to view this content.
    It works for me.

+ 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. [SOLVED] Populate Dynamic Array from Cell Values and write to Dynamic Range
    By TFiske in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2018, 09:09 AM
  2. Show Range of Data in messagebox with columnwidth
    By naveenmarapaka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2014, 05:53 AM
  3. Match ComboBox with dynamic range, then add Textbox1 to dynamic range
    By Lasse Moe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2014, 01:26 AM
  4. Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)
    By BrokenBiker in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-04-2012, 11:40 AM
  5. Fill Dynamic Range From Dynamic Source Range
    By goss in forum Excel General
    Replies: 2
    Last Post: 03-06-2012, 12:05 PM
  6. MessageBox range
    By zplugger in forum Excel General
    Replies: 10
    Last Post: 12-07-2011, 04:51 PM
  7. Replies: 2
    Last Post: 02-02-2006, 04:10 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