VBA Vlookup #N/A Error

[jeskit]

HI

I written the following if statement which should say if the variable ManualVN = yes then open a workbook (PPOWB) and copy data form that workbook into a new sheet in another workbook (PPCWB) and then close the workbook opened.

Then in (PPCWBSht) put a vlookup in H2 to look the value in cell C2, in the table area of ConvertManual!$A$2:$Y$4000. Then do an autofill and fill the rest of column H with that forumla.

If ManualVN = vbYes Then
Application.StatusBar = "Importing Manual data "
Set PPOWB = Application.Workbooks.Open(strFldr & "/" & "HDE_PPIII_Month_Calculations_Table_V" & ManualImportVN & ".xlsx")
Set PPOWBSht = PPOWB.Sheets("CalcAPD")
PPCWB.Sheets.Add.Name = "ConvertManual"
PPOWBSht.Range("C1:J20000").Copy Destination:=PPCWB.Sheets("ConvertManual").Range("A1")
PPOWB.Close
k = WorksheetFunction.CountA(PPCWBSht.Range("A:A"))

PPCWBSht.Range("H2").Formula = "=IF(C2=0,0,VLOOKUP($C2,ConvertManual!$A$2:$Y$4000," & 6 & ",FALSE))"
PPCWBSht.Range("H2").AutoFill Destination:=PPCWBSht.Range("H2:H" & k)

Application.StatusBar = "Formatting and deleteing temporary Sheet"
    PPCWBSht.Cells.Copy
    PPCWBSht.Range("A1").PasteSpecial Paste:=xlPasteValues
End If

Now the macro works and the vlookups put in work for half of column H and then half way down the rest show #N/A, i deleted the #n/a and manually entered the formula and it worked fine. Can anyone explain why its working in half of the column H but not the otehr half?

Thanks

Jeskit

[abousetta]

Hi,

Could you please post a workbook with dummy data so we can run it on data similar to yours? In general #NA means that its not present, but since you say that when you manually put the formula then it works then it would interesting to see the formulas action to see what is going wrong.

abousetta

[abousetta]

Quick question... your data seems to run from Row 20000 but vlookup is only from Row 4000 and you are copying the formula down to the last used row (k). Is K more than 4000?

abousetta

[jeskit]

Hi

K is higher than 4,000, its about 6-8000. Will this cause a problem?

Unfortunalty i am having trouble uploading a workbook, i keep getting a database error

[abousetta]

Without seeing your data if the value you are looking in below row 4000 then you will get a #NA error. Just for fun, why don't you try changing this number to 10000 and see what happens.

Regarding the upload, if the file is too big then you can zip it, else try uploading later.

abousetta

[jeskit]

I have had a look and the #N/A Error starts at row 4000.

Does this mean it can not autofill past row 4000??

Can i do multiple autofills??