Macro with VLOOKUP on another WorkBook

[guibro]

Hi Excel People,

I have a template with a lot of Different Job Titles associated with Job functions. So for example, I have Director of Tax in Job Title, then the Job Function would be : Finance/Accounting. And I've done this matching with a lot and a lot of different Job Titles to Group them into fewer Job Function categories.


My intent is then to populate different new files everyday by matching their Job Titles to the ones in the template and then returning the correct Job Function in those files.

So I guess the correct way to do it is to have a VLOOKup in the Macro that looks for a similar or almost similar Job Titles in the Template and then returns the Job Function associated to it.


Do you guys have any idea of how the macro would look like ?

For the purpose of this experience, let's say the Column Index will always be the same but the number of cells within the columns could fluctuate.

Thanks a lot and I look forward to exchanging with you about that.


Cheers,

-Guillaume

[humdingaling]

what are you looking up against what?
i dont see where the separate workbook comes into play?

[guibro]

Thank you really much for the follow up Humdingaling.

So I want to have a a workbook that's gonna serve as a bank. The workbook will have 2 columns : One with all the Job Titles that I could Have and in the 2nd one, all the Job Functions associated with those Job Titles.

Then I'll have other worksheets on a daily basis, that I will have to populate. Those workbooks will have a Job Title column but a Job Function column empty.

So I want the macro to run a Lookup of each Job Title in this workbook in the bank-workbook and return the job Function associated with it in the workbooks that I have to populate.

Is it more clear ?

Thanks a lot for your help and I stay tuned.


Cheers,


-guillaume

[humdingaling]

ok
that makes sense
cant you just put the vlookup (use the whole column) in and leave it? save a different version if you want it hardcoded version?
that way no need for macro/VBA? or am i missing something?

[guibro]

The thing is that I want to automate few tasks on multiple workbooks at once. I already have a macro that Proper my first 2 columns of every open workbooks. I want to add this macro which will populate every Job Functions of my all my open workbooks based on the relationship (in the bank workbook) that i just explained.

So by one click, the macro will search for the same job title in the bank workbook and return the Job function associated to it.

Does that make sens to you ?

[humdingaling]

ok sure no problem

what is the workbook called?
will the workbook be open when you run the macro or you want the macro to open it as well?

what is the cell of the first formula
assume you want it to auto fill formula as well?

[guibro]

The book hasn't got a name so far. So whatever you choose, I could put it after.

And yes please, open the workbook would be great. But if I run the macro multiple times, is it problem if the macro tries to open something already opened ? if it is, don't worry about opening the workbook, i'll do it by myself when I need to.

Million thanks for your help.

[guibro]

The book hasn't got a name so far. So whatever you choose, I could put it after.

And yes please, open the workbook would be great. But if I run the macro multiple times, is it problem if the macro tries to open something already opened ? if it is, don't worry about opening the workbook, i'll do it by myself when I need to.

Million thanks for your help.

[guibro]

Sorry for the duplicate messages... Internet issues...

So for the workbooks that's gonna serve as as bank : i'll have Job titles in column A and Job Function in column B.

For the workbooks that I want the run macro in, let's say the Job Title will be in column D starting in D2 and the Job Function would need to be in column E, so starting E2.

Will it be easy to make the changes of that cell references in the future ?

thanks a lot

[humdingaling]

ok i can give you the rough guide

basically you will need to fill in the "right" formula afterwards though

Sub Insert_Vlookup()
'
'
    Dim ws1 As Worksheet
    Dim wb1 As Workbook
    
    Set wb1 = ThisWorkbook
    Set ws1 = ActiveSheet
    
    Workbooks.Open Filename:="C:\test.xlsx"
    wb1.Activate
    ws1.Range("C2").Formula = "=VLOOKUP(A2,'[test.xlsx]Sheet1'!$A:$C,3,0)"
    
End Sub

[guibro]

Perfect, I'll try that at work Tuesday and I will definitely let you know.

Thanks for your help on this.

Is the Vlookup doing the matching but also returning the value in the workbook ?

Also how can I add a loop so that it does the same on every files opened ?

thanks.

[humdingaling]

the code adds the formula into your worksheet
you can change the workbook and worksheet it refers to

as it is rough guideline just change to tailor to your needs

Also how can I add a loop so that it does the same on every files opened ?

would think it can be
you would need to create a loop for all open workbooks

[guibro]

Awesome thank you.

I keep you posted on tuesday.

[guibro]

Hi humdingaling ,


So the macro does work perfectly.

Just a few questions PLEASE:

-How can I ask the macro to repeat the formula in all the non empty cells of the column ? So far, it is just doing the Vlookup for the first cell, the one that we put here : ws1.Range("C2").Formula

-How can I ask the macro the run the same thing in all the open workbooks except the one called "test"?

Thank you really much.


-Guillaume

[humdingaling]

How can I ask the macro to repeat the formula in all the non empty cells of the column ? So far, it is just doing the Vlookup for the first cell, the one that we put here : ws1.Range("C2").Formula

you are going to be a bit more specific about this
you want it to fill down but only for non empty?

How can I ask the macro the run the same thing in all the open workbooks except the one called "test"?

again not quite 100% sure what you are asking
you want to vlookup formula in C2 on sheet1 of all workbooks open except test?
if that is so you can run a loop thru all the open workbooks i guess
let me know if this is exactly what you want

Sub Insert_Vlookup()
'

    Dim wb As Workbook
    
    Workbooks.Open Filename:="C:\Users\vthang\Downloads\Vlookup.xlsx"
    
    For Each wb In Workbooks
        If wb.Name <> "PERSONAL.XLSB" Then
            If wb.Name <> "Vlookup.xlsx" Then
        
                wb.Activate
                Range("C2").Formula = "=VLOOKUP(A2,'[vlookup.xlsx]Sheet1'!$A:$C,3,0)"
            End If
        End If
        
    Next wb
    
End Sub

Vlookup.xlsx is the file with the vlookup table

[guibro]

Thank you for your time !

[QUOTE=humdingaling;4059158]you are going to be a bit more specific about this
you want it to fill down but only for non empty?

It's exactly why I want. I want the macro to fill the whole column not just the first cell. It is actually just filling the cell C2 and not the ones below.


[QUOTE= again not quite 100% sure what you are asking
you want to vlookup formula in C2 on sheet1 of all workbooks open except test?
if that is so you can run a loop thru all the open workbooks i guess
let me know if this is exactly what you want [/QUOTE]

Again, you understood it perfectly. So because you name the file with the vlookup table Vlookup.xlsx, I want the macro to run in every open workbooks except the one that is called Vlookup.xlsx.

In your macro in your last message, what does the file Personal.xlsb have to ? is it the place when you store the macro so that you can run it in every document all the time ?

I hope I could help you because you are really helping me. thanks a lot for that.

[humdingaling]

ok fill down...to where?
what is the relative row you want it to fill down to?
is it the same all the time or different?...are you sure you only want non blanks rather than just all of them updated?
the coding for one is easy and the other hard

Re: personal.xlsb
im not sure if you have it on your system or not
it is basically where most people store their macro's and where i assumed you are storing yours
as such you dont want add a vlookup on that so that was just to bypass it as well

[guibro]

Actually, do the simpler one. I guess it's the one that is going to update them all. I just prefer if it's blank, to return nothing than to put #N/A.

I don't have a precise row for the fill down and it does change from one workbook to another. So fill down until the last non empty row. is that possible ? it's like the auto-fill tool that we can use when we click on the bottom left of the cell.

And yes I do have a personal file. So that's ok for me, I already have 3 macros in that file that I use all the time on my workbooks.

Thanks a lot !

[humdingaling]

I don't have a precise row for the fill down and it does change from one workbook to another. So fill down until the last non empty row. is that possible ? it's like the auto-fill tool that we can use when we click on the bottom left of the cell.

yes plausible but still need some relative guide on where to stop otherwise....1048576 rows will get filled...
last row in column A? B?C? or just last used range/row on the sheet?
i will assume match column A

then code

Sub Insert_Vlookup()
'

    Dim wb As Workbook
    
    Workbooks.Open Filename:="C:\Users\vthang\Downloads\Vlookup.xlsx"
    
    For Each wb In Workbooks
        If wb.Name <> "PERSONAL.XLSB" Then
            If wb.Name <> "Vlookup.xlsx" Then
        
                wb.Activate
                Range("C2").Formula = "=VLOOKUP(A2,'[vlookup.xlsx]Sheet1'!$A:$C,3,0)"
                Range(Cells(2, 3), Cells(Cells(Rows.Count, "A").End(xlUp).Row, 3)).FillDown
                
            End If
        End If
        
    Next wb
    
End Sub

[guibro]

I have a few issues... First, it doesn't auto fill until the last row of the column. Let's say, column H is for Job Title and I for Job Function. I want to auto fill column I until the last entry in column H.

Second Issue, it's weird because when I run the macro, the same formula appears in My Personal.xlsb file. It doesn't do any harm but I'm like wondering why it creates the same formula it that template.

Below is the macro I use. I've been also wondering to what Range("C2"). formula is supposed to refer to ? is that first cell where I want the formula to start populating ?

Thank you so much for your help. I'm sure it's almost done...

Sub Insert_Vlookup()
'

    Dim wb As Workbook
    
    Workbooks.Open Filename:="C:\Users\guillaume.brovelli.c\Desktop\Vlookup.xlsx"
    
    For Each wb In Workbooks
        If wb.Name <> "PERSONAL.XLSB" Then
            If wb.Name <> "Vlookup.xlsx" Then
        
                wb.Activate
                Range("I2").Formula = "=VLOOKUP(H2,'[vlookup.xlsx]Sheet1'!$A:$B,2,1)"
                Range(Cells(2, 3), Cells(Cells(Rows.Count, "I").End(xlUp).Row, 3)).FillDown
                
            End If
        End If
        
    Next wb
    
End Sub

[guibro]

It's working now ! I was able to make the couple changes needed for me.

Million thanks for your help !!

-Guillaume

[guibro]

Hi humdingaling ,

Did you have a chance to take a last look at my project ?

Thank you really much.

-Guillaume

[humdingaling]

sorry i havent been in office this week
I dont have excel at home so cant look at it

glad you got it to work

cheers
Hum