Vlookup between two different files

[kacyi]

Hi Everyone,

I'm very new to VBA, but I need to add just one part to an existing macro I use.
I've googled it and tried various codes, but to be honest I'm helpless. I would appreciate if you could please help me with this.

Every week or two I receive and export from a database with names and additional information. I've added a column where I put my own comments, but I need to find a way to copy comments I made on the old file into a new file and add the code to the existing macro.

So I've got two files and I need to copy comments from column F in the old file into column F in the new file.



I want to make a vlookup macro with where a name from column E would be the criteria. That's how I would do this manually:
=VLOOKUP(E2;'[Old file.xlsx]Old sheet'!$E:$F;2;0)

Then drag the formula until the end of the data.

If you could please give me some help I would be very grateful!

 

[Smallman]

Hi Kancyi

Welcome to the forum

Firstly before I give you an answer I don't use external links when designing a spreadsheet.

With this in mind if you are going to keep those external links in vb you have to ask the question is the Old File going to be open when you run this vlookup. Because as you close the file the vlookup formula will change from

=VLOOKUP(E2;'[Old file.xlsx]Old sheet'!$E:$F;2;0)

to

=VLOOKUP(E2,'C:\Users\HYMC\theSmallman\[OldFile.xlsx]Sheet1'!E:F,2,0)

I don't think you can guarantee that the file will be open when the lookup needs to be performed. I will give you both vba procedures, just in case.

Basic code:

Sub FileisOpen()
Range("E2", Range("E" & Rows.Count).End(xlUp)).Offset(, 1) = "=VLOOKUP(A2,[File1.xlsx]Sheet1!E:F,2,0)"
end sub

Which will be fine if the file lookin up is open. Now things get a bit more interesting when you have to cope with a closed file. You need something like:

Sub MoreDynamic()
Range("E2", Range("E" & Rows.Count).End(xlUp)).Offset(, 1).Formula = "=VLOOKUP(E2,'C:\Users\HYMC\theSmallman\[OldFile.xlsx]Sheet1'!$E$2:$F$6,2,0)"
End Sub

If you just close the file and copy the formula at your end into the vb solution above you should be OK.

Take care

Smallman

 

[vijay.vizzu]

Dear Kacyi

In your above question, you mentioned "I need to find a way to copy comments I made on the old file", here comments means cell comments ( as yellow color box at the corner of the cell) or just you typed some text in that cell.

Please clear to resolve the problem