+ Reply to Thread
Results 1 to 8 of 8

Finding row number using Match then assigning to variable

  1. #1
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Finding row number using Match then assigning to variable

    Hi all,

    I have the below code which I think is almost there but I can't seem to get it to work. I receive a Type Mismatch error on the formula.

    I have a userform with a combobox. When the user selects a value from this combobox I want to be able to show which row their selection is in (within range A1:A100 on sheet1 - a range with a header).


    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Finding row number using Match then assigning to variable

    You are assigning a string to a variable declared as an integer.

    whichrowselected = "=MATCH(FindValue,A1:A100,0) + ROW(A1:A100)-1"

    That would be seen as a string, not the result of the MATCH formula


    I think what you need to look into is
    Please Login or Register  to view this content.
    Last edited by Speshul; 08-26-2014 at 08:19 AM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Finding row number using Match then assigning to variable

    maybe
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Finding row number using Match then assigning to variable

    Quote Originally Posted by JosephP View Post
    maybe
    Please Login or Register  to view this content.
    Yeah that one, but Application.WorksheetFunction.match

  5. #5
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: Finding row number using Match then assigning to variable

    Brilliant. Thank you everyone. Joe, yours worked fine with Speshul's addition.

    Thank you.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Finding row number using Match then assigning to variable

    Quote Originally Posted by Speshul View Post
    Yeah that one, but Application.WorksheetFunction.match
    no. :-) if you use worksheetfunction.match you must handle run-time errors if the value is not found; application.match will return an error value that is trappable using iserror as I wrote

  7. #7
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Finding row number using Match then assigning to variable

    Quote Originally Posted by JosephP View Post
    no. :-) if you use worksheetfunction.match you must handle run-time errors if the value is not found; application.match will return an error value that is trappable using iserror as I wrote
    ? I was under the impression that match was a worksheetfunction specifically, not a vba application function


    Please Login or Register  to view this content.
    I mean as far as I know application.vlookup doesn't work, you need to use application.worksheetfunction.vlookup in there

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Finding row number using Match then assigning to variable

    all worksheetfunction methods are also available as methods of application. the difference is that application.worksheetfunction.vlookup will raise a run-time error which you've gotta handle with an On Error statement but application.vlookup will return an error value (as in a cell) which you trap by assigning to a variant and testing with IsError. The application.function_name version does not provide intellisense but does work albeit with a marginally higher overhead than the worksheetfunction version

    more reading here http://www.cpearson.com/Excel/Callin...ionsInVBA.aspx
    ;-)

+ 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. Finding a duplicate and assigning a set number
    By maddy45632 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2013, 07:08 PM
  2. Replies: 4
    Last Post: 02-19-2013, 02:19 PM
  3. Replies: 7
    Last Post: 05-17-2010, 08:22 AM
  4. Finding Max of multiple cells and assigning the value to a variable
    By t3chn0babbl3 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2009, 04:23 AM
  5. Assigning a Row Number to a variable in a macro
    By Neil07979 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-19-2009, 08:17 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