Runtime error 1004 - Application Defined or Object Defined error

[andreroux]

I have a macro, that worked before I put in a For...Next Statement. Now I get an error which did not happen before the changes. The error reads: Runtime error 1004 - Application Defined or Object Defined error.

The line on which I get the error is as follows: ActiveSheet.Range("J" & ROW_NO).Select and I cannot figure out why. The ROW_NO is defined as Dim ROW_NO As Long and is initialised as follows:ROW_NO = 2

For the sake of completeness, I supply the entire code below:

Sub Macro1()
'
' Macro1 Macro
'
' ============================
' Define Variables & Populate them
' ============================
   Dim fname As String
   Dim FIL_NAM1 As Long
   Dim ROW_NO As Long
   Dim INVCNT As Long
   Dim COUNTER As Long
   Dim ws1 As Worksheet
   FIL_NAM1 = 2
   ROW_NO = 7
   COUNTER = 1
   
' ==================================================
' Copy First Invoice Detail to Invoice Control Sheet
' ==================================================
        Workbooks.Open Filename:= _
        "C:\Users\E-Machine\Dropbox\Technology Station - VUT\Master Invoice TS.xlsm"
        Sheets("Invoices").Select
        Range("D" & FIL_NAM1).Select
        
        INVCNT = Range("C2").Value
    For COUNTER = 1 To INVCNT
        DoWork
    Next COUNTER
        
        

End Sub
Sub DoWork()
        fname = "C:\Users\E-Machine\Dropbox\Technology Station - VUT\Invoices\" & ActiveCell.Value
        Workbooks.Open fname
        Cells.Select
        Selection.Copy
        ActiveWindow.Close
        Windows("Invoice Control Sheet.xlsm").Activate
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        ActiveSheet.Range("I1:J1").Select
        Selection.Copy
        Sheets("Sheet1").Select
        ActiveSheet.Range("J" & ROW_NO).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("I2:J2").Select
        Application.CutCopyMode = False
        Selection.Copy
        Application.WindowState = xlMinimized
        Windows("Master Invoice TS.xlsm").Activate
        Application.WindowState = xlNormal
        Windows("Invoice Control Sheet.xlsm").Activate
        Sheets("Sheet1").Select
        Range("B" & ROW_NO).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("I3:J3").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("C" & ROW_NO).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("B5:D5").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("D" & ROW_NO).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("I4:J4").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("G" & ROW_NO).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("I21:J21").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("K" & ROW_NO).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False
        Range("K" & ROW_NO).Select
        Application.CutCopyMode = False
        Selection.Style = "Comma"
        Sheets("Sheet2").Select
        ActiveWindow.SelectedSheets.Delete
        Windows("Invoice Control Sheet.xlsm").Activate
        Application.WindowState = xlNormal
        FIL_NAM1 = FIL_NAM1 + 1
        ROW_NO = ROW_NO + 1
    
        Sheets("Sheet1").Select
End Sub

[AB33]

Andre,
Please use code tags with your code. The code has almost filled-up the entire page.

[andreroux]

HI AB33

Sorry about that. Only figured out now how to do it. Big OOPS on my side.

Thanx for helping me right

Andre

[Norie]

When you get the error ROW_NO has no value.

[andreroux]

Hi Norie

I see what U r saying. But at the beginning of the macro, I do give ROW_NO a value of 7 with the line: ROW_NO = 7. Maybe U are seeing something that I am missing. I am, after all, a novice with VBA

Regards

Andre

[Norie]

Andre

You've declared and given ROW_NO a value in the first sub (Macro1), it's not recognised in the second sub (DoWork).

[andreroux]

Hi Norie,

THanx for pointing me in the right direction. It is all about logic! I still have other small problems, but I think I can handle that now that I know how VBA thinks

Thanx a lot!

Andre