Match a concatenated value in a concatenated list in a vba function

Hello,

I am using an match fomula to get a row: Match(nr1&nr2, nr1list&nr2list, 0) -- this works perfectly in excel.

But once i try this same in an excel vba functions i get an error and do not know how to fix it.

dim nr1, nr2 as string
dim nr1list, nr2list as range

nr_row = Worksheet.Function.Match(nr1 & nr2 , nr1list & nr2list , 0)

I was thinking of using a match or find to get the row, because my list can go up to 1000 rows and i need the function in every row. and was thinking if i use a "for each" that will just slow down the file.

Does anyone have a idea ?

Thank you.

Hi,

use Application.Match …

Does not work, the function exits because of an error.

The variable must be Variant and you must control its result via IsError or IsNumeric VBA function …

This is what it starts like

What the function needs to do.
1. From current week, get previous week.
2. Find the employee from previous week
3. See if the info from previous week's row matches with current weeks row, if not get column header name.

Please Login or Register  to view this content.

I managed to finish the function but needed a helper column that concatenates the 2 column info.

Is there a way to do it without the helper column ?

Please Login or Register  to view this content.

Yes !

Follow the top yellow banner in order to attach a sample workbook and well elaborate your need …

I posted the whole code above, why do u need a workbook ?

'cause we are not on some mind readers forum ! …

So try under VBA the same formula than in the helper column but for both columns range within a variable array …

If i do not use the helper column ( concatenate 2 cell, and fill down ), i need something like this: prev_week_row = Application.Match(prev_week & empl_id, week_list & empl_list, 0) , but this does not work.

If i create a concatenated column of week_list and empl_list => i get week_list_ID and then i can use the line: prev_week_row = Application.Match(prev_week & empl_id, week_list_ID, 0) , this works perfectly.

My question was is there a way to concatenate the 2 lists in VBA that would not slow down the file ? ( not to loop true each item )

Originally Posted by Rolly_Sefu

Hello,

I am using an match fomula to get a row: Match(nr1&nr2, nr1list&nr2list, 0) -- this works perfectly in excel.

Then

Please Login or Register  to view this content.