UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vlookup To Closed Workbook    
 
   
bakersburg9
post Jul 5 2018, 01:29 PM
Post#1



Posts: 5,150
Joined: 2-November 04
From: Downey, CA


Informal poll: Am I the only one who just learned it's easy as pie to have a VLOOKUP to a closed workbook ? Wow - this is awesome.... I've been automating the process of completing PO requests - my macro puts the Zip Code in Cell I2 - there's a number that's needed, the CPR number, and I want the result of the VLOOKUP in cell F12 (the CPR#, which is now based on zip code)

1. In File Explorer, go to C:\TEMP
2. Create folder named “CPR_Lookup”
3. Save Zip code lookup (to CPR#) in newly-created folder

Code with Formula is as follows – no need to have file open

CODE
Range("F12").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-10]C[3], 'C:\TEMP\CPR_Lookup\[CPR_Lookup_Central.xlsm]ZipCodes'!R2C1:R200C7,6,FALSE)"

What the macro does:
1. Goes to cell F12, where you want the CPR# (result of VLOOKUP)
2. Arguments in VLOOKUP formula:
a. Go up 10 rows, right 3 columns (to where the zip code is)
b. Range to lookup is filepath, name, worksheet name and absolute range
c. Sixth column over – column with CPR#
d. FALSE – exact match

Who knew ?

Go to the top of the page
 
JonSmith
post Jul 5 2018, 05:31 PM
Post#2



Posts: 3,544
Joined: 19-October 10



Yes, you can even use evaluate to get the result of external formula's without actually writing any formula's to a cell. If you only need a couple of values from a workbook then using Evaluate and lookups is much quicker.
That being said I'd always propose using Match and Index over VLookup.
Go to the top of the page
 
JonSmith
post Jul 6 2018, 03:50 AM
Post#3



Posts: 3,544
Joined: 19-October 10



Here is some more stuff for you.

Getting any single value from a closed workbook without opening it in VBA.
CODE
Function fnReturnValue(strPath As String, strFile As String, strSheet As String, strRng As String) As String
   '   Purpose: Returns the value from a closed workbook.
   '    Author: Internet Source, Updated by Jon Smith
   '      Date: 10/3/16
   'Parameters:  strPath - The path to the Excel File
   '             strFile - The filename of the Excel File
   '            strSheet - The sheetname we want to get a value from
   '              strRng - The range we want to get the value of.
   '   Returns: string - The value of the cell.
    
    
   'Example of useage
   'strPath = "C:\Users\jonasmith\Desktop\"
   'strFile = "Test.xlsx"
   'strSheet = "Sheet1"
   'strrng = Range("A1").Address(1, 1, xlR1C1)
   Dim strRef As String
    
   If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
   'Build a reference based on the parameters above
   strRef = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strRng

   'If the reference returns an error value or a blank value exit the function so it also returns blank.
   If ExecuteExcel4Macro("IsError(" & strRef & ")") = True Then Exit Function
   If ExecuteExcel4Macro("IsBlank(" & strRef & ")") = True Then Exit Function
  
   'Set the function to the returned value.
   fnReturnValue = ExecuteExcel4Macro(strRef)
End Function



Here is an example of a method to find the matching row in a table of data using Match (which returns a row number). (I cant remember off the top off my head why I needed the +1).
CODE
iRow = shtClientList.Evaluate("=MATCH(""56"",tblClientList[IDClient],0)") + 1


Once you have the row you have a few options. If you were to select that cell you can then reference any other column on that row using the following.
CODE
        strClientName = shtClientList.Evaluate("=tblClientList[@ClientName]") 'Client Name
        strOptionGroup = shtClientList.Evaluate("=tblClientList[@Option]")  'Option
        strLanguage = shtClientList.Evaluate("=tblClientList[@Language]")  'Language
        boolSAF = shtClientList.Evaluate("=tblClientList[@SAFClient]")  'SAF Option



Alternatively with the row number you can do something like this without selecting the cell after you use Match
CODE
strClientName = shtClientList.Evaluate("=INDEX(tblClientList[ClientName]," & iRow & ")")


You can also combine them into one line.
CODE
strClientName = shtClientList.Evaluate("=INDEX(tblClientList[ClientName],MATCH(""56"",tblClientList[IDClient],0))")



Now as to why this is waaaaay better than VLookup.
Column order doesn't matter, you don't need to mess around putting the columns you are looking up to the far left of your data.
The lookup column is much easier to read and alot more stable, in the VLookup posted early its looking up the column 6 columns right of the one used for looking up a value. I have no clue what column that is though. I need to find the lookup and count the columns myself and work it out.
Furthermore if there are any structural changes to the table the formula can and will break and return the wrong values.

In my Index Match example, you can clearly see we are looking up a client ID and returning the value from the ClientName column etc etc. The column number doesn't matter at all as we use structured references so any changed to the table structure and column position are irrelevant, the code will still work correctly.


So yeah, abandon VLookup, get into Index and Match and hope you found my examples useful.

Edit:
Forgot to mention, all of my examples are using a listobject table in Excel (go to Insert>Table and it turns your data range into a table). That automatically allows all the structured references I have used. I have not written a single Named Range or anything like that. Its all vanilla Excel functionality when you use tables, which when working with a table of data I also think you should.
This post has been edited by JonSmith: Jul 6 2018, 04:03 AM
Go to the top of the page
 
BuzyG
post Jul 6 2018, 03:57 AM
Post#4



Posts: 375
Joined: 20-September 12
From: Cornwall UK


Well I sure found it interesting Jon. thumbup.gif

--------------------
Live to Surf
Go to the top of the page
 
bakersburg9
post Jul 11 2018, 10:03 AM
Post#5



Posts: 5,150
Joined: 2-November 04
From: Downey, CA


ahh... people are passionate about the INDEX MATCH thing, haha - hey, guess what? I "lost" this macro in my PERSONAL.xlxm - so I came back here to recover it ! Whoo hoo !!! cool.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th July 2018 - 11:28 AM