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

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

I think you have a typo in your code because it won't compile like that.
However, I suspect the main problem is that you are looking up dates. In which case you need to put a CLng() round your value, For example:

Set Rango = worksheets("RESUMEN").Range("A6:BM1000")
Disp=Application.Vlookup(CLng(value), Rango, 7, False)

 

[crazyboy037]

Perfect!! Thanks a lot!! It has worked at the first time!! :D :D :D :D :D :D

 

[RickXL]

No problem.

Thanks for the feedback.

 

[pdpereira]

Hi all,

I need help to solve an Error2042 error in the code line below in bold.
I think it should be easy but I can't find the problem. Probably I am just using the wrong type of variable.

The message that appears in my screen is:
Run-time error '13'
Type mismatch.

Thanks in advance.

________________________________

Sub A_QCF_TYPE()
'
'
'    Sheets("Material").Select
'    Sheets("QCM").Select


   Dim FirstITRFound As Boolean
   Dim MaterialTag As String
   Dim QCFType As String
   Dim NumberOfQCFs As Integer
   Dim CheckingTagFromQCM As String
      
   ' workbook must have the material sheet from takeover as the first sheet and QCM sheet from takeover as the 2nd sheet
   ' these two sheets will be renamed to "Material" and "QCM"
   Sheets(1).Select
   Sheets(ActiveSheet.Name).Name = "to be renamed 1"
   Sheets(ActiveSheet.Name).Name = "Material"
   
   Sheets(2).Select
   Sheets(ActiveSheet.Name).Name = "to be renamed 2"
   Sheets(ActiveSheet.Name).Name = "QCM"
   
   ' Creates a new column to put the QCF Types
   Sheets("Material").Select
   Range("B2").Select
   Selection.EntireColumn.Insert
   
   ' Searches for the several QCF Types for each Tag of Material
   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 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
              
        Next
        
        MsgBox "Next Tag is:  " & Cells(rowMaterial + 1, 1)
    Next
End Sub

 

[Michael M]

Don't have Excel at the moment, but you haven't dimmed "rowQCM" as Long

 

[pdpereira]

Don't have Excel at the moment, but you haven't dimmed "rowQCM" as Long

Sorry.
I forgot to mention the public variable declaration:

Public rowMaterial As Double
Public rowQCM As Double

(rest of the code as written above )

Thanks.

 

[Michael M]

Runs Ok for me....if you run it manually, where is the error line ??

 

[pdpereira]

Runs Ok for me....if you run it manually, where is the error line ??

The type mismatch error occurs in this line:
If Cells(rowQCM, 3) = MaterialTag Then

 

[RickXL]

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.