• 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.

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?
Regards,

>>> use code - tags <<<
Code:
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
 

Attachments

  • martes.xlsm
    20.6 KB · Views: 2
  • martes.xlsx
    11.2 KB · Views: 2
Last edited by a moderator:
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.
 
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.
 
Hola Ana ! According to your attachment an Excel basics VBA demonstration as a beginner starter :​
Code:
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 !​
 
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 <<<

Code:
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
 
Last edited by a moderator:
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 !​
 
Back
Top