+ Reply to Thread
Results 1 to 3 of 3

Taking over a spread sheet with a macro not working, (I'm just a novice with VBA)

  1. #1
    Registered User
    Join Date
    06-03-2014
    Posts
    2

    Taking over a spread sheet with a macro not working, (I'm just a novice with VBA)

    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

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Taking over a spread sheet with a macro not working, (I'm just a novice with VBA)

    Hi, and welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    In addition the thread title hardly meets our 'descriptive' rule and would be better changed.


    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-03-2014
    Posts
    2

    Re: Taking over a spread sheet with a macro not working, (I'm just a novice with VBA)

    Have no idea where # is on your post screen. Tried to format things and a bunch of stuff came up instead of what I intended. never mind then I'll just delete this account.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 03-04-2013, 01:15 PM
  2. working with more than one spread sheet
    By skhv in forum Excel General
    Replies: 1
    Last Post: 12-11-2012, 12:25 PM
  3. Macro - Taking the value of a cell and finding it on another sheet
    By mrrobward in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-01-2012, 07:01 AM
  4. Using a macro to record and paste to new spread sheet not working
    By oniete1997 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2012, 03:11 AM
  5. Taking info from 1 spread sheet to another
    By redhand in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-23-2008, 08:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1