+ Reply to Thread
Results 1 to 3 of 3

Returning the row number of the option after the selected option in a userform combobox

  1. #1
    Registered User
    Join Date
    06-29-2014
    Location
    Adelaide, Australia
    MS-Off Ver
    2013
    Posts
    2

    Returning the row number of the option after the selected option in a userform combobox

    Hi guys,

    I'm working on a VBA userform that references searches a database of comparable rates for a project at work. Due to confidentiality issues I am unable to post the original file but I've made a quick mock-up that gives an example of the format of my data on a small scale. Column A is a list of units, column B has item categories and column C has the items within those categories. Basically, I've set up a userform with 3 combo boxes; combobox1 selects a project which actually dictates which worksheet is relevant, combo box 2 is a selection of the categories in Row B of that worksheet (without the empty rows between) and combobox C should generate an option for each of the items within that category. When you select the project, the category within that project and an item within that category, hit a "Check rates" command button and it searches the rows next to that item (rows D through ZZ have been allowed) and returns the maximum, minimum and average rates for the item.

    From the example you can see that the first item is in cell B2 so the first item in that category is ("C" & (category1row +1)). Similarly the last item in that category can be defined in terms of the next category heading in the list. In other words, I'm asking if anyone knows how to return the row number of the category heading after the selected category in a combo box. If I can get that row number, the last item in each category can be defined by ("C" & (category2row -1)).

    Any assistance you may be able to provide on this matter will be greatly appreciated and I'm happy to provide clarification if need be.

    Regards,
    Stephen
    Attached Files Attached Files

  2. #2
    Forum Contributor pareshj's Avatar
    Join Date
    05-20-2014
    Location
    MUMBAI
    MS-Off Ver
    2007 & 2010
    Posts
    447

    Re: Returning the row number of the option after the selected option in a userform combobo

    Hi rep67439fgs,

    Check the attached file.
    This is possible using MATCH function in excel.
    Just select the category from the drop-down and you will get the row number in cell G13.
    Hope this will help you.

    ExampleMacro.xlsx


    Regards,
    Paresh J
    Click on "* Add Reputation" as a way to say thanks

  3. #3
    Registered User
    Join Date
    06-29-2014
    Location
    Adelaide, Australia
    MS-Off Ver
    2013
    Posts
    2

    Re: Returning the row number of the option after the selected option in a userform combobo

    This is a good solution for returning the row number of the selected option but I'm actually after an integer output for row number of the category heading after the selected option and using VBA.

    If we use my attachment example, I want to print all of the items under the category of fruit. First I want to be able to return the row number of category heading fruit (which I've already managed) to define the top bound of the fruit item list. My problem comes when I try to define the bottom bound of the fruit items. I'm trying to determine code that will return the row number of the next category heading which in this case is veg. I can then use that row number to say that the end of the fruit item list is equal to the row number of the veg category heading subtract 1 row.

    That fact that I can't find this being done anywhere is a bit of red flag to me but seeing as I've put a fair bit of effort in already I'm hoping for a work-around.
    Last edited by rep67439fgs; 06-30-2014 at 08:17 AM.

+ 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] UserForm Initialization does not load items in ComboBox, Option values won't transfer etc.
    By Ambassador777 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-27-2014, 08:01 AM
  2. Replies: 2
    Last Post: 08-15-2013, 12:25 PM
  3. Activate Print option with a userform where selected Sheet check box option available..
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2013, 08:19 AM
  4. Userform Option Button to Show/Hide Another Option Button on same Userform
    By R_S_6 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-26-2010, 09:44 AM
  5. Option buttons: How to get the selected option from a group?
    By naddad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-21-2005, 01: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