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>

 

[steve the fish]

You say your vlookup table is in A16:I55?

This bit in your code R16C2:R48C9 relates to B16:I48

ie Row 16 column 2 : Row 48 Column 9

 

[Alicelia]

Hi Steve. Thanks for pointing that out. I have been changing the code often and forgot to rewrite that part. I have changed it to R16C1:R55C9 but it is still returning just 0.00% as values.

 

[steve the fish]

What about this line targetSheet.Cells(i, 11).Value to:

targetSheet.Cells(i, 11).FormulaR1C1

This will leave a formula in the cell. Does the formula say what you expect it to say?

 

[Alicelia]

What about this line targetSheet.Cells(i, 11).Value to:

targetSheet.Cells(i, 11).FormulaR1C1

This will leave a formula in the cell. Does the formula say what you expect it to say?

Thanks for your reply. I tried changing it to your suggestion and the result was all cells are blank in the row where results are supposed to be imported.

I tried this:

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

and this:

 targetSheet.Cells(i, 11).FormulaR1C1 = "=VLOOKUP(targetSheet.Cells(i, 2),[sourceSheet]!R16C1:R55C9, 9, False)"

both of them return blank column and the formula did not show up in the cell.

 

[steve the fish]

Sorry sometimes I don't see for looking. You need to concatenate the formula together.

Something like:

Cells(i, 11).Value = "=VLOOKUP(" & targetSheet & "!R" & i & "C2," & sourceSheet & "!R16C1:R55C9,9,False)"

 

[steve the fish]

This is better should your sheet names have spaces:

Cells(i, 11).Value = "=VLOOKUP('" & targetSheet & "'!R" & i & "C2,'" & sourceSheet & "'!R16C2:R48C9,9,False)"

 

[Alicelia]

Thank you Steve. I tried both of them but the cells are empty after I ran the macro. Is it because there is some problem with the table_array I set or what do you suggest that it could be?

 

[steve the fish]

Another read and they are different workbooks so you need the name of the source workbook in there as well. What does this produce:

msgbox customerFilename

 

[Alicelia]

Hi Steve. Sorry my internet stopped working because of partial electricity shortage in my office. I have found the following code to be working now. Now I just need to replace the formula in the cells with the cell value. Thanks a lot for your help! It is very much appreciated.

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


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








customerWorkbook.Close SaveChanges:=False


Cells.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents


End Sub