Index match to VBA copy the found entire row

**Sjaaktrekhaak** · 10-19-2009, 11:05 AM

Hello,

I'm having troubles with getting the index match function into vba.
And when the row is found get it to be copied to de worksheet.

the formula i want to get into vba is:

PHP Code:


=INDEX(Database!D$2:D$2000;MATCH($A2&" "&$C2&" "&$G2;Database!$A$2:$A$2000&" "&Database!$C$2:$C$2000&" "&Database!$G$2:$G$2000;0))

It compares three different cells which have to match.
this will give me the row i want to copy to the sheet.

Does somebody have an idea to get this done.

many thanks bram

**Richard Buttrey** · 10-19-2009, 11:26 AM

Hi,

If your question is how do you get VBA to add the function, then you need to modify it as follows

Please Login or Register  to view this content.

i.e. where you have literals such as a space " ", you need to include the literal in its own pair of quotes, e.g. " " becomes "" ""

HTH

**DonkeyOte** · 10-19-2009, 11:37 AM

When adding formulae via VBA use , delimiter rather than ; regardless of client locale
(native XL will translate... though I'm not sure this holds for ATP functions (not used here)).

**Sjaaktrekhaak** · 10-20-2009, 03:22 AM

I made an example of the sheet i have now to give a better view of what i want to achieve.

I have a userform for putting data into the database.
But most of the entry will be the same only the revision will change.
So I made a check for most of the values that they will fill in automaticly.

If you enter de class item en sheet number of an entry which is already in the database the rest is filled in.

It put the values of class item en sheet in sheet "index match" where i have the other cells filled with the index match formulas to get al the information.
But if i can find the row en copy that row whitch matches one formula of index match it would be much faster I think.

In the example it is al lot faster then in my real worksheet so i was wondering if this proces could be speeden up a bit by losing a lot index match formulas which have to search the hole database.

I hope this will give a better view of what I want to achieve.

Thanks Bram