none
Application-defined or Object-defined error '1004': RRS feed

  • General discussion

  • Hi,

    So im trying to write a macro (to be honest im brand new to Visual Basic) that goes through all the worksheets in a workbook and checks if there are any changes in the work sheet values in column 11 and add them up and output it on an other worksheet. I cant wrap my head around this error "Application-defined or Object-defined error '1004':" and its always the bolded line. The rest of the it works but i dont know why that one line keeps giving me an error. Please help!!

    Sub Main()
        Dim crow As Integer 'current row in detail
        Dim ocrow As Integer 'current row in overview
        Dim TotalChangesOverview As Integer
        TotalChangesOverview = 0
       
        For Each ws In Worksheets
            Dim Last As Integer
            Dim TotalChangesDetail As Integer
            Last = LastRowIndex(ActiveSheet, 1)
           
            'TotalChangesDetail = 0
           
            TotalChangesDetail = ActiveSheet.Cells(Last, 11).Value - ActiveSheet.Cells((Last - 1), 11).Value
            TotalChangesOverview = TotalChangesOverview + TotalChangesDetail
         Next ws
       
        Output (TotalChangesOverview)
    End Sub

    Function LastRowIndex(ByVal w As Worksheet, ByVal col As Variant) As Long
      Dim r As Range

      Set r = Application.Intersect(w.UsedRange, w.Columns(col))
      If Not r Is Nothing Then
        Set r = r.Cells(r.Cells.Count)

        If IsEmpty(r.Value) Then
          LastRowIndex = r.End(xlUp).row
        Else
          LastRowIndex = r.row
        End If
      End If
    End Function
    Function Output(ByVal total As Integer)
        Worksheets("WeeklyOverview").Select

        Dim Last As Integer
        Last = LastRowIndex(ActiveSheet, 1)
       
        ActiveSheet.Cells((Last + 1), 2).Value = total
       
    End Function

    Tuesday, March 5, 2013 5:30 PM

All replies

  • You don't want to use activesheet when you are looping:

    Sub Main()
        Dim crow As Integer 'current row in detail
        Dim ocrow As Integer 'current row in overview
        Dim WS As Worksheet
        Dim TotalChangesOverview As Integer
        TotalChangesOverview = 0
       
        For Each WS In Worksheets
            Dim Last As Integer
            Dim TotalChangesDetail As Integer
            Last = LastRowIndex(WS, 1)
           
            'TotalChangesDetail = 0
           
            TotalChangesDetail = WS.Cells(Last, 11).Value - WS.Cells((Last - 1), 11).Value
            TotalChangesOverview = TotalChangesOverview + TotalChangesDetail
         Next WS
       
        Output (TotalChangesOverview)
    End Sub

    Function LastRowIndex(ByVal w As Worksheet, ByVal col As Variant) As Long
      Dim r As Range

      Set r = Application.Intersect(w.UsedRange, w.Columns(col))
      If Not r Is Nothing Then
        Set r = r.Cells(r.Cells.Count)
        If IsEmpty(r.Value) Then
          LastRowIndex = r.End(xlUp).Row
        Else
          LastRowIndex = r.Row
        End If
      End If
    End Function

    Function Output(ByVal total As Integer)
        Dim Last As Long
        Last = LastRowIndex(Worksheets("WeeklyOverview"), 1)
        Worksheets("WeeklyOverview").Cells((Last + 1), 2).Value = total
    End Function

    Tuesday, March 5, 2013 6:05 PM
  • hmmm i just tried that and it doenst work :S

    Tuesday, March 5, 2013 6:08 PM
  • Is it possible that the cells being found don't have numbers - but have errors, strings, or other non-numeric values? I tested the code before I posted, and it worked for my workbook with 5 sheets and numbers in column K (column 11). Is it also possible that column A is completely empty on any sheet? If Last has a value of zero or 1, then it will give you that error, so try this version of main:



    Sub Main()
        Dim crow As Integer 'current row in detail
        Dim ocrow As Integer 'current row in overview
        Dim WS As Worksheet
        Dim TotalChangesOverview As Integer
        TotalChangesOverview = 0
       
        For Each WS In Worksheets
            Dim Last As Integer
            Dim TotalChangesDetail As Integer
            Last = LastRowIndex(WS, 1)
           
            'TotalChangesDetail = 0
            If Last > 1 Then
            TotalChangesDetail = WS.Cells(Last, 11).Value - WS.Cells((Last - 1), 11).Value
            TotalChangesOverview = TotalChangesOverview + TotalChangesDetail
            End If
         Next WS
       
        Output (TotalChangesOverview)
    End Sub
    Tuesday, March 5, 2013 6:55 PM
  • Okay so one of the worksheets called "Overview" doesnt have any data in column 11 so i chagned up the code alittle bit but it still gives me that errorr :S
    Sub Main()
        Dim crow As Integer 'current row in detail
        Dim ocrow As Integer 'current row in overview
        Dim TotalChangesOverview As Integer
        TotalChangesOverview = 0
        
        For Each WS In Worksheets
            If (Worksheets(ActiveSheet).Name = "Overview") Then
                Resume Next
            End If
            
            Dim Last As Integer
            Dim TotalChangesDetail As Integer
            Last = LastRowIndex(ActiveSheet, 1)
            
            TotalChangesDetail = WS.Cells(Last, 11).Value - WS.Cells((Last - 1), 11).Value
            TotalChangesOverview = TotalChangesOverview + TotalChangesDetail
         Next WS
        
        Output (TotalChangesOverview)
    End Sub
    Function LastRowIndex(ByVal w As Worksheet, ByVal col As Variant) As Long
      Dim r As Range
      Set r = Application.Intersect(w.UsedRange, w.Columns(col))
      If Not r Is Nothing Then
        Set r = r.Cells(r.Cells.Count)
        If IsEmpty(r.Value) Then
          LastRowIndex = r.End(xlUp).row
        Else
          LastRowIndex = r.row
        End If
      End If
    End Function
    Function Output(ByVal total As Integer)
        Worksheets("WeeklyOverview").Select
        Dim Last As Integer
        Last = LastRowIndex(ActiveSheet, 1)
        
        ActiveSheet.Cells((Last + 1), 2).Value = total
        
    End Function

    Tuesday, March 5, 2013 7:44 PM
  • It is column A you need to worry about, since LastRowIndex is being passed 1 as the column to check.

    But, to catch that error, change

           
            TotalChangesDetail = WS.Cells(Last, 11).Value - WS.Cells((Last - 1), 11).Value
           
    TotalChangesOverview = TotalChangesOverview + TotalChangesDetail
     

    to

           
            If Last >1 Then

            TotalChangesDetail = WS.Cells(Last, 11).Value - WS.Cells((Last - 1), 11).Value
           
    TotalChangesOverview = TotalChangesOverview + TotalChangesDetail

            End if


    Tuesday, March 5, 2013 8:00 PM
  •  i think i got it to work! thank you soo much!
    Tuesday, March 5, 2013 8:32 PM