Application.WorksheetFunction.Match error

[Deniouz]

I get this error at

Please Login or Register  to view this content.

What is wrong with this line?


Runtime error '1004'
Unable to get the Match property of the WorkSheetFunction class at

Please Login or Register  to view this content.

[Ochimus]

Deniouz,

Unless you have defined "ColumnA" as a defined cell or value somewhere else, the Code is not defining what to find within the "rng", (E.g TargetRow = WorksheetFunction.Match(Range("D2").Value, rng, 0)

If you just want the next free row ("n"):

With ActiveSheet
n = .Cells(.Rows.Count, "A").End(xlUp).Row+1
end with


Ochimus

[Deniouz]

Hello Ochimus. My ColumnA is the name of the combobox. How should I get the value from the combobox to be used in the match application?

[p45cal]

Assuming ColumnA is a value (whatever's been chosen in the list box - it should be if the listbox is an activeX listbox) then if the value's not to be found in rng then using

Please Login or Register  to view this content.

[note that the line above does NOT contain WorksheetFunction]
will not throw an exception; instead TargetRow will contain an error. So then all you need to do is check if it contains an error:

Please Login or Register  to view this content.

[Deniouz]

It doesn't work. I get the error "method range of object worksheet failed. I think it has something to do with the ColumnA's row source. Currently it is empty. My TargetRow is supposed to target the exact row of the selected value in the combobox. How do I do that? I am trying to use the userform to edit values in the worksheets.

[hrlngrv]

You also need to declare TargetRow as Variant in order for it to hold either Long or Error values. OP had declared it as Integer, which is almost always a mistake. Integer as VBA defines it is an outdated artifact of 16-bit computing, which went away by the mid-1990s.

[Deniouz]

It still doesn't work. The Application.Match could run after setting as variant. However, it is not detecting the value in ColumnA. By the way, the table is a listobject. Did I do something wrong?

[hrlngrv]

Try this. Supplement as needed with declarations and variable initializations you didn't include in your original code fragment.

Please Login or Register  to view this content.

[Deniouz]

It still does not work. No errors but the data keyed into the userform does not update the tables. The edit button is at sheet AAA.

[Deniouz]

Solved. Would have to use:

Please Login or Register  to view this content.

to convert the variant into integer

and

Please Login or Register  to view this content.

to maintain the value from another userform