• 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


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

VBA result "Not found"

Ana Luna

New Member
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?

>>> 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"
    End If
    On Error GoTo 0
    wsHojaDestino.Range("C2").Value = result
End Sub


Last edited by a moderator:

Ana Luna

New Member
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.


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

Marc L

Excel Ninja
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)
    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)
        Application.ScreenUpdating = True
End Sub
Te gusta ? So thanks to click on bottom right Like !​

Ana Luna

New Member
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)
   End With
    With Application
        ActiveWorkbook.[c2].Resize(UBound(V)) = V
        .ScreenUpdating = True
    End With

End Sub
Last edited by a moderator:

Marc L

Excel Ninja
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 !​