"Run-time error '1004': Application-defined or object-defined error" with ranges in Excel VBA

0
votes

I have written the code below for VBA (Excel 2015). I am getting a "Run-time error '1004': Application-defined or object-defined error" at Set Cell(i, j) = Sheets(c).Cells(Cell(i, j)). I'm trying to redefine the ranges in the "Cell" array as the identical ranges in a different sheet.

Dim c As Integer, i As Integer, j As Integer, k As Integer, l As Integer, box As Range, Cell(6 To 24, 3 To 5) As Range

For i = 6 To 24

    For j = 3 To 5

        v = Sheets("Capture").Cells(i, j).Value

        For k = 1 To 376

            For l = 1 To 20

                If Sheets(1).Cells(k, l).Value = v Then

                    Set box = Sheets(1).Cells(k, l)

                End If

            Next l

        Next k

        Set Cell(i, j) = box

    Next j

Next i

For c = 1 To 7

    If Sheets("Capture").Cells(2, 2).Value = c Then

        For i = 6 To 24

            For j = 3 To 5
                ' Error: Application-defined or object-defined error
                Set Cell(i, j) = Sheets(c).Cells(Cell(i, j))

                Sheets("Capture").Cells(i, j).Value = Cell(i, j).Value

            Next j

        Next i

    End If

Next c
1
vbaexcel

1 Answers

2
votes

You need to change:

Set Cell(i, j) = Sheets(c).Cells(Cell(i, j))

To: 

Set Cell(i, j) = Sheets(c).Cells(i, j)