Debug error points to:
BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"), Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")
Sub is below:
Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer
Dim PartRow As Integer
Dim SourceRow As Integer
Dim SourceWorksheet As String
Dim Column(1000) As String
Dim ColumnCount As Integer
Dim j As Long
Dim i As Integer
Dim Msg As String
Dim BuildPlan(5000000 To 5200000, 2014 To 2016, 1 To 12) As Long
Dim PartNumbers(5000000 To 5200000) As String
Column(0) = "A"
Column(1) = "B"
Column(2) = "C"
Column(3) = "D"
Column(4) = "E"
Column(5) = "F"
Column(6) = "G"
Column(7) = "H"
Column(8) = "I"
Column(9) = "J"
Column(10) = "K"
Column(11) = "L"
Column(12) = "M"
Column(13) = "N"
Column(14) = "O"
Column(15) = "P"
Column(16) = "Q"
Column(17) = "R"
Column(18) = "S"
Column(19) = "T"
Column(20) = "U"
Column(21) = "V"
Column(22) = "W"
Column(23) = "X"
Column(24) = "Y"
Column(25) = "Z"
' Define Column Array
ColumnCount = 26
For j = 0 To 10
For i = 0 To 25
Column(ColumnCount) = Column(j) + Column(i)
ColumnCount = ColumnCount + 1
Next i
Next j
Application.Calculation = xlCalculationManual
Application.Cursor = xlWait
j = Range("Forecast_MonthPartList").Rows(1).Row
Do While Worksheets("Forecast").Cells(j, "C") <> ""
If Worksheets("Forecast").Cells(j, "C").Interior.ColorIndex = xlNone Then
Worksheets("Forecast").Range("D" & j, "AA" & j).Value = ""
End If
j = j + 1
Loop
' Read Forecast from SupplyDemand data sheet
SourceWorksheet = "T_Rp1_SupplyDemandData_byMonth"
j = 2
Do While Worksheets(SourceWorksheet).Cells(j, "A") <> ""
BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"), Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")
j = j + 1
Loop
' Populate Forecast worksheet
For PartRow = Range("Forecast_MonthPartList").Rows(1).Row To Range("Forecast_MonthPartList").Rows(Range("Forecast_MonthPartList").Rows.Count).Row
For Year = 2014 To 2016
For Month = 1 To 12
If Worksheets("Forecast").Cells(PartRow, "C").Interior.ColorIndex = xlNone Then
If Year = 2014 Then
If Worksheets("Forecast").Cells(PartRow, "C").Value = 5122053 Then
Worksheets("Forecast").Cells(PartRow, Column(Month + 2)).Value = "=4*" & BuildPlan(Worksheets("Forecast").Cells(PartRow, "C").Value, Year, Month)
Else
Worksheets("Forecast").Cells(PartRow, Column(Month + 2)).Value = BuildPlan(Worksheets("Forecast").Cells(PartRow, "C").Value, Year, Month)
End If
Else
If Worksheets("Forecast").Cells(PartRow, "C").Value = 5122053 Then
Worksheets("Forecast").Cells(PartRow, Column(Month + 14)).Value = "=4*" & BuildPlan(Worksheets("Forecast").Cells(PartRow, "C").Value, Year, Month)
Else
Worksheets("Forecast").Cells(PartRow, Column(Month + 14)).Value = BuildPlan(Worksheets("Forecast").Cells(PartRow, "C").Value, Year, Month)
End If
End If
End If
Next Month
Next Year
Next PartRow
' Message Box showing part numbers not uploaded from data
j = 2
Do While Worksheets(SourceWorksheet).Cells(j, "A") <> ""
PartNumbers(Worksheets(SourceWorksheet).Cells(j, "A")) = Worksheets(SourceWorksheet).Cells(j, "B")
j = j + 1
Loop
Msg = "Part #'s Not Loaded:" & vbCr
For j = 5000000 To 5200000
If PartNumbers(j) <> "" Then
For PartRow = Range("Forecast_MonthPartList").Rows(1).Row To Range("Forecast_MonthPartList").Rows(Range("Forecast_MonthPartList").Rows.Count).Row
If Worksheets("Forecast").Cells(PartRow, "C") = j Then
PartNumbers(j) = ""
End If
Next PartRow
End If
If PartNumbers(j) <> "" Then
Msg = Msg & j & " " & vbCr
End If
Next j
MsgBox Prompt:=Msg
Application.Calculation = xlCalculationAutomatic
Application.Cursor = xlDefault
End Sub
Bookmarks