Excel VBA: Error 1004 application-defined or object-defined error

Question

I am having trouble debugging a code I wrote. It runns fine until a certain point and then stops and shows: runtime error 1004 application-defined or object-defined error.

I have not written VBA in a very long time so my code might be a mess =).

The problem seems to be with: see comment 'Generate UID for the Current Imp

'Sheet Definitions
strSourceSheet = "MasterReport"
strComponentSheet = "UniqueIdComponents"

'defines row where data starts
intEntryCount = 2
intImpCount = 0

'determine maximum number of rows for both sheets
lngMaxRowSS = ThisWorkbook.Sheets(strSourceSheet).UsedRange.SpecialCells(xlCellTypeLastCell).Row
lngMaxRowCS = ThisWorkbook.Sheets(strComponentSheet).UsedRange.SpecialCells(xlCellTypeLastCell).Row

'Run until there are no more entries
For intEntryCount = 2 To lngMaxRowSS

   'Prevents to overwrite existing UIDs
   If ThisWorkbook.Sheets(strSourceSheet).Range("BP" & intEntryCount) = "" Then

    'Recieve next imperative on the Source List to find according UID Components
    strSourceImperative = ThisWorkbook.Sheets(strSourceSheet).Range("A" & intEntryCount)

        'Run until no new Imp UID is defind
        For intImpCount = 11 To lngMaxRowCS

            'Location of Imps on Component Sheet
            strComponentImperative = ThisWorkbook.Sheets(strComponentSheet).Range("C" & intImpCount)

            ' If the Source Imp = Component Imp then we create a UID for that Source IP
            If strSourceImperative = strComponentImperative Then

                'Assign Column to UID component in order to find the Column in the MasterReport
                strUIDComponent1 = ThisWorkbook.Sheets(strComponentSheet).Range("D" & intImpCount)
                strUIDComponent2 = ThisWorkbook.Sheets(strComponentSheet).Range("E" & intImpCount)
                strUIDComponent3 = ThisWorkbook.Sheets(strComponentSheet).Range("F" & intImpCount)
                strUIDComponent4 = ThisWorkbook.Sheets(strComponentSheet).Range("G" & intImpCount)
                strUIDComponent5 = ThisWorkbook.Sheets(strComponentSheet).Range("H" & intImpCount)
                strUIDComponent6 = ThisWorkbook.Sheets(strComponentSheet).Range("I" & intImpCount)
                strUIDComponent7 = ThisWorkbook.Sheets(strComponentSheet).Range("J" & intImpCount)

                'Generate UID for the Current Imp
                strUID = ThisWorkbook.Sheets(strSourceSheet).Range(strUIDComponent1 & intEntryCount) _
                        & ThisWorkbook.Sheets(strSourceSheet).Range(strUIDComponent2 & intEntryCount) _
                        & ThisWorkbook.Sheets(strSourceSheet).Range(strUIDComponent3 & intEntryCount) _
                        & ThisWorkbook.Sheets(strSourceSheet).Range(strUIDComponent4 & intEntryCount) _
                        & ThisWorkbook.Sheets(strSourceSheet).Range(strUIDComponent5 & intEntryCount) _
                        & ThisWorkbook.Sheets(strSourceSheet).Range(strUIDComponent6 & intEntryCount) _
                        & ThisWorkbook.Sheets(strSourceSheet).Range(strUIDComponent7 & intEntryCount)

                'Writes UID into MasterReport
                'ThisWorkbook.Sheets(strSourceSheet).Range("BP" & intEntryCount) = strUID

                'Test Writes
                ThisWorkbook.Sheets("Test").Range("A" & intEntryCount) = strUID

            'If the Source Imp = Component Imp then we created a UID for that Source IP
            End If

        'If the two Source Imp <> Component Imp, go to next row on Component sheet and compare again
        Next intImpCount

   'Prevented to overwrite existing UIDs
   End If

Next intEntryCount

In the case where i get the error the components are A,M,N,O,BK,"","" and the Entry count is 5718. It wrote 5718 entries just fine and then shows the Error.

Any Ideas?

Thanks in advance for your help!!

Answer 1

Taking ThisWorkbook.Sheets(strSourceSheet) out to a With statement and substituting values, you appear to be saying this statement is equivalent to:

With ThisWorkbook.Sheets(strSourceSheet)

  strUID = .Range("A" & 5718) & _
           .Range("M" & 5718) _
           .Range("N" & 5718) _
           .Range("O" & 5718) _
           .Range("BK" & 5718) _
           .Range("" & 5718) _
           .Range("" & 5718)

End With

The last two entries are not valid ranges.

I cannot test this code but something like this might meet your requirements:

  Dim WkShtComp As Worksheet
  Dim WkShtSrc As Worksheet
  Dim ColMast As String

  With ThisWorkbook
    Set WkShtComp = .Sheets(strComponentSheet)
    Set WkShtSrc = .Sheets(strSourceSheet)
  End With

  strUID = ""
  For Each ColMast in Array("D", "E", "F", "G", "H", "I", "J")
     strUIDComponent = WKShtComp.Range(ColMast & intImpCount).Value
     If strUIDComponent <> "" Then
       strUID = strUID & WkShtSrc.Range(strUIDComponent & intEntryCount).Value
     Endif
  Next