vlookup problem and count problem

I try the nested for-loop at the past, but data checking is time consuming. So I decide to use vlookup to compare and get the data from another worksheet. I have searched many samples which is only for one workbook. In my code, the variable 'found' get error result, all values are display "NULL".

Please help to solve it...thank you!

Sub marco()
Dim rn As Integer
Dim x As Integer
Dim findmain As Variant
Dim found As Variant
Dim r As Range

    Workbooks.Open ("c:\ABC.xls")
    Workbooks("ABC.xls").Sheets(1).Range("a1:iv65536").Copy
    ThisWorkbook.Sheets(1).Range("a1").PasteSpecial
    Workbooks.Open ("c:\AA.xls")
    Workbooks("AA.xls").Sheets(1).Range("a1:iv65536").Copy
    ThisWorkbook.Sheets(2).Range("a1").PasteSpecial
    rn = 3930   
    rn2 = 12613 
   
    x = 4

 For i = x To rn
  findmain = ThisWorkbook.Sheets(1).Cells(x, 3)
  Set r = ThisWorkbook.Sheets(2).UsedRange
  found = Application.VLookup(findmain, r, 11, 0)
  If IsError(found) Then
    ThisWorkbook.Sheets(1).Cells(x, 26) = "NULL"
  Else: ThisWorkbook.Sheets(1).Cells(x, 26) = found
  End If
      x = x + 1
 Next i

Try these changes:

Dim LR As Long

Workbooks.Open ("c:\ABC.xls")
LR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
Range("A1:IV" & LR).Copy

When you opened the workbook, it became the the focused workbook so you don't have to keep referring to the bookname until you give focus to another workbook, like when you open the next book.

I think you would need to post a sample so we can see the data types in play etc... does the same VLOOKUP entered natively return expected results ? ie

Z4: =VLOOKUP(C4,Sheet2Name,11,0)

Your lookup range is not correct for a VLOOKUP... in a VLOOKUP the criteria must exist in the leftmost column of the range... your range utilises the .UsedRange of the 2nd sheet which is A:K but the ID number you wish to find is in Column E not A -- hence the VLOOKUP returns an error.

I have to go offline now but you could think about using an INDEX/MATCH approach, ie replace:

found = Application.VLookup(findmain, r, n3, False)

with

found = Application.Index(r.Columns(11),Application.Match(findmain,r.Columns(5),0))