Creating a macro to do a lookup by selecting it from a folder

S

soupi

Board Regular
Joined
Mar 31, 2014
Messages
61
I am trying to create a macro to do a lookup off a certain lookupfile.xls, when the macro is selected a popup window will appear so the user can select the lookupfile.xls...

I recorded a macro by physically doing a lookup, but when the run the macro the lookup file has to be open. so I was trying to find a different way to do it, because the lookup will be different each week i use it.

thank you.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
The following bit of code will allow the user to select a file to be opened...
Code: 
...
    Dim FileToOpen    FileToOpen = Application.GetOpenFilename("Lookup File (*.xlsx), *.xlsx", , "Select the lookup file")
    If FileToOpen <> False Then   'cancelling or clicking the X ends the macro
       Workbooks.Open FileName:= FileToOpen
       'do other stuff here
    End If
Hope that helps
 
Upvote 0
You can try it out on its own, then add to it to do what you need. With regard to recording...I sometimes start by recording, but the final version usually bears little resemblance to what was recorded. But that really depends on the scope of the actions you need to do.
If you want to use this to just see how it works, you can put the following into a module (the kind of module you get when you record a macro; just paste this into the window:
Code: 
Sub Open_This_Workbook()


Dim FileToOpen   ' this is a variant that holds the filename if one is selected, or holds the boolean False if the dialog is canceled.


FileToOpen = Application.GetOpenFilename("Lookup File (*.xlsx), *.xlsx", , "Select the lookup file")
    If FileToOpen <> False Then   'cancelling or clicking the X ends the macro
       Workbooks.Open FileName:=FileToOpen
       MsgBox "You just opened this file: " & FileToOpen
       'do other stuff here
    End If
End Sub
If you list out the rest of the steps you need to do, or show the code you already recorded, I can probably help with it.
 
Upvote 0
Thank you Cindy, I got my lookup to work, but then I recorded to search for #n/a and replace it with a blank.
but when i ran the macro it crashed... is there another way to do this search and replace... because the cells change often so i cant do the filter for #n/a's and physically delete them.. in bold is were the bombing starts

Code: 
 ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-3],'[Wirecenter CT 2-7.xls]report'!C2:C6,5,FALSE)"
    Selection.AutoFill Destination:=Range("G2:G27268")
    Range("G2:G27268").Select
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-5],'[Wirecenter CT 2-7.xls]report'!C2:C3,2,FALSE)"
    Selection.AutoFill Destination:=Range("I2:I27268")
    Range("I2:I27268").Select
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-6],'[Wirecenter CT 2-7.xls]report'!C2:C11,10,FALSE)"
    Selection.AutoFill Destination:=Range("J2:J27268")
    Range("J2:J27268").Select
    Columns("G:G").Select
    [B]Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False[/B]
    Selection.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("G:G").Select
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("I:I").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("J:J").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("J27").Select
End Sub
 
Upvote 0
The best way is to include the hiding as part of the formula, so that you don't have to search and replace later. I'm on a deadline at work right now, so it will be a few more hours before I can fix it. I'll have other suggestions, as well, for reducing the number of "select" statements, which will make the code easier to read and maintain.
 
Upvote 0
I think the following will replace just about all of the code above, except replacing formulas with values. I wasn't sure if that part was intended or maybe still trying to get rid of the "N/A" values, so I left it out.
Code: 
Sub Make_Vlookups()
    ActiveSheet.Range("G2:G27268").FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-3],'[Wirecenter CT 2-7.xls]report'!C2:C6,5,FALSE), """")"
    ActiveSheet.Range("I2:I27268").FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-5],'[Wirecenter CT 2-7.xls]report'!C2:C3,2,FALSE), """")"
    ActiveSheet.Range("J2:J27268").FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-6],'[Wirecenter CT 2-7.xls]report'!C2:C11,10,FALSE), """")"
End Sub
The "Iferror", with the bits after the end of the vlookup, will suppress the "N/A" values, but leave the formula there if something changes.
If the length of the file varies, you can change the 27268 to a variable based on the last data row, with the following formula to get the row number:
Code: 
LastDataRow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row  '1 is the column with data
.
Hope this helps,
 
Upvote 0
thank you Cindy, I tried to do alook up in the same file now but off another sheet (EPM)... When i ran it it crashed in the first bold line.. The error said RUN-TIME error' 1004' Application-definded or object-defined error.

Why would that happen, also do you have a email for quicker reponse?

thank you..

Code: 
Sub vLookup_EPM()
  [B]  ActiveSheet.Range("AD2:AD27268").FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-26],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]report'!C2:C4,3,FALSE), """")"[/B]
    ActiveSheet.Range("AE2:AE27268").FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-27],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]report'!C2:C6,5,FALSE), """")"
    ActiveSheet.Range("AF2:AF27268").FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-28],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]report'!C2:C7,6,FALSE), """")"
    ActiveSheet.Range("AG2:AG27268").FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-29],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]report'!C2:C8,7,FALSE), """")"
    ActiveSheet.Range("AI2:AI27268").FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-31],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]report'!C2:C9,8,FALSE), """")"
End Sub
 
Upvote 0
Per the forum rules, all communication should be in the forum, not by separate email. Can you please copy 1 of a working vlookup (not from the macro) that points to the different worksheet? I can probably help correct the formula in the macro to reflect the correct location.
 
Upvote 0
When complete it still has n/as and i want to format those numbers with decimal places.
Code: 
Sub EPM()
'
' EPM Macro
'
'
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-26],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]Analysis 1'!C2:C4,3,FALSE)"
    Selection.AutoFill Destination:=Range("AD2:AD27268")
    Range("AD2:AD27268").Select
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-27],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]Analysis 1'!C2:C6,5,FALSE)"
    Selection.AutoFill Destination:=Range("AE2:AE27268")
    Range("AE2:AE27268").Select
    Range("AF2").Select
    Windows( _
        "TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls" _
        ).Activate
    Windows("vSAP Job Information 5-8-14AFTER.xlsx").Activate
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-28],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]Analysis 1'!C2:C7,6,FALSE)"
    Selection.AutoFill Destination:=Range("AF2:AF27268")
    Range("AF2:AF27268").Select
    Range("AG2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-29],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]Analysis 1'!C2:C8,7,FALSE)"
    Selection.AutoFill Destination:=Range("AG2:AG27268")
    Range("AG2:AG27268").Select
    Range("AH2").Select
    Windows( _
        "TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls" _
        ).Activate
    ActiveWindow.WindowState = xlMinimized
    Range("AI2").Select
    Windows( _
        "TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls" _
        ).Activate
    Windows("vSAP Job Information 5-8-14AFTER.xlsx").Activate
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-31],'[TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls]Analysis 1'!C2:C9,8,FALSE)"
    Selection.AutoFill Destination:=Range("AI2:AI27268")
    Range("AI2:AI27268").Select
    Range("AJ2").Select
    Windows( _
        "TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls" _
        ).Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Windows("vSAP Job Information 5-8-14AFTER.xlsx").Activate
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
336
glynn1969
G
S
  • Solved
For each row, create new folder based on single cell value, then save exported XML doc to matching folder.
Replies
3
Views
147
shand
S
K
  • Question
Vlookup that copies formatting?
Replies
3
Views
331
Eric W
Eric W
J
  • Question
How can I create a list from a single layer of a directory?
Replies
6
Views
303
PeteWright
PeteWright
U
  • Question
Lookup/Vlookup to find exact date
Replies
6
Views
173
UndwaterExcelWeaver
U

Forum statistics

Threads
1,214,649
Messages
6,120,730
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top