Match function is malfunctining if variable is used as arg1

try ..

set MatB = WorksheetFunction.Match(choice(y), Columns("B:B"), 0)

upload sample file...

How is the array populated?

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.

:-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 you´ve 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 I´d love to as mentioned above) and that´s 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 you´re looking at the end of the code of setting those variables there are few if´s those are there first to copy the values at proper cell and the match function is there to prevent repetition (when for example you´ve 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 i´m 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) - that´s for the sample i´ve 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)

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.

Dim MatB As Variant ' allows MatB to be an error value

' other code

    Set MatB = Application.Match(choice(y), Columns("B:B"), 0) ' Application.Match should prevent a runtime error

    If IsError(MatB) Then
        MsgBox choice(y) & " not found."
    End If

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 i´ve explained all thus far....;-)...now im off to try out the last thing you´ve proposed to declare MatB as variant which i didnt have (had it as long since the output should be numeric) but I´ll give it a shot lets see what´s the outcome....brb

EDIT: so I´ve 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

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?

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 I´ll 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 you´re 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 you´ve chosen; "C" keeps the formating value -- u can see that the "jmeno" item is on the 1st and 3rd row...this shouldn´t 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 I´m hitting atm, any ideas (I don´t 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

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?

Hi,

I´ll 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 I´ll 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 else´s proposial at the top
3) MatB will be number at the end so I dont see how Variant solves the problem (and honestly I´ve tried redeclaring it as variant, string etc.) and nothing helped
4) worksheetfunction - as far as I´ve 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 - that´s 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 I´m 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 I´d 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

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.

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

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.