VBA function(_ to use Vlookup()

[imfarhan]

Hi All,
I have two files one is working. file (Working.xlsx) and LookupFile.xlsx attached images with some data.
I simply use the function called "Division"(shown below) to call the values from my LookupFile into Working.xlsx file
When I copy the "LookupFile.xls" Sheet within the working file its works but when I try to use the address using variable (extwbk) ... it doesn't work
please advise.

Once its working I will add this function as a custom Excel Add-in, so I and my colleagues can use it on any workbook.
i think I should be able to it myself the next step (Add this function as an Add-IN)

Thanks

Function Division(CC)

 Dim extwbk As Workbook, twb As Workbook
 
 Set extwbk = Workbooks.Open("K:\Finance\ManAccts\Month End Reports\Management Reporting Databases\99_Power_Bi_TB\LookupFile.xlsx")
 Set x = extwbk.Worksheets("CostCentre").Range("A1:G1752")
 
 Division = Application.WorksheetFunction.VLookup(CC, Range(x), 2, 0)

End Function

 

[LazyBug]

Hello, @imfarhan!
Where's your function code located? How do you call this function? What is CC?

 

[imfarhan]

Hello, @imfarhan!
Where's your function code located? How do you call this function? What is CC?

Hi Thanks for your response
I have created the function within the Working file under the new Module called "FS_Utility" as shown in the attached image
To call the function, simply any cell if you type =Division(reference cell) it works
If you wants to see the clip how to call the function can be see on this video

I have a response from my colleague it can work as Sub but not as function, hope make sense
Happy to share the sample files if you want to try them yourself
Cheers!

 

[LazyBug]

Happy to share the sample files if you want to try them yourself

That would be great, with a little dummy data set.
BTW, you can use VBA, RICH, </> and >_ buttons above the Reply window for your VBA Code posting.

 

[imfarhan]

Sorry can't see the option to attach the excel files, but I think in my original post I have shown both files Working.xlsx and LookupFiel with the sample data
The VBA Code also shown in the original post under </>

Appreciate your help

VBA Code:
Function Division(CC)

Dim extwbk As Workbook, twb As Workbook
 
Set extwbk = Workbooks.Open("K:\Finance\ManAccts\Month End Reports\Management Reporting Databases\99_Power_Bi_TB\LookupFile.xlsx")
Set x = extwbk.Worksheets("CostCentre").Range("A1:G1752")
 
Division = Application.WorksheetFunction.VLookup(CC, Range(x), 2, 0)

End Function

 

[imfarhan]

Cost Centre(CC) (Column = A)	Lookup value
123	=Division(A2)
124
125
126

Also enclosed the sample data in the image using two workbook (Working) and Rrefrence workbook
Thanks hopefully make sense now

 

[LazyBug]

I may be wrong, let more competent colleagues correct me, but it seems that UDF() call from within a Sub can open a file, but the same UDF() called from a worksheet cell can not. Therefore, I suggest a simpler option:
1) select the range in column B in the Working.xlsx book (the neighboring one on the left should contain the first argument VLookup (the value to search)),
2) run the macro:

Sub Dvsn()
Dim ash As Worksheet, vlksh As Worksheet, c As Range
Application.ScreenUpdating = False
    Set ash = ActiveWorkbook.ActiveSheet
    Set vlksh = Workbooks.Open("K:\Finance\ManAccts\Month End Reports\Management Reporting Databases\99_Power_Bi_TB\LookupFile.xlsx").Worksheets("CostCentre")
        With ash
            .Activate
            For Each c In Selection
                If Not (IsEmpty(c.Offset(0, -1))) Then
                    c.Value = WorksheetFunction.VLookup(c.Offset(0, -1).Value, vlksh.Range("A1:G1752"), 2, 0)
                End If
            Next c
        End With
        vlksh.Parent.Close 0
Application.ScreenUpdating = True
End Sub

 

[imfarhan]

Thanks a lot , your method is working but a couple of things we need to resolve
So these reference values (Lookup) should called through the function i.e. =Div(A2)

Does it mean all the files should be saved as *.xlsm, can we apply or call this sub on standard *.xlsx WB?
Ideally, we need when the users open their working files *.xls, they simply called this UDF() i.e. =Div(CellRef) and give the output (Divsion) name
let accept this challenge I'm sure you can do that
Thanks again
Farhan

 

[imfarhan]

testing