What I do wrong???

[crazyboy037]

Hi!

I'm from Spain and I'm tryng to emulate the buscarv function with VBA. I'm using the Vlookup property but it doesn't work. Appears the 2042 error. What I do wrong? I supose that could be something relationed about the format of te cells... but I'm not sure...

Dim Fecha As Date
Dim Disponible As Integer
Dim Disp As Variant
Value As Variant
Rango As Range

Sub PrevDisp_FMT_VLC()

Fecha = Worksheets("PREVISIONES Y PEDIDOS").Range("U29").value
Value = Fecha
Set Rango = worksheets("RESUMEN").Range("A6:BM:1000)

Disp=Application.Vlookup(value, Rango, 7, False)

If IsError(Disp) Then
Disponible =0
Else
Disponible = Disp
End If

Worksheets("PREVISIONES Y PEDIDOS"). Range("I32"). value = Disponible

End Sub

The macro retunrs into the Cell I32 a 0 and it's wrong... It should be other number...

 

[pdpereira]

You are right about double/long variable. I try with long and it also gives me the same error (as I was expecting).

It is really very strange.

Thank you anyway.

Hi,

It is worth adding a .Value so that VBA knows exactly what it is supposed to be doing:

If Cells(rowQCM, 3).Value = MaterialTag Then

Also, this should not be a problem, but I usually make row numbers Long as in:

Dim rowQCM as Long

They will never need to be fractional and they need to work with up to a million rows.

 

[FormR]

What is the value of cells(rrowQCM,3) when you get the error?

In the immediate pane with the code in debug what does this return?

? cells(rrowQCM,3).value

 

[FormR]

What is the value of cells(rrowQCM,3) when you get the error?

I need help to solve an Error2042 error in the code line below in bold.

Sound like you have #N/A errors in the range; try changing the section of the code below to:

   For rowMaterial = 2 To 50000
        FirstITRFound = True
        NumberOfQCFs = 0
        MaterialTag = Cells(rowMaterial, 1)
      ' Search for the Tag in the QCF sheet
        Sheets("QCM").Select
        For rowQCM = 2 To 50000
           If Not IsError(Cells(rowQCM, 3).Value) Then
                If Cells(rowQCM, 3) = MaterialTag Then
                    NumberOfQCFs = NumberOfQCFs + 1
                    QCFType = Cells(rowQCM, 18)
                        If FirstITRFound = True Then
                        FirstITRFound = False
                        Sheets("Material").Select
                        Cells(rowMaterial, 2) = QCFType
                            ElseIf FirstITRFound = False Then
                        MsgBox "FirstITRFound = :  " & FirstITRFound
                        Sheets("Material").Select
                        Cells(rowMaterial + NumberOfQCFs, 2).Select
                        Selection.EntireRow.Insert
                        ActiveCell.Value = QCFType
                    End If
                   'Sheets("Material").Select
                   MsgBox "QCFType found is:  " & QCFType
                   MsgBox "Number of QCFs found is:  " & NumberOfQCFs
                 End If
            End If
        Next
        MsgBox "Next Tag is:  " & Cells(rowMaterial + 1, 1)
    Next

 

[pdpereira]

That;s it. Thank you.
Besides, I was not selecting the right sheet (the selection was out of the For cycle instead of inside), so it was testing a sheet with #N/A cells.

Thanks all!

Sound like you have #N/A errors in the range; try changing the section of the code below to:

   For rowMaterial = 2 To 50000
        FirstITRFound = True
        NumberOfQCFs = 0
        MaterialTag = Cells(rowMaterial, 1)
      ' Search for the Tag in the QCF sheet
        Sheets("QCM").Select
        For rowQCM = 2 To 50000
           If Not IsError(Cells(rowQCM, 3).Value) Then
                If Cells(rowQCM, 3) = MaterialTag Then
                    NumberOfQCFs = NumberOfQCFs + 1
                    QCFType = Cells(rowQCM, 18)
                        If FirstITRFound = True Then
                        FirstITRFound = False
                        Sheets("Material").Select
                        Cells(rowMaterial, 2) = QCFType
                            ElseIf FirstITRFound = False Then
                        MsgBox "FirstITRFound = :  " & FirstITRFound
                        Sheets("Material").Select
                        Cells(rowMaterial + NumberOfQCFs, 2).Select
                        Selection.EntireRow.Insert
                        ActiveCell.Value = QCFType
                    End If
                   'Sheets("Material").Select
                   MsgBox "QCFType found is:  " & QCFType
                   MsgBox "Number of QCFs found is:  " & NumberOfQCFs
                 End If
            End If
        Next
        MsgBox "Next Tag is:  " & Cells(rowMaterial + 1, 1)
    Next