VBA, Runtime Error 1004, Application defined or Object-defined error

[Cassius]

Hello,

I'm a beginner in need of some help please (Excel 2013).

I have the below code which loops through the first sheet (Input) taking the value on the 1st row and the value on the first column where there is an intersecting "X" and pasting them into the second sheet (Output).

The code worked well until the first sheet grew and now I have to split the sheet into 3 and run separately otherwise I get a Runtime Error 1004, Application defined or Object-defined error.

I thought I could just define the LastRow and Column as Long but that doesnt seem to work. The error is in the line starting with 'Range'.

Any help is appreciated.

Thank you.

Sub Newest()


Dim LastRow As Long
Dim LastCol As Long


With ActiveWorkbook.Sheets("Input")


LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
inarr = Range(.Cells(1, 1), .Cells(LastRow, LastCol))


End With
With Worksheets("Output")
Range(.Cells(1, 1), .Cells(LastRow * LastCol, 2)) = ""
outarr = Range(.Cells(1, 1), .Cells(LastRow * LastCol, 2))
indi = 1
For i = 2 To LastCol
 For j = 2 To LastRow
   If inarr(j, i) = "X" Then
    outarr(indi, 1) = inarr(1, i)
    outarr(indi, 2) = inarr(j, 1)
    indi = indi + 1
   End If
 Next j
Next i


Range(.Cells(1, 1), .Cells(LastRow * LastCol, 2)) = outarr


End With
End Sub

 

[Fluff]

You're welcome & thanks for the feedback