VLOOKUP IN EXCEL using a script written on notepad

[jissididi]

So I am writing a script to compare two excel files.

I'm using a For loop in the first workbook to get the references I want to find in the second workbook (6450 rows long so that no For loop, way to slow)

I have been looking for some way to use the VLOOKUP thing but i could not make it work Here is the code :

For i = 7  to numLines ''numLines is the number of used lines of the first workbook
 
    If '''test to get out of the LOOP
objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("H"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("L"&i)="" Then
 
        i = numLines
 
    Else '' here i get the reference (the 6 first digits of the first workbook and I try to find it in the second)
 
        If objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)<>"" Then
 
            Reference = Mid(objExcel.Workbooks(Str1).Sheets(1).Range("D"&i),1,6)
 
            Set table_lookup = objExcel.Workbooks(Str1).Sheets(1).Range( "C1:C" & numLines2 )
            cell = objExcel.Workbooks(Str2).WorksheetFunction.vlookup(Reference, table_lookup, 0, False)
            MsgBox cell.row
            MsgBox cell.column
 
        End If
 
    End If
 
Next

[jissididi]

So I found the answer to my question, I switched to the "find" method instead of the vlookup that does not seem to work on vbscript :


Here is the code :

 
For i = 7  to numLines
    If objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("H"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("L"&i)="" Then
        i = numLines
    Else
        If objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)<>"" Then
            Reference = Mid(objExcel.Workbooks(Str1).Sheets(1).Range("D"&i),1,6)
                Set r = objExcel.Workbooks(Str2).Sheets(1).Range( "C1:C" & numLines2 )
            Set matched = r.Find(Reference)
            If Not r.Find(Reference) Is Nothing Then
                objExcel.Workbooks(Str1).Sheets(1).Range("R"&i).Value = matched.Offset(0,0).Value
                objExcel.Workbooks(Str1).Sheets(1).Range("S"&i).Value = matched.Offset(0,1).Value
                objExcel.Workbooks(Str1).Sheets(1).Range("T"&i).Value = matched.Offset(0,2).Value
                objExcel.Workbooks(Str1).Sheets(1).Range("U"&i).Value = matched.Offset(0,3).Value
                objExcel.Workbooks(Str1).Sheets(1).Range("V"&i).Value = matched.Offset(0,6).Value
            End If            
        End If
    End If
Next