+ Reply to Thread
Results 1 to 4 of 4

Assign Match result to a variable

  1. #1
    Registered User
    Join Date
    04-03-2020
    Location
    Belfast
    MS-Off Ver
    2016
    Posts
    2

    Smile Assign Match result to a variable

    Hi all,

    I am trying to assign the result of a match function to a variable, as I will need to use it several times and don't want to run the function repeatedly.

    I thought the below code would do this, but I receive the Run-time error '438' : Object doesn't support this property or method.

    code:

    Please Login or Register  to view this content.
    Any suggestions will be appreciated! Thanks
    Last edited by Pepe Le Mokko; 04-03-2020 at 10:01 AM. Reason: Tags

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,509

    Re: Assign Match result to a variable

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I did it for you this time. Please read forum rules. Thanks
    I'm a newbie with PQ, so if my solution can be improved, please let me know. Thanks

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,233

    Re: Assign Match result to a variable

    Remember that this is VBA and not Excel, so we cannot use the same syntax we use in Excel. Worksheets(sheetname) is a reference to an entire worksheet. VBA does not know how to concatenate a worksheet object with the text string "!J"&i. Also note that "Allcodes!B:B" is just a text string here and not a range reference.

    It looks like your intention is to search for the value in Ji of the worksheet identified by the value of sheetname. I would probably use the range.value or range.value2 properties to extract that value. Something like ...Match(ThisWorkbook.Worksheets(sheetname).Cells(i,10).Value, ThisWorkbook.Worksheets("Allcodes").Range("B:B"), 0).

    I find that a lot of work with VBA in Excel is about accessing ranges, so there could be some value in familiarizing yourself with the Range object, how to refer to a specific range object, and the things you can do with/to a range object: https://docs.microsoft.com/en-us/off....range(object)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    04-03-2020
    Location
    Belfast
    MS-Off Ver
    2016
    Posts
    2

    Re: Assign Match result to a variable

    That was extremely helpful, thank you very much for your insight!

+ 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] User defined type: assign a variable to another variable
    By BNCOXUK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2019, 10:51 AM
  2. Setting variable equal to match result- error 2042
    By Feethurt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2019, 03:21 PM
  3. [SOLVED] How to extract a piece of a string variable, and assign to a new variable
    By jmccoughlin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-04-2015, 11:50 PM
  4. [SOLVED] Assign entire row to variable, how do you paste variable values in a new workbook?
    By tv69 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2013, 08:01 PM
  5. Replies: 3
    Last Post: 04-23-2013, 03:46 PM
  6. assign the result of countif to a variable in VBA
    By lima in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-22-2010, 05:19 PM
  7. Assign a SQL Query result to a VB Variable
    By olobraecky in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2007, 08:45 AM

Tags for this Thread

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