Solved: overflow and failed vlookup

[CatDaddy]

Sub ImportNewKey()
ActiveWorkbook.Sheets(2).Activate
Range("A1").Activate
Range("AY1").Value = "MANAGER"
Dim cell As Range
Dim i As Integer
Dim r As Integer
Dim CAr As Integer
Dim MKPr As Integer
CAr = Sheets("CA").Range("A1000").End(xlUp).Row
MKPr = Sheets("MKP").Range("A1000").End(xlUp).Row
For i = 2 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Activate
For Each cell In Range("Q2:Q" & Range("A65536").End(xlUp).Row)

r = cell.Row
If cell.Value <> Empty Then
Range("L" & r).Formula = "=VLookup(Q" & r & ",CA!A2:C" & CAr & ",3,FALSE)"
Range("AY" & r).Formula = "=VLookup(Q" & r & ",CA!A2:C" & CAr & ",2,FALSE)"
Range("L" & r).Value = Range("L" & r).Value
Range("AY" & r).Value = Range("AY" & r).Value

Else
Range("L" & r).Formula = "=VLookup(M" & r & ",MKP!A2:C" & MKPr & ",3,FALSE)"
Range("AY" & r).Formula = "=VLookup(M" & r & ",MKP!A2:C" & MKPr & ",2,FALSE)"
Range("L" & r).Value = Range("L" & r).Value
Range("AY" & r).Value = Range("AY" & r).Value
End If
Next cell
Next i
End Sub


i get an overflow error about halfway through my workbook (32000 lines) and for some reason the first vlookup doesnt work ("L") at all and the second works perfectly ("AY") but they are both based off the same ws appended to the end...hoping for something stupid i overlooked lol

[CatDaddy]

ok so if i delete the information already in column L the vlookup works (I dont have to delete the data in AY though...) but it still throws the overflow error

[CatDaddy]

needs to be a long...sorry to waste your time!!!!