VBA result "Not found"

[Ana Luna]

Dear all,
The vba code that I attached below returns "Not found" . I have checked the code that is inside the buttom several times but with no results.
martes.xlsm has to take the values in column C (Facturacion) from the excel file martes.xlsx . But the taken value must match with the column A (period) and column B(cod). Take note that the values in column A and B are repeated.
I have also tried Vlookup but It seems that Vlookup only can match with one column but no with two.

Please, someone could help me on this?
Regards,

>>> use code - tags <<<

Sub indexmatch()

    Dim wbLibroOrigen As Workbook
    Dim wsHojaOrigen As Worksheet
    Dim wbLibroDestino As Workbook
    Dim wsHojaDestino As Worksheet
    Dim lookup_value As String
    Dim lookup_range As Range
    Dim match_range As Range
    Dim result As Variant
    Dim Ruta As String
    Ruta = "C:\martes.xlsx"
    Dim i As Integer


    'Datos Destino
    Set wbLibroDestino = Workbooks(ThisWorkbook.Name)
    Set wsHojaDestino = wbLibroDestino.Worksheets("Sheet1")

    'Datos Origen
    Set wbLibroOrigen = Workbooks.Open(Ruta)
    Set wsHojaOrigen = wbLibroOrigen.Worksheets("Sheet1")

    lookup_value = wsHojaDestino.Range("B2").Value & wsHojaDestino.Range("A2").Value
    Set lookup_range = wsHojaOrigen.Range("B2:B7")
    Set match_range = wsHojaOrigen.Range("A2:A7")
    On Error Resume Next
    result = Application.WorksheetFunction.Index(wsHojaOrigen.Range("C2:C7"), Application.WorksheetFunction.Match(lookup_value, Union(lookup_range, match_range), 0), 0)
    If Err.Number <> 0 Then
        result = "Not found"
        Err.Clear
    End If
    On Error GoTo 0
    wsHojaDestino.Range("C2").Value = result
End Sub

 

[vletm]

Ana Luna
Have You tested to use CLng( 'Your_dates' ) instead using 'raw' 'Your_dates'?
... for me, if try to Match with dates, it works.

 

[Ana Luna]

Ana Luna
Have You tested to use CLng( 'Your_dates' ) instead using 'raw' 'Your_dates'?
... for me, if try to Match with dates, it works.

Thank you for your reply, could you please tell me how to insert CLng?, the column where the dates are is a range A2:a7, So, I dont know how to convert it to long.

 

[vletm]

Ana Luna
If basic Match with Dates then something like below
=Match(CLng(Your_date),Range(Your_Match_Range),0)
So, maybe Your lookup_value = wsHojaDestino.Range("B2").Value & CLng(wsHojaDestino.Range("A2").Value)
could solve like this ...
and
then You could us it as You've used in in Your Match.
You'll know it soon.

 

[Ana Luna]

Thanks again but it still returns "not found".
I will keep trying something different.
Thank you!

 

[Marc L]

Hola Ana ! According to your attachment an Excel basics VBA demonstration as a beginner starter :​

Sub Demo1()
  Const F = "C:\Users\gortueta\OneDrive - Deloitte (O365D)\Desktop\Analytics\PowerApps\feb\martes.xlsx"
    Dim W, V, L&, R
        If Dir(F) = "" Then Beep: Exit Sub
    With [A1].CurrentRegion.Columns
        W = Evaluate(.Item(1).Address & "&" & .Item(2).Address)
       .Item(3).Offset(1).ClearContents
    End With
        Application.ScreenUpdating = False
        Workbooks.Open F, 0
        V = [A1].CurrentRegion.Columns("A:C").Value2
        ActiveWorkbook.Close False
    For L = 2 To UBound(V)
        R = Application.Match(V(L, 1) & V(L, 2), W, 0)
        If IsNumeric(R) Then Cells(R, 3) = V(L, 3)
    Next
        Application.ScreenUpdating = True
End Sub

Te gusta ? So thanks to click on bottom right Like !​

​

 

[Ana Luna]

Oh! that answer is better than I could imagine. Thank you Ninja.
I am trying to write the opposite code.This is: return the same info from the marco file to the excel file.
It seems that there is an error after the Next stament that I cant figure it out.

>>> as You've noted <<<
>>> use code - tags <<<

Sub Demo2()
    Dim F$, W, V, X, L&, R
        F = ThisWorkbook.FullName:  Mid(F, Len(F), 1) = "x"
        F = "C:file.xlsm"
        If Dir(F) = "" Then Beep: Exit Sub
    With Workbooks.Open(F).Worksheets(1).Range("A2", [B1].End(xlDown)):  W = Evaluate(.Columns(1).Address & "&" & .Columns(2).Address):  End With
        ReDim V(1 To UBound(W), 0)
        Debug.Print UBound(V)
    With Application
       .ScreenUpdating = False
        X = [A1].CurrentRegion.Columns("A:C").Value2
        'ActiveWorkbook.Close False
    For L = 2 To UBound(W)
        R = .Match(X(L, 1) & X(L, 2), W, 0)
        If IsNumeric(R) Then V(R, 0) = X(L, 3)
    Next
   End With
    With Application
        ActiveWorkbook.[c2].Resize(UBound(V)) = V
        .ScreenUpdating = True
  
    End With

End Sub

 

[Marc L]

As the way you have modified the file name can't never work on my side, like the other modifications …​

As for the 'opposite' my original demonstration just needs to replace obviously IsNumeric with IsError !​

​