+ Reply to Thread
Results 1 to 5 of 5

InputBox Range - Process the contents Dynamically

  1. #1
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    InputBox Range - Process the contents Dynamically

    Is it possible to make the following code work dynamically?
    Currently I have to know what range the user is going to select, and then assign those ranges as shown in my code below. I would prefer the user could select any range, and the code would process the user's range in the same manner.

    Please Login or Register  to view this content.
    Thank you
    Mike

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: InputBox Range - Process the contents Dynamically

    Hi Mike,

    Try the following code. The user can select any range including a non-contiguous range.
    Please Login or Register  to view this content.
    To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

    Lewis
    Last edited by LJMetzger; 03-06-2015 at 07:38 AM.

  3. #3
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: InputBox Range - Process the contents Dynamically

    Much more than I was expecting, but thank you, it works great!

    By the way, this isn't for anything particular, I'm just practicing VBA trying to get a grasp on the coding element.

    I'm curious why some commands are not shown after entering the dot notation, such as Application.Sum? If somebody hadn't told me I could use it, I wouldn't have known it existed.

    I was able to use it to sum MyRange variable, but I didn't understand why it wasn't in the drop down list of available commands. How would you know a command is available if it's not offered in the list?

    Thanks again
    Mike

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: InputBox Range - Process the contents Dynamically

    Hi Mike,

    Sometimes I start writing something and get carried away, especially if unexpected errors occur. The worksheet function you used is more efficient. I didn't think of it, since my personal bias is in favor of VBA before Worksheet Functions, because my background is in writing software in general, not in Excel.

    Application.Sum exists for backward compatibility with old code (and is probably slightly faster). Since it is old and apparently not recommended for new applications, that is probably why it doesn't show up in the Intellisense list.

    Application.WorksheetFunction.Sum is the newer syntax which will show up in the Intellisense list.

    For more details see: http://www.bettersolutions.com/excel...I114366552.htm

    Lewis

  5. #5
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: InputBox Range - Process the contents Dynamically

    Quote Originally Posted by LJMetzger View Post
    Hi Mike,

    Sometimes I start writing something and get carried away, especially if unexpected errors occur. The worksheet function you used is more efficient. I didn't think of it, since my personal bias is in favor of VBA before Worksheet Functions, because my background is in writing software in general, not in Excel.

    Application.Sum exists for backward compatibility with old code (and is probably slightly faster). Since it is old and apparently not recommended for new applications, that is probably why it doesn't show up in the Intellisense list.

    Application.WorksheetFunction.Sum is the newer syntax which will show up in the Intellisense list.

    For more details see: http://www.bettersolutions.com/excel...I114366552.htm

    Lewis
    Thank you for sharing your thoughts and the link!

+ 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: 02-19-2013, 04:30 PM
  2. Adjusting Range Contents Based on contents of Three other columns
    By VTHokie11 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2012, 01:55 PM
  3. [SOLVED] How to count process running time ( process not finished)
    By miao jie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2005, 06:06 AM
  4. [SOLVED] How to count process running time ( process not finished)
    By miao jie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-12-2005, 02:06 AM

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