Application.Worksheetfunction.match ERROR

[Guido Meng]

Hi!

I'm using a worksheet function in a piece of VBA code to get the column number of an specific date. It seems that the Match Function is unable to find the date even though the Date does exist in a column in the First row of the sheet "Lecturas". I've tried using the Match Function in Excel itself and the capturing the cell value t use it in mu code. This works but I'd rather be able to come up with this value using code only. The code I'm using is as follows:

Private Function BuscaColumna() As Long


    Dim Columna As Long
    
    Dim Fecha As Date
    
    
    With Worksheets("Ingreso Lecturas")
    
        Fecha = DateValue("01/" & .Cells(4, 27) & "/" & Str(.Cells(5, 27)))
    
    End With
    
    
    With Worksheets("Lecturas")
        
        Columna = Application.WorksheetFunction.Match(Fecha, .Range(.Cells(1, 3), .Cells(1, .Range("1:1").Rows.Count)), 0)   <---------- THIS IS WHERE THE PROBLEM OCCURS
        
    End With
    
    BuscaColumna = Columna

End Function

Thanks!

[Norie]

Try this.

Private Function BuscaColumna() As Long


    Dim Columna As Variant
    
    Dim Fecha As Date
        
    With Worksheets("Ingreso Lecturas")
    
        Fecha = DateValue("01/" & .Cells(4, 27) & "/" & Str(.Cells(5, 27)))
    
    End With
    
    With Worksheets("Lecturas")
        
        Columna = Application.WorksheetFunction.Match(CDbl(Fecha), .Range(.Cells(1, 3), .Cells(1, .Range("1:1").Rows.Count)), 0) 

    End With
    
   If Not IsError(Columna) Then
    BuscaColumna = Columna
   End If

End Function

By the way, why are you using code for this?

Why not use the worksheet functions MATCH and DATEVALUE?