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!

Please Login or Register  to view this content.

[michson]

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

Please Login or Register  to view this content.

[alicelia]

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.

Please Login or Register  to view this content.

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.

[michson]

alicelia, your ready code:

Please Login or Register  to view this content.

[alicelia]

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

Please Login or Register  to view this content.

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.

[michson]

This should work fine, try it:

Please Login or Register  to view this content.

[alicelia]

Thank you very much michson! It did work!Your help has been very much appreciated. I have added to your reputation. Thanks again!

[alicelia]

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:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.