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]

How many rows & columns do you have in the Input sheet?

 

[Cassius]

How many rows & columns do you have in the Input sheet?

There are 180 columns and about 16,000 rows.

 

[NdNoviceHlp]

You are missing the preceding period....

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

HTH. Dave

 

[Fluff]

The problem is that you are trying to work with 2,880,000 rows, when Xl only has 1,048,576
How many cells are you likely to have with an X

 

[Cassius]

The problem is that you are trying to work with 2,880,000 rows, when Xl only has 1,048,576
How many cells are you likely to have with an X

Thanks for helping, It didn't work with 23,500 X's.

 

[Fluff]

That's because you are trying to clear a range that's twice as large as the number of rows available.

 

[Fluff]

Try

Sub Cassius()
   Dim InArr As Variant, OutArr As Variant
   Dim LastRow As Long, LastCol As Long
   Dim i As Long, j As Long, indi 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)).Value2
      indi = Application.CountIf(Range(.Cells(1, 1), .Cells(LastRow, LastCol)), "X")
   End With
   
   With Worksheets("Output")
      .UsedRange.Value = ""
      ReDim OutArr(1 To indi, 1 To 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("A1").Resize(indi, 2).Value = OutArr
   End With
End Sub

 

[Cassius]

Try

Sub Cassius()
   Dim InArr As Variant, OutArr As Variant
   Dim LastRow As Long, LastCol As Long
   Dim i As Long, j As Long, indi 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)).Value2
      indi = Application.CountIf(Range(.Cells(1, 1), .Cells(LastRow, LastCol)), "X")
   End With
   
   With Worksheets("Output")
      .UsedRange.Value = ""
      ReDim OutArr(1 To indi, 1 To 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("A1").Resize(indi, 2).Value = OutArr
   End With
End Sub

Thank you, I'll give it a go when I get in.

 

[Cassius]

Worked like a treat, thank you Fluff.