+ Reply to Thread
Results 1 to 21 of 21

Match function is malfunctining if variable is used as arg1

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Match function is malfunctining if variable is used as arg1

    Hi,

    I wonder whats wrong with this piece, it returns 0 nomatter what:

    Please Login or Register  to view this content.
    choice(y) is a global variable and if i check on it in debug.print it shows what is expected, but match function is not...any tips?

    Best regards

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Match function is malfunctining if variable is used as arg1

    try ..

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    Hi,

    thx for respond, tried your code, it pops error with object request...:-/

    also I was browsing the other forum threads found that one with similar problem in regards of using variable in match not working but he had problem with the declaration difference (text VS number) since my variable is declared as variant (other is not supported or is not working as global) and the typename funcion says that the outcome is string i assume this one is not coliding right? (btw. searching text in text range)

  4. #4
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Match function is malfunctining if variable is used as arg1

    upload sample file...

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Match function is malfunctining if variable is used as arg1

    How is the array populated?
    If posting code please use code tags, see here.

  6. #6
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    here you go.

    Btw. the code in task is under frm_order userform...my code is kinda scattered all over the place (always where it is related) aswell as there are few tryouts and for now dead ends which are to be extended etc.

    sample.xls

  7. #7
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    Quote Originally Posted by Norie View Post
    How is the array populated?
    what array? you mean the variable one? from the userform outcomes.. I had insane idea to use indexed variables to complete my code but as for now It seems Im out of ideas how to handle it exactly so i had to backtrack a bit to easier solution which is to fill the output to spreadsheet (which will be than hiden to users) and ill come back to that idea later, now i wanna finish that app...so it seems Im doing too much fuss about the variables in the code where simple vars would sufice but even if i replaced that one which is causing all the havoc it didnt help and didnt work

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Match function is malfunctining if variable is used as arg1

    I think I might know what the problem is but I've not been able to replicate it as I don't quite know how I'm supposed to use the forms.

  9. #9
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    :-D yeah i can imagine, should have posted a little manual with it, sorry guys:

    here is the thing, if you click the first button which is copied there i believe twice, it triggers the first menu, where you select desired fields for the headers, than send it, you get another userform where there is only the list youve chosen, you can click through all of it to select proper formating and order of the fields (if you click lets say "tituly" another userform pops up - this one populates the global variables; here you select at what place the header should be and with what format and click "OK" this sets the variables and paste the value in cells - that paste is just for now since im not able to execute the code Id love to as mentioned above) and thats it.....just when you have set all the fields dont send the remaining userform or you get another error cause it goes to word app and does some more tricks there.....the thing is in the last sheet ("krit") there is the outcome of the users choice

    EDIT: if youre looking at the end of the code of setting those variables there are few ifs those are there first to copy the values at proper cell and the match function is there to prevent repetition (when for example youve set the format and then decide to change it, so that was my try on correcting the outcome in the field) but since the match is not working im unable to decide if there is already an entry with defined value or not.

    EDIT2: just to say where my issue pops up, its at the part where you try to set first set of variables (frm_order - userform; when you try to send it with OK it fails on the match function which is checking the repetition as explained above) - thats for the sample ive provided

    also another thing i wanted to say: at first fill up its empty (the columns to search within) so i thought it fails on null and therefore i made a errhandling routine to get over it and to be able to record outcomes in debut.print and so even after i had some values to go through for the match function it still didnt work and only 0 were recorded (which signinfies "no match" result)
    Last edited by SoulPrisoner; 09-03-2013 at 09:11 AM. Reason: Additional info

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Match function is malfunctining if variable is used as arg1

    I'll give it a try but I'll tell you what might be the problem - the array is being populated from a combobox/listbox so it's populated with strings, not numbers.

    That would be a problem if the column you were trying to find a match in had numeric data.

    Hard to tell if that's the case because, well, there's no data on any of the sheets apart from a header row on 'Input'.

    Another problem is that a match just isn't being found, perhaps because there's no data.

    If that's what's happening here's how you can deal with it.
    Please Login or Register  to view this content.
    Last edited by Norie; 09-05-2013 at 09:45 AM.

  11. #11
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    yap there are no data cause they get deleted on the menu initialization but that must be so every time its used there is a fresh set of variables. other data (that were used under the header) are of a personal character which is not to be shared....all the important data for this task are there trust me...;-)....if you click through as i explained above it will create few new entries on "krit" sheet and there raises the issue

    also the columns("B:B") refers to the range where only strings are - tried out with typename function set on the variable and on the cell itself always returns string

    btw. also tried to put "" to the match function (""" & choice(y) & """ ... ) didnt work either

    regarding no data thats what i explain in the edit2....i thought of that problem so i skipped first set of variables to get to the point where there are data but still flawed results

    Hope ive explained all thus far....;-)...now im off to try out the last thing youve proposed to declare MatB as variant which i didnt have (had it as long since the output should be numeric) but Ill give it a shot lets see whats the outcome....brb

    EDIT: so Ive tried to declare MatB as variant and the only result was that in debug print it gives nothing instead of 0 if i had no "set" before the actual code or
    object required
    error message with match. highlighted
    Last edited by SoulPrisoner; 09-03-2013 at 09:09 AM. Reason: providing test results

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Match function is malfunctining if variable is used as arg1

    Did you also change from WorksheetFunction.Match to Application.Match?

    By the way, """ & choice(y) & """ is definitely wrong

    PS Any chance you could upload a file with some sample, non-sensitive, bogus data?

  13. #13
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    yeah, tried that one aswell without succes.

    well i know it might look like there should be plenty of data but truth is, there arent, only list with data is the input one, the process is where Ill make some nice designed frontpage just as the background for the application and the krit sheet is for criteria (will be xlveryhidden at the end) so, you can write under the header whatever data you wish, they are not important atm since those will get in the game when i figure out the match problem until than they are useless....first the header needs to be sorted out and the header is not working cause the check for the duplicity (with the match function) is not working, so if a user format the first choice and than decides he wants different formating for it and reformate it, my code so far record both of the cases and keeps both of em which is wrong i need to overwrite the first one....and for that i need some kind of lookup function for it....as for me match seemed as the best way (which im not that sure atm though....:-/

    anyways thx for looking into it - Ill be off for almost whole day now, but when i get back to my pc ill dig in it once more...;-)

    best regards

    Soul

  14. #14
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    well, was quite busy lately so I didnt manage to get back to this up till now, anyways any new ideas? as for now still dont have the solution for the match function

    to replicate my issues, u can use the file above and follow this process:

    1) click "Button 2"
    2) select first 3 items ("jmeno", "prijmeni", "tituly") - it doesnt actualy matter if you choose different and / or more & less items, its just for instance
    3) click "OK"
    4) click on the first item ("jmeno")
    5) choose a value in combobox ("1" for i.e.) - again doesnt matter which atm.
    6) choose format ("Heading 2") - ...
    7) click "OK"

    Voala - 1st issue ("object required") - because of the word "set" before the variable "MatB" -- if removed another error: run-time error 1004 ("Unable to get the match property of the worksheetfunction class") that is cause because the range in which it is searching is empty; if on error resume next is placed before the variable to get past the initial error, because on the second roll there will be data Ill continue with instruction:

    8) remove "set"; add On Error Resume Next on the line above variable "MatB"
    9) set error handling back to default straight after "MatB"
    10) open "Immediate window" (ctrl+g)
    11) go through points 1) to 7)
    12) no error occurs now -- check on immediate window, there should be "0" and "jmeno" that is correct outcome
    13) click next item in the list ("prijmeni")
    14) choose from combobox and the format (which ever you like, doesnt matter) and click "OK"
    15) and again immediate window shows the item you have choosen and whether there is a match or not (value "0" says no match)
    16) click on "jmeno" item again (now youre setting formating for item that has been set already, so match should trigger now the value)
    17) choose whatever formating there and click "OK"
    18) check immediate window and it says "0" and "jmeno" which is NOT correct...the number should be 1 because item "jmeno" has been already formated and it was formated as 1st one so match should be on the first line
    19) stop the macro
    20) go to sheet "krit"
    21) columns "A" keeps your choises from the combobox; columns "B" keeps the item youve chosen; "C" keeps the formating value -- u can see that the "jmeno" item is on the 1st and 3rd row...this shouldnt happen, there should have been Match and 3rd row shouldnt have been recorded but 1st row should have been overwritten, but the match doesnt trigger and new rows is being pasted

    These are all the dead ends Im hitting atm, any ideas (I dont insist on match function, if anyone knows the way to bypass that function or other function which does the same job - i need the outcome of match to be able to get correct row overwritten with new input)

    Best regards

    Soul
    Last edited by SoulPrisoner; 09-05-2013 at 04:39 AM. Reason: Typo

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Match function is malfunctining if variable is used as arg1

    Soul

    Whatever you do don't add On Error Resume Next, unless you just want to hide the problem that is.

    You also shouldn't have Set - MatB is not an object.

    Actually that brings up something else, declare MatB as Variant not Long.

    Also replace WorksheetFunction.Match with Application.Match.

    You should do both of those things for ColA, ColB and ColC.

    The reason MatB is 0 in the Immediate Window is because that's it's default value.

    The reason Match fails is because there is no match - only one worksheet in the workbook has data, and it's not even that sheet that's active when the code runs.

    Which worksheet should Columns("B:B") refer to?

  16. #16
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    Hi,

    Ill take it one by one:

    1) i dont use resume next within my finished apps this is just go get through the initial error which will occure and which Ill have to solve but atm im not interested in that (it will be prolly done by the header in "krit" sheet)
    2) i know MatB is not object, but with that set matB that was someone elses proposial at the top
    3) MatB will be number at the end so I dont see how Variant solves the problem (and honestly Ive tried redeclaring it as variant, string etc.) and nothing helped
    4) worksheetfunction - as far as Ive tried both produced sam error and colA-C works fine so no need to replacing those (at least i dont see the reason why, if its working, is there any real issue with the worksheetfunction?)
    5) default value of 0 - thats actualy what im counting on with my conditions there and what i have tried to reasure with some of my other tries to make it work to set it 0
    6) and finaly we are getting to the last and the most important point where you prolly discovered the issue, and Im about to try it out, i forgot the sheet reference in the code in the match funcion and therefore its failing (at least i hope)

    in my language there is a saying "for the forest you dont see the trees" and Id say this is the example of it, all those variables above match function has it done properly and here i left that one out....:-( soo folish and so stupid...hope it works brb

    btw. just for a note, there cant be any more additional data in the sheets....there is the input from a user and then criteria sheet (where this match function should go) and thats it...and the criteria will be created upon the process described above...;-)

    brb

    EDIT: So that was it, now it works as it should...just cant believe i havent seen it in these three days or so....thx a lot for noticing...always something small and simple cause the worst nightmares....well thx for your time and patience

    best regards

    Soul
    Last edited by SoulPrisoner; 09-05-2013 at 10:37 AM. Reason: additional info

  17. #17
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Match function is malfunctining if variable is used as arg1

    3 Declaring it as Variant won't solve the problem but it will help with it.

    A Variant can be of any data type, even an error, so if the Match fails you won't get a run-time error but you can check, with IsError, MatB.

    4 When I declare as Variant and use Application.Match there's no run-time error but MatB does contain an error value.

    5 Not sure what you mean there.

    As for there being no more data, if that's the case the Match will fail every time.

  18. #18
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    Quote Originally Posted by Norie View Post
    3 Declaring it as Variant won't solve the problem but it will help with it.

    A Variant can be of any data type, even an error, so if the Match fails you won't get a run-time error but you can check, with IsError, MatB.

    4 When I declare as Variant and use Application.Match there's no run-time error but MatB does contain an error value.

    5 Not sure what you mean there.

    As for there being no more data, if that's the case the Match will fail every time.
    I know ive marked as solved and therefore i should not write in here anymore but i feel like this rly needs clarification:

    5) if you checked on the conditions just below the variable declaration there is a matB <>0 and matb =0 which is the case where im counting on default value of 0

    and as for the last statement it wont fail, cause the data where it should match will be created before the match function will be triggered....you run the userforms, you create a list of criteria in which the match function gonna look for repetitions

  19. #19
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Match function is malfunctining if variable is used as arg1

    If you use Variant you can use IsError instead of checking for 0.

  20. #20
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Match function is malfunctining if variable is used as arg1

    yap now i see your point when i corrected that wrong reference. will have to look at it a bit more now to see it its ok with variant though, dont wanna have a problem somewhere along the path with value vs string (numeric vs text)

  21. #21
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Match function is malfunctining if variable is used as arg1

    Why would you have a problem like that?

    MatB will hold either a long integer or an error value, the former if a match is found, the latter if no match is found.

+ 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] MATCH function with a variable as lookup_value?
    By mccalle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-23-2013, 07:37 AM
  2. Adding a variable to index & match function using VBA
    By prestopr in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-07-2013, 05:48 PM
  3. Replies: 2
    Last Post: 11-05-2011, 03:26 PM
  4. Specifying Arguments in Run ([Macro], [arg1],[arg2]...)
    By Kaigi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-09-2010, 06:35 PM
  5. Using Match function output as a variable
    By iii in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-28-2009, 06:01 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