Match two values with two values on another sheet and return value from third column

  • Re: Match two values with two values on another sheet and return value from third col



    Many thanks. That's Great.

  • Re: Match two values with two values on another sheet and return value from third col


    I've been trying to adapt the code to work in the actual workbook and always come up with the same error - 'Runtime error 1004 Unable to get the Match property of the WorkSheetFunction class'.


    I have played with the original 'Slot_Example' wkbk, changing the code moving data around and everything I can think of and it still works fine! When I try it in my wkbk it breaks! There are a dozen or so other macros, a few other sheets and a datamodel but I have spent pretty much all day trying to get this to work and my brain is completely fried right now.


    Here's what the code looks like now which works fine on 'Slot_Example' wkbk with the names changed




    Here's the workbook that has the exact layout that I need - ozgrid.com/forum/core/index.php?attachment/73438/


    :hammerhe:

  • Re: Match two values with two values on another sheet and return value from third col


    It probably has something to do with a structural difference between the sample you provided and your actual workbook.


    See post #2.


    This is why we try to get people to just submit their actual workbook with desensitized data.


    I'll have a look at it when I get a chance.

  • Re: Match two values with two values on another sheet and return value from third col


    Re: I have no idea why the same code works in one wkbk and not on the other.
    I think I might have an idea.
    In Post # 15 you say: "it's pretty much the same set up but with extra columns here and there."
    In other words, totally different.


    If you attach a workbook that is the same as the one you're working with and a proper explanation to go with it I am sure that you would have a working suggestion in no time flat.
    You have to remember that you know what you want and the people trying to help you for free at their time do not. Therefor it is up to you to take some time and explain your wishes properly. Just like you would to a child not knowing anything!!!!!

  • Re: Match two values with two values on another sheet and return value from third col


    Ok guys I understand I'm poor at using this forum and I'm sorry if I've wasted any of your time, I'm not a forum using type of guy nor a techie or code type of guy! I do appreciate your help and efforts, I guess I've frazzled my brain the last few days with staying up to 3-4 am at times trying to find a solution.


    Basically skywriters code does exactly what I want. On the 'Today' sheet select a slot from the slicer then decide which ones will be a bet or not, a 1 or 0 in the yellow bet column. Click the red 'Bet' button to run the code. Select the next slot and repeat til all slots are done. This will populate the 'Bet' column on the 'Qualifiers' sheet. This is done by matching the slot number in E2 and the text in the qualifier column in 'Today' with the slot number and qualifier text in the columns in 'Qualifiers' sheet.
    I can then click the 'Betsender' button on 'Control' which sends today's bets (all the rows that contain a 1 in the bet column) to a csv file that can be uploaded into a bot.


    I've tried repeatedly to upload a file today but it always fails. The size is 2.24mb. It gets to 100% then the webite gives me a fail notification. The last file I uploaded has the same layout as what I use, the only difference being is the selections generated in 'Today' are done so using a dynamic array formula. as opposed to being just values or text. I've tested the code without the formulas but still get the same error.


    Not sure what I can do next.

  • Re: Match two values with two values on another sheet and return value from third col


    Currently I am on Excel 2003.
    You talk about "slots". I don't see the word "slot" anywhere.
    Do you have to copy/paste one at the time or can you fill in all your so called slots and copy/paste in one go as I did in my attachment a few days ago?
    Am I correct In thinking that you want to transfer data from "Today" sheet to "Qualifiers" sheet?
    Where does the last sentence, "I can then click the "Betsender" button ..........", fit in with your current requirement?

  • Re: Match two values with two values on another sheet and return value from third col


    Probably won't work on 2003, this is 2016 version using powerpivot. Slots are what I call the numerical reference for a system, they have a nickname too. Betsender is irrelevant for this code, I'm just describing the purpose of my requisition really. There's a slicer called slots that will show the selected slots qualifiers for that day when clicked. No C&P needed


    from previous post


    Basically skywriters code does exactly what I want. On the 'Today' sheet select a slot from the slicer then decide which ones will be a bet or not, a 1 or 0 in the yellow bet column. Click the red 'Bet' button to run the code. Select the next slot and repeat til all slots are done. This will populate the 'Bet' column on the 'Qualifiers' sheet. This is done by matching the slot number in E2 and the text in the qualifier column in 'Today' with the slot number and qualifier text in the columns in 'Qualifiers' sheet.
    I can then click the 'Betsender' button on 'Control' which sends today's bets (all the rows that contain a 1 in the bet column) to a csv file that can be uploaded into a bot.

  • Re: Match two values with two values on another sheet and return value from third col


    I think I've found the problem even though I don't understand why the problem occurs. The ranges for each sheet must start in the same column on each sheet to avoid the match error. The difference in range sizes makes no difference just as long as they start in the same column. Does anyone know why this is??


    This code now works fine



    Many thanks to skywriter and jolivanes for your help, hope you have a great weekend!

  • Re: Match two values with two values on another sheet and return value from third col


    Quote

    Does anyone know why this is??


    The answer is in your observation.


    A lot of times when there are errors in code people tend to look at the problem as if code had a brain.


    Code is a robot, it does exactly what you tell it to do.


    It's the human who has to figure out the difference between what he thinks he's telling the code to do and what he is actually telling the code to do.


    The code was told to look at data in a certain column and that's exactly what is happening, so the explanation is in your observation.


    Quote

    ranges for each sheet must start in the same column on each sheet to avoid the match error

  • Re: Match two values with two values on another sheet and return value from third col


    Quote

    maybe it's another vb quirk.


    What's the quirk?


    If you write a formula to add numbers in two columns and arbitrarily decide to start putting the numbers in a different column without changing the formula, how is that a quirk?