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

[soupi]

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.

 

[Cindy Ellis]

It's not difficult to change the source workbook name, but to make it easier for the future, in case the directory or filename changes, it's now in a variable so you can change it in only one place. The following code also formats the numbers to 2 decimal places.

Sub EPM()
'
'
Dim SourceFileName As String, SourceSheetName As String, Range2Copy As String
    SourceFileName = "TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls"
    SourceSheetName = "Analysis 1"
    
    With ActiveSheet.Range("AD2:AD27268")
        .FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-26],'[" & SourceFileName & "]" & SourceSheetName & "'!C2:C4,3,FALSE), """")"
        .NumberFormat = "0.00"
    End With
    With ActiveSheet.Range("AE2:AE27268")
        .FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-27],'[" & SourceFileName & "]" & SourceSheetName & "'!C2:C6,5,FALSE), """")"
        .NumberFormat = "0.00"
    End With
    With ActiveSheet.Range("AF2:AF27268")
        .FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-28],'[" & SourceFileName & "]" & SourceSheetName & "'!C2:C7,6,FALSE), """")"
        .NumberFormat = "0.00"
    End With
    With ActiveSheet.Range("AG2:AG27268")
        .FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-29],'[" & SourceFileName & "]" & SourceSheetName & "'!C2:C8,7,FALSE), """")"
        .NumberFormat = "0.00"
    End With
    With ActiveSheet.Range("AI2:AI27268")
        .FormulaR1C1 = _
        "=iferror(VLOOKUP(RC[-31],'[" & SourceFileName & "]" & SourceSheetName & "'!C2:C9,8,FALSE), """")"
        .NumberFormat = "0.00"
    End With
End Sub

Hope this helps,

 

[soupi]

thank you cindy.. for some reason the lookup dosnt work unless i have file open also.. would it work if i prompt the user to select the look up file... like a popup that u displayed in the first reply to this thread?

 

[Cindy Ellis]

VLOOKUP should work even with the workbook closed. I obviously don't have a file with your filename and contents on my system, but I was able to run the macro (everything was populated with ""), and when I re-opened the file (but not the source file) it said that there were links to remote files, and did I want to update the linked information. You may have a security setting that prevents links from updating properly if the file isn't open. Take a look at the trust center in Excel options to check your settings.

 

[soupi]

Good morning Cindy, what does this line doing?

Dim SourceFileName As String, SourceSheetName As String, Range2Copy As String
    SourceFileName = "TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls"
    SourceSheetName = "Analysis 1"

thank you so much

 

[soupi]

Cindy I have a column that has several budget numbers for example it has 150, 110, 125... So I created a new tab next to it and I filtered on 150 and the fill the blank row next to it with 150. Then I want to filter 110 and fill the blank row next to it with .50... also same goes for 125.

This is the macro I had and it didnt work when i tried to run it....

Sub EPM_Finish()
'
' EPM_Finish Macro
'
'
    Selection.AutoFilter
    ActiveWindow.ScrollColumn = 2
    ActiveSheet.Range("$A$1:$AK$27268").AutoFilter Field:=18, Criteria1:= _
        "CAP110"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "0.1"
    Selection.FillDown
    
    ActiveSheet.Range("$A$1:$AK$27268").AutoFilter Field:=18, Criteria1:= _
        "CAP125"
   
    Range("S4").Select
    ActiveCell.FormulaR1C1 = "0.25"
    Selection.FillDown
    ActiveWindow.ScrollRow = 26958
    ActiveSheet.Range("$A$1:$AK$27268").AutoFilter Field:=18, Criteria1:= _
        "CAP150"
    Range("S3").Select
    ActiveCell.FormulaR1C1 = "0.5"
    Selection.FillDown
    Range("S27254").Select
    ActiveWindow.ScrollRow = 27144
End Sub

Do you know why that would happen.
thank you so much

 

[Cindy Ellis]

Dim SourceFileName As String, SourceSheetName As String, Range2Copy As String
    SourceFileName = "TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls"
    SourceSheetName = "Analysis 1"

The first line declares the variables. It's a good idea for a variety of reasons, but one of them is for the benefit of the reader of the code (you or me, or someone in the future). There's a difference between the workbook as an "object" and the workbook name as a string, so declaring it as a string makes it clear that it's the workbook name you're using.
The second 2 lines just make it possible to change just those 2 lines to open a different workbook/worksheet, without changing the entire code. Also, the workbook name gets used in a few places, and it made the code easier to read

Hope that helps,

 

[Cindy Ellis]

Cindy I have a column that has several budget numbers for example it has 150, 110, 125... So I created a new tab next to it and I filtered on 150 and the fill the blank row next to it with 150. Then I want to filter 110 and fill the blank row next to it with .50... also same goes for 125.

Since I don't have your data set, I'm not sure why that didn't work, but you can try the following (I'm assuming from your code that the 150, 110, 125, etc. are in column R, and that the value for column S is the value in R-100, divided by 100)
So, instead of filtering, try using the formula in S2:

= (R2-100)/100

, then copy down. You can then copy/paste values to get rid of the formulas.

 

[soupi]

Now I recorded a macro to creating a calculation onto a blank line which is the formula below... then do the % spent calcualtion which is ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"
calculation.. after that i filtered AI2 by div/0 and put filled 1 into the treshold (Cell AH)
then I filtered AG for greater then 0 and then filled AJ with "TES"..
it seems like the code didnt run properly when ran the macro i recorded below... do you know why?
thank you cindy

Sub EPM_Calc()
'
' EPM_Calc Macro
'
'

    Range("AH2").Select
 
    ActiveCell.FormulaR1C1 = "=RC[-4]+(RC[-4]*RC[-15])"
    Range("AH2").Select
    Selection.AutoFill Destination:=Range("AH2:AH27268")
    Range("AH2:AH27268").Select
    Range("AH10").Select
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"
    Range("AI2").Select
    Selection.AutoFill Destination:=Range("AI2:AI500"), Type:=xlFillDefault
    Range("AI2:AI500").Select
    ActiveWindow.SmallScroll Down:=12
    Selection.AutoFill Destination:=Range("AI2:AI27268"), Type:=xlFillDefault
    Range("AI2:AI27268").Select
    ActiveWindow.ScrollRow = 27188
    Columns("AH:AI").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AI10").Select
    Application.CutCopyMode = False
    Range("AH3").Select
    ActiveSheet.Range("$A$1:$AK$27268").AutoFilter Field:=35, Criteria1:= _
        "#DIV/0!"
    Range("AH50:AH26628").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("AH50").Select
    Selection.FillDown
    ActiveWindow.ScrollRow = 1
    ActiveSheet.Range("$A$1:$AK$27268").AutoFilter Field:=33, Criteria1:=">0", _
        Operator:=xlAnd
    Range("AJ50").Select
    ActiveCell.FormulaR1C1 = "yes"
    Range("AJ50").Select
    ActiveCell.FormulaR1C1 = "Yes"
    Selection.FillDown
    ActiveWindow.ScrollRow = 1
    ActiveSheet.Range("$A$1:$AK$27268").AutoFilter Field:=35
    ActiveSheet.Range("$A$1:$AK$27268").AutoFilter Field:=33
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 1
End Sub

 

[soupi]

hi Cindy, when i do a look up for example

Dim SourceFileName As String, SourceSheetName As String, Range2Copy As String
SourceFileName = "TCM_XX_0965-1_Capital Actual Spend vs Budget Commitment_2014-05-06_09-28-37.xls"
SourceSheetName = "Analysis 1"

.. i tried to run a macro for a xls sheet that is the same information as the TCM above, but it is for todays date... what would i do for the macro to do the lookup without looking at the exact sourcefilename...

is there a way to present a open window so i can physically chose the file to open, to do the look up off.?

thank you

 

[soupi]

I changed the syntax and now after the below macro is ran, it prompts for the xls file that i am doing alook up off, but it just opens the file and dosnt do the lookup... why is that?

thank you...

Sub EPM_vLookupsTEST()
'Lookup from the wirecenter reports and EPM finish date
Dim ECCDFileName
ECCDFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", 1, "Select ECCD download", , False)
Workbooks.Open Filename:=ECCDFileName

Dim ECCDShortFileName
ECCDShortFileName = ActiveWorkbook.Name

ActiveSheet.Range("G2:G27268").FormulaR1C1 = _
"=iferror(VLOOKUP(RC[-3],'[" & ECCDShortFileName & "]report'!C2:C6,5,FALSE), """")"
ActiveSheet.Range("I2:I27268").FormulaR1C1 = _
"=iferror(VLOOKUP(RC[-5],'[" & ECCDShortFileName & "]report'!C2:C3,2,FALSE), """")"
ActiveSheet.Range("J2:J27268").FormulaR1C1 = _
"=iferror(VLOOKUP(RC[-6],'[" & ECCDShortFileName & "]report'!C2:C11,10,FALSE), """")"


End Sub