• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX/MATCH :VBA codes

Binaxxx

New Member
Hi,

I am looking for to change the below as VBA codes and make it dynamic :
I do not know where to start

=IFERROR(INDEX(PO_Detail!C:C,MATCH(1,(PO_Detail!A:A=VD_Backlog!A2)*(PO_Detail!B:B=VD_Backlog!B2),0))," ")

Please find attached a workbook with two tabs :

- VD_Backlog
_PO_Detail

The formula is within column C.

I'd like this formula to be generate through VBA and the cells will have only the "Value" found and not the formula


Many thanks for your help
 

Attachments

  • Test.xlsx
    17.1 KB · Views: 6
Using Scritping.Dictionary.

Code:
Sub Demo()
Dim poDic As Object
Dim poArr
Dim cel As Range

Application.ScreenUpdating = False

Set poDic = CreateObject("Scripting.Dictionary")

With Sheets("PO_Detail")
    poArr = .Range("A2:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With

For i = 1 To UBound(poArr)
    poDic(poArr(i, 1)) = poArr(i, 3)
Next

With Sheets("VD_Backlog")
    For Each cel In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Cells
        cel.Offset(, 2) = poDic(cel.Value)
    Next
End With

Application.ScreenUpdating = True
End Sub
 
Using Scritping.Dictionary.

Code:
Sub Demo()
Dim poDic As Object
Dim poArr
Dim cel As Range

Application.ScreenUpdating = False

Set poDic = CreateObject("Scripting.Dictionary")

With Sheets("PO_Detail")
    poArr = .Range("A2:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With

For i = 1 To UBound(poArr)
    poDic(poArr(i, 1)) = poArr(i, 3)
Next

With Sheets("VD_Backlog")
    For Each cel In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Cells
        cel.Offset(, 2) = poDic(cel.Value)
    Next
End With

Application.ScreenUpdating = True
End Sub

Hi CHIHIRO,

This is great, however the lookup does not mention the second criteria to generate the lookup. As a matter of fact the codes return a wrong value.
 
Ah, Column B. I missed that. See modified code (just the concatenation of Column B to A).

Code:
Sub Demo()
Dim poDic As Object
Dim poArr
Dim cel As Range

Application.ScreenUpdating = False

Set poDic = CreateObject("Scripting.Dictionary")

With Sheets("PO_Detail")
    poArr = .Range("A2:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With

For i = 1 To UBound(poArr)
    poDic(poArr(i, 1) & "," & poArr(i, 2)) = poArr(i, 3)
Next

With Sheets("VD_Backlog")
    For Each cel In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Cells
        cel.Offset(, 2) = poDic(cel.Value & "," & cel.Offset(, 1).Value)
    Next
End With

Application.ScreenUpdating = True
End Sub
 
Back
Top