1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Dynamic vlookup help

Discussion in 'VBA Macros' started by Hurr1can3Blu3, Apr 14, 2018 at 10:51 PM.

  1. Hurr1can3Blu3

    Hurr1can3Blu3 New Member

    Messages:
    1
    Need help with VBA code to make vlookup dynamic (2 workbooks) book 1 holds vlookup...books have daily changing filenames, book 1 is old master file....see current code below.




    Code (vb):
    Sub todeleteaging1()
    '
    ' todeleteaging Macro
    '
    '
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
      .AllowMultiSelect = False
      .Title = "Please select the report."
      .Filters.Clear
      .Filters.Add "Excel 2016", "*.csv"
      .Filters.Add "All Files", "*.*"
      If .Show = True Then
        FilePath1 = .SelectedItems(1)
        ary = Split(.SelectedItems(1), "\")
        TextBox1.Value = ary(UBound(ary))

    Windows(TextBox2.Value).Activate
    myFileName2 = ActiveWorkbook.Name
    mySheetName2 = ActiveSheet.Name
    myRangeName2 = Range("A1:A")
    Windows(TextBox1.Value).Activate
    Columns("T:T").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("T2").Select
    myRangeName2 = ActiveWorkbook.ActiveSheet.Range("A1:A").Address(RowAbsolute:=1, ColumnAbsolute:=1, external:=True)
    Range("T2:T").Formula = "=VLOOKUP(T2, " & myRangeName2 & ", 1, FALSE)"
    Range("T2").Select
    Selection.AutoFill Destination:=Range("T2:T")
    Range("$A$1:$T$").AutoFilter Field:=20, Criteria1:="=#N/A" _
            , Operator:=xlOr, Criteria2:="=#N/A"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Range("A1").Select
        ActiveSheet.Paste
        Range("C6").Select
        Application.CutCopyMode = False
        ChDir "C:\Users\RRF - 113\Downloads"
        ActiveWorkbook.SaveAs fileName:= _
            "C:\Users\RRF - 113\Downloads\todelete20180413.csv", FileFormat:=xlCSV, _
            CreateBackup:=False
      End If
    End With
    End Sub
    Last edited by a moderator: Apr 15, 2018 at 5:58 AM

Share This Page