CODEWITHSUNDEEP
excelvba
P.b
P.b

Reputation: 11468

Excel VBA get row numbers in range matching multiple criteria

I am trying to get a match of the row number with a certain condition in my first sheet Sheet1 and copy the format and paste it in the second sheet Sheet2 where the lookup value matches.

I have data in Sheet1 like this:

A B
Title condition
1 color1 A
2 color2 B
3 color4 C
4 color2 D
5 color1 A
6 color2 D

And in Sheet2 like this:

like this:

A B C
Title condition
1 color1 A
2 color2 D
3 color5 C
4 color2 B

I came to the following code for accomplishing this for the first column matches (result in column C):


Sub colorcopy()

 

Dim ws1 As Worksheet, ws2 As Worksheet

Dim rng1 As Range, rng2 As Range, lookup1 As Range, lookup2 As Range, result As Range

Dim mtch As Variant

Dim i As Long, lr1 As Long, lr2 As Long

 

Set ws1 = Sheet1

Set ws2 = Sheet2

 

With ws1

    lr1 = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

 

With ws2

    lr2 = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

 

Set rng1 = ws1.Range("A1:A" & lr1)

Set rng2 = ws1.Range("B1:B" & lr1)

 

    For i = 1 To lr2

 

Set lookup1 = ws2.Range("A" & i)

Set lookup2 = ws2.Range("B" & i)

 

Set result = ws2.Range("C" & i)

   

    mtch = Application.Match(lookup1, rng1, 0)

   

    If Not IsError(mtch) Then

        ws1.Range("A" & mtch).Copy result

    End If

   

    Next i

 

End Sub

But then I wanted the code to work with multiple conditions (2).

I want to copy the cells of where there’s a match in data from both column A and column B from lookup values in Sheet1 to match in Sheet2.

In formula form this would be accomplished like =MATCH(1,INDEX((Range1=Value1)*(Range2=Value2),),0) for the first row. This seems not possible to be used in Application.Match, but I get stuck here.

Color of sheet1 is like this:

pic1

Result of current code is like this:

pic2 Wanted result is:

pic3

Upvotes: 1

Views: 1674

Answers (1)

JvdV
JvdV

Reputation: 75860

Here is an older post on the matter, however (as I previously mentioned in a comment) it's rarely a good idea to concatenate values when you lookup/match any value. Rather use the following formula:

=MATCH(1,INDEX((Range1=Value1)*(Range2=Value2),),0)

In your current code, this would lead to a single change:

mtch = Application.Match(lookup1, rng1, 0)

Into;

mtch = ws1.Evaluate("MATCH(1,(A:A=""" & lookup1.Value & """)*(B:B=""" & lookup2.Value & """),0)")

Note1: Since this is an array-formula it may be process-heavy and computing time can become substantial

Note2: The worksheets-formula holds a nested INDEX() which is no longer needed since Evalute() will handle all formulae as though they were CSE-entered.

Upvotes: 2

Related Questions