VBA Vlookup function returns wrong values

[Alicelia]

Hi I am using vlookup function in VBA codes to import data from one excel file to another. The source file opens successfully. The code runs with no error messages. But the imported value are not correct.

The expected values imported are attendance percentages from the source file. However, the imported value are all zeros only. I have highlighted what I think is the problematic code in red.

The table array in the source file is A16:I55 but it can be shorter as number of students varies so it could be A16:I50. I set it as I55 so it will be sure to include everyone as the length of the table will not exceed I55. The lookup value is B4 which is the student name. Since i have to look up many students, I have set up the dim i as integer function to loop the Vlookup function. Could you please suggest what could be wrong with the highlighted code below so that it caused the aforementioned problem?

Thank you very much!

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub button()

' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

On Error Resume Next

Set customerWorkbook = Application.Workbooks.Open(customerFilename, UpdateLinks:=False, ReadOnly:=True)


' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)


Dim i As Integer
For i = 4 To 43
   targetSheet.Cells(i, 11).Value = "=VLOOKUP(targetSheet.cells(i, 2),sourceSheet!R16C2:R48C9, 9, False)"
Next i



customerWorkbook.Close SaveChanges:=False


End Sub

</code>

 

[Alicelia]

Hi Steve! I have tried and run the code. It indeed is working with the importation of numbers. However, the value imported is still in formula format and so the column next to it cannot carry out its designated functions as it only recognizes numeric value. I have tried using "range value" to fix the problem but it is still showing up as formula only in the cell.


Also, I have tried using the following codes to remove the #N/A in the cells but none of them worked.

Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clear

Cells.Replace "#N/A", "clear", xlWhole

This one worked a few times and then suddenly stopped working:

Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clearcontent

Would you please give me some pointers to these? Thank you very much.

 

[steve the fish]

Have a try with this. You can handle the formula errors within the formula itself

For i = 43 To 4 Step -1
    With targetSheet.Cells(i, 11)
        .Formula = "=IFERROR(VLOOKUP(" & targetSheet.Cells(i, 2).Address(external:=True) & "," & sourceRng.Address(external:=True) & ",9,False),"""")"
        .Copy
        .PasteSpecial xlPasteValues
    End With
Next i

 

[Alicelia]

Thanks Steve for your reply! Ｉhave tried the iferror but it was still showing #NA. However, changing that to ISNA did work! So thank you very much for your ideas and help! I really appreciate them.