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.

Function Prev_week_delta_column_name(txt_header As Range, current_week As String, week_list As Range, empl_id As String, empl_list As Range) As String

prev_week = "Week " & Right(current_week, 2) - 1
prev_week_row = Application.Match(prev_week & empl_id, week_list & empl_list, 0) ' after this like the code stops


Prev_week_delta_column_name = ""

End Function

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 ?

Function Prev_week_delta_column_name(txt_header As Range, current_week As String, week_list_ID As Range, empl_id As String) As String

current_week_row = Application.Match(current_week & empl_id, week_list_ID, 0)
prev_week = "Week " & Right(current_week, 2) - 1
prev_week_row = Application.Match(prev_week & empl_id, week_list_ID, 0)


For Each cell In txt_header
    
    If Cells(current_week_row, cell.Column) <> Cells(prev_week_row, cell.Column) Then
        
        x = x & cell & "|"
        
    End If

Next cell

Prev_week_delta_column_name = x

End Function

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

    Dim x
    x = nr1list.Parent.Evaluate("match(""" & nr1 & nr2 & """," & nr1list.Address & "&" & nr2list.Address & ",0)")
    If IsNumeric(x) Then
        '  when match
    Else
        '  when no match
    End If