locked
Excel script fails in Excel 2013 but works fine in Excel 2010 RRS feed

  • Question

  • Hello  

    We have a excel script that work 100% in excel 2010 but we upgrade our systems to excel 2013  now the scrip only returns two items.    Any thoughts ?

    Sub Button1_Click()

    'find out how many rows of data there are
      Dim lastRow As Long
      Dim MyRange As Range
      Dim newInv As Worksheet
      Set newInv = Worksheets("Sheet2")
      Set MyRange = Worksheets("Sheet1").Range("C" & "1")
      lastRow = Cells(65536, MyRange.Column).End(xlUp).Row
      Dim Ws As Worksheet
      Set Ws = ActiveSheet
    'go through each row
    'Dim rnArea As Range

    'Set rnArea = Range("F2:G" & lastRow)
        'Dim r As Row
         
    Dim onHand As Integer
    Dim reorderPt As Integer
    Dim destRow As Integer
    destRow = 2
    For i = 2 To lastRow


      'MsgBox ("Row # " & i)
      If IsEmpty((Cells(i, 8).Value)) Then Cells(i, 8).Value = -10
      onHand = CInt(Cells(i, 7).Value)
      reorderPt = CInt(Cells(i, 8).Value)
      

     If onHand <= reorderPt Then
      Ws.Rows(i).Copy
      'copy selection?
      newInv.Rows(destRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
      destRow = destRow + 1
     End If
    'for i =
    'if items on hand <= reorder point
    'copy column
    'paste to a new blank worksheet
    Next i
    End Sub
    Tuesday, September 17, 2013 10:24 PM

Answers

  • Krystle,

    I have no ready explanation for why stepping thru works and running the code doesn't.
    That does say something needs fixing. (excel 2013 users have reported lots of problems)
    If you are Automating Excel from another application then that code may need looking at.
    I've cleaned up the code to my liking and changed a few things.

    Both worksheets have object references set and the references are used for all callouts.
    (notice the use in front of "cells")

    Error handling was added.

    The integer variables were changed to Longs.
    (if you have reorder points or inventory levels above/below ~32000, the code would fail)

    Replaced copy and paste with Value2 = Value2.
    Copy and paste is used as a backup if an error is thrown.

    This all worked once for me. ( I don't have live with it) <g>
    If all else fails, and maybe before trying my code revisions, you should set the VBE to "break on all errors" and see what turns up.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)   


    'CODE FOLLOWS...

    Sub Button1_Click()
     On Error GoTo NotInOrder
     Dim newInv As Worksheet
     Dim Ws       As Worksheet
     Dim i            As Long
     Dim onHand  As Long
     Dim lastRow  As Long
     Dim destRow As Long
     Dim reorderPt As Long

     Set Ws = Worksheets("Sheet1")
     Set newInv = Worksheets("Sheet2")
     lastRow = Ws.Cells(Ws.Rows.Count, 3).End(xlUp).Row
     destRow = 2

     For i = 2 To lastRow
       If IsEmpty((Ws.Cells(i, 8).Value)) Then Ws.Cells(i, 8).Value = -10
       onHand = CLng(Ws.Cells(i, 7).Value2)
       reorderPt = CLng(Ws.Cells(i, 8).Value2)

       If onHand <= reorderPt Then
         On Error Resume Next
         newInv.Rows(destRow).Value2 = Ws.Rows(i).Value2
         If Err.Number <> 0 Then
           Ws.Rows(i).Copy
           newInv.Rows(destRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                  SkipBlanks:=False, Transpose:=False
           Application.CutCopyMode = False
         End If
         On Error GoTo NotInOrder
         destRow = destRow + 1
       End If
     Next 'i
     Exit Sub

    NotInOrder:
     MsgBox "Error " & Err.Number & vbCr & Err.Description
    End Sub


    • Marked as answer by George Hua Wednesday, September 25, 2013 4:25 PM
    • Edited by James Cone Tuesday, November 1, 2016 1:25 AM
    Wednesday, September 18, 2013 8:55 PM

All replies

  • Thoughts...

    variable i is not declared

    There are ~1,000,000 rows on a sheet, yet you are only checking the top portion of the sheet...
      lastRow = Cells(65536, MyRange.Column).End(xlUp).Row
    The above should be...
      lastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row

    Should a reorder point be a negative value? (-10)
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, November 1, 2016 1:23 AM
    Wednesday, September 18, 2013 4:02 AM
  • Thanks Jim,

    While your suggestion is cleaner, it doesn’t actually change anything, as I never have more than a few thousand rows of data.

    I have the reorder points set to -10 for some items, so yes, this is common.

    What I should have added is that if I put a breakpoint in my code, and step through it, it works fine.

    But when I run the entire code at once it only returns two items – with no error message.

    Thanks!

    Wednesday, September 18, 2013 6:31 PM
  • Krystle,

    I have no ready explanation for why stepping thru works and running the code doesn't.
    That does say something needs fixing. (excel 2013 users have reported lots of problems)
    If you are Automating Excel from another application then that code may need looking at.
    I've cleaned up the code to my liking and changed a few things.

    Both worksheets have object references set and the references are used for all callouts.
    (notice the use in front of "cells")

    Error handling was added.

    The integer variables were changed to Longs.
    (if you have reorder points or inventory levels above/below ~32000, the code would fail)

    Replaced copy and paste with Value2 = Value2.
    Copy and paste is used as a backup if an error is thrown.

    This all worked once for me. ( I don't have live with it) <g>
    If all else fails, and maybe before trying my code revisions, you should set the VBE to "break on all errors" and see what turns up.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)   


    'CODE FOLLOWS...

    Sub Button1_Click()
     On Error GoTo NotInOrder
     Dim newInv As Worksheet
     Dim Ws       As Worksheet
     Dim i            As Long
     Dim onHand  As Long
     Dim lastRow  As Long
     Dim destRow As Long
     Dim reorderPt As Long

     Set Ws = Worksheets("Sheet1")
     Set newInv = Worksheets("Sheet2")
     lastRow = Ws.Cells(Ws.Rows.Count, 3).End(xlUp).Row
     destRow = 2

     For i = 2 To lastRow
       If IsEmpty((Ws.Cells(i, 8).Value)) Then Ws.Cells(i, 8).Value = -10
       onHand = CLng(Ws.Cells(i, 7).Value2)
       reorderPt = CLng(Ws.Cells(i, 8).Value2)

       If onHand <= reorderPt Then
         On Error Resume Next
         newInv.Rows(destRow).Value2 = Ws.Rows(i).Value2
         If Err.Number <> 0 Then
           Ws.Rows(i).Copy
           newInv.Rows(destRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                  SkipBlanks:=False, Transpose:=False
           Application.CutCopyMode = False
         End If
         On Error GoTo NotInOrder
         destRow = destRow + 1
       End If
     Next 'i
     Exit Sub

    NotInOrder:
     MsgBox "Error " & Err.Number & vbCr & Err.Description
    End Sub


    • Marked as answer by George Hua Wednesday, September 25, 2013 4:25 PM
    • Edited by James Cone Tuesday, November 1, 2016 1:25 AM
    Wednesday, September 18, 2013 8:55 PM