Greetings, Gurus.
I have a script designed to prompt you to open a workbook, giving you a selection box to use, and then perform a vlookup from the workbook you selected. I have set all my variables, but there must be some issue with my formula. Although the script enters the correct formula it prompts you twice more to open the workbook before entering the formula. Script below, with comments. Thanks in advance for any help you can offer.
Sub Find_Already_Commit()
Dim mwb As Workbook, vwb As Workbook
Dim vfPath As String, vfName As String, str As String
Dim fd As FileDialog
Application.ScreenUpdating = False
Set mwb = ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Filters.Add "Excel Files", "*.xls*"
.AllowMultiSelect = False
.Title = "Select your Current Tooling Commit Workbook file to open!"
If .Show <> -1 Then
MsgBox "You didn't select any file", vbExclamation, "No File Selected!"
Exit Sub
End If
vfName = .SelectedItems(1)
End With
Workbooks.Open vfName
Set vwb = ActiveWorkbook
mwb.Activate
Range("AJ19").FormulaR1C1 = "CURRENT COMMIT VENDOR"
Range("AJ19").WrapText = True
'For some reason the formula below prompts me again to select a workbook to open.
'I can select the same file I selected earlier. It will then prompt me again.
'I can agains select the file I selected earlier, and the remainder of the script
'works correctly, giving me the results I expect.
Range("AJ20").FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC2,'[vwb]MASTER'!R13C2:R15000C33,32,FALSE),"""")"
Range("AJ20").Copy
Range("AJ20:AJ" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial xlPasteFormulas
Selection.Value = Selection.Value
Application.CutCopyMode = False
vwb.Close savechanges = False
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Data has been imported successfully.", vbInformation, "Task Completed!"
End Sub
Bookmarks