Open File Dialog box - VBA

rilzniak

Active Member
Joined
Jul 20, 2012
Messages
291
I'm losing my mind and need some help. I might just be missing something having stared at my screen for way too long today.

I have some code that puts a formula in a cell which references a closed workbook, spits out the result, and then pastes it as a value. What I can't figure it is:

1) Why the Open File Dialog box pops up every time I run the code. I've specified where to get the data from (at least I believe I have);
2) Why the VLOOKUP trips the code.

Can someone have a quick look and see if there's something glaring that I've missed? Or perhaps offer suggestions to my expanding code mess?

Code:
Option Explicit

Public i As Integer, j As Byte, m As Byte, MyRowCount As Long, IIRWProdCol As Byte, IIRWCounted As Byte, IIRWCalculated As Byte, LastColumn As Integer, StartRow As Integer, EndRow As Integer
Public Path1 As String, Path2 As String, Path3 As String, Path4 As String
Public SheetName As String, ArrayBegCol As String, ArrayEndCol As String, LookUpPeriod As String, Source As String, Filename As String
Public ProdColLetter As String, CountedCol As String, CalculatedCol As String
Public MinutesElapsed As Integer
Public StartDate As Date, EndDate As Date
Public StartTime As Double

Public Sub IIRWce()
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

StartTime = Timer

ActiveWorkbook.Sheets("Inventory").Activate
MyRowCount = Application.CountA(Range("A:A"))
LastColumn = ActiveWorkbook.Sheets("Inventory").Cells(2, Columns.Count).End(xlToLeft).Column
StartRow = 26
EndRow = 26

For i = StartRow To EndRow
    
    Path1 = Range("A" & i).Value
    Path2 = Range("B" & i).Value
    Path3 = Range("C" & i).Value
    Path4 = Range("D" & i).Value
    Filename = Range("E" & i).Value
    Source = Range("F" & i).Value
    LookUpPeriod = Range("H1")
    StartDate = Range("I1")
    ArrayBegCol = Range("J" & i).Value
    ArrayEndCol = Range("K" & i).Value
    SheetName = Range("L" & i).Value
    EndDate = Range("L1")

    IIRWProdCol = 2
    IIRWCounted = 10
    IIRWCalculated = 11
    ProdColLetter = Chr(WorksheetFunction.Match("Produced", Sheets("Inventory").Range("2:2"), 0) + 64)
    CountedCol = WorksheetFunction.Match("Counted", Sheets("Inventory").Range("2:2"), 0)
    CalculatedCol = WorksheetFunction.Match("Calculated", Sheets("Inventory").Range("2:2"), 0)
           
    If LookUpPeriod = "Date" Then
            
            Call Production_M_Date
            Call Inventory_M_Date

        Else

            Call Production_M
            Call Inventory_M
        
    End If


Cells.Replace "", 0

Next i


Application.Calculation = xlCalculationAutomatic

Range("M" & StartRow & ":V" & EndRow + 1).Copy
Range("M" & StartRow & ":V" & EndRow + 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("H1").Activate

Cells.Replace "#N/A", 0, xlWhole

'Determine how many seconds code took to run
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

'Notify user in seconds
MsgBox "Run twice, the first time doesn't work!" & vbNewLine & "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

--------

Sub Production_M_Date()

    Range(ProdColLetter & i).Value = "=VLOOKUP(I1,'" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!" & ArrayBegCol & ":" & ArrayEndCol & "," & IIRWProdCol & ",FALSE)"

End Sub

--------

Sub Inventory_M_Date()
   
    For j = 13 To 21 'loop from column M to V on Inventory worksheet in IIRW file
        ActiveSheet.Cells(i, j).Value = "=IFERROR(VLOOKUP(I1,'" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!" & ArrayBegCol & ":" & ArrayEndCol & "," & j - 8 & ",FALSE),0)"
    Next j
    
End Sub

--------

Sub Production_M()
     
Range(ProdColLetter & i).Value = "=SUMPRODUCT(--('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C3:R370C3>=date(" & Year(StartDate) & "," & Month(StartDate) & "," _
    & Day(StartDate) & ")),--('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C3:R370C3<=date(" & Year(EndDate) & "," & Month(EndDate) & "," & Day(EndDate) _
    & ")),('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C4:R370C4))"
    
End Sub

--------

Sub Inventory_M()

    For m = 13 To 20
        ActiveSheet.Cells(i, m).Value = "=SUMPRODUCT(--('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C3:R370C3>=date(" & Year(StartDate) & "," _
        & Month(StartDate) & "," & Day(StartDate) & ")),--('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C3:R370C3<=date(" & Year(EndDate) & "," _
        & Month(EndDate) & "," & Day(EndDate) & ")),('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C" & m - 9 & ":R370C" & m - 9 & "))"
    Next m
        
    ActiveSheet.Cells(i, CountedCol).Value = "=VLOOKUP(I1,'" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!" & ArrayBegCol & ":" & ArrayEndCol & "," & IIRWCounted & ",FALSE)"
    ActiveSheet.Cells(i, CalculatedCol).Value = "=VLOOKUP(I1,'" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!" & ArrayBegCol & ":" & ArrayEndCol & "," & IIRWCalculated & ",FALSE)"

End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hello,

The macro you posted does not seem to open a workbook ...:confused:
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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