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?
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
