VBA Vlookup function returns wrong values

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!

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!R16C1:R55C9, 9, False)"
Next i



customerWorkbook.Close SaveChanges:=False


End Sub

alicelia, what you do wrong is that you set variable as string and do not use value property but formula. Do like this:

targetSheet.Cells(i, 11).formula = "=VLOOKUP(" & targetSheet.cells(i, 2) & ",sourceSheet!R16C2:R48C9, 9, False)"

Hi michson. Thanks for the reply. I have changed the code according to your corrections. This time the value changed to empty cell. The designated cells which are supposed to have values are empty and the remaining cells that exceeds the the table shows up with #NA.

targetSheet.Cells(i, 11).formula = "=VLOOKUP(" & targetSheet.cells(i, 2) & ",sourceSheet!R16C1:R55C9, 9, False)"

Also, I am not sure what I should set the variable to? Could you please give me some pointers to that? Sorry about this as I am quite new to VBA.
Thank you very much.

alicelia, your ready code:

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 SourceRng As Range

With sourceSheet
Set SourceRng = .Range(.Cells(16, 1), .Cells(55, 9))
End With

Dim i As Integer
For i = 4 To 43
  With targetSheet
   .Cells(i, 11).Formula = "=VLOOKUP(" & .Cells(i, 2).Address(false,false) & "," & SourceRng.Address & ", 9, False)"
  End With
Next i


customerWorkbook.Close SaveChanges:=False


End Sub

Thanks a lot michson. I really appreciate your help. I tried using it in place of my current code. However, it returns with #N/A. the formula in the cell (1,11) is

=VLOOKUP(B4,$A$16:$I$55, 9, FALSE)

It seems like it is not referring to the source file. Maybe I need to add it in? or do you mind if I send you the two excel files so you can see if there are some fundamental mistakes I made on the table_array selection? Thank you again.

This should work fine, try it:

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 SourceRng As Range

With sourceSheet
Set SourceRng = .Range(.Cells(16, 1), .Cells(55, 9))
End With

Dim i As Integer
For i = 4 To 43
  With targetSheet
   .Cells(i, 11).Formula = "=VLOOKUP(" & .Cells(i, 2).Address(False, False, external:=True) & "," & SourceRng.Address(external:=True) & ", 9, False)"
  End With
Next i


customerWorkbook.Close SaveChanges:=False


End Sub

Hi michson! I have tried and run the code. It indeed is working with the importation of numbers. However, the value imported is still in text format even after the alternation of the code from this:

 With range("K4:K43")
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    Application.CutCopyMode = False

to this:

With targetSheet.range("K4:K43")
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValuesAndNumberFormats
        .Cells(1).Select
    End With
    Application.CutCopyMode = False

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

Updates: I have fixed the numeric value problem by multiplying the formula with 1 so that the text format is forced into becoming numeric values in cells. Now only remains the #N/A problem.

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