fixing error code create report based on criteria


hi, experts

i got this code from the internet and i try  amending what i need to creating report  based on criteria  col 3 = q8      it should copy theses data from sheet1 to sheet 2   but it gives me run time error 1004  "application-defined or  object-defined error" in this line 

Sheets("Sheet2").Range("A2").Resize(k, UBound(arr, 2)).Value = arr

this is the  code 

Sub report()
  Dim a As Variant, arr As Variant, cTexs As Variant, cCols As Variant
  Dim i As Long, j As Long, k As Long

  a = Sheets("sheet1").Range("A1", Sheets("sheet1").UsedRange.SpecialCells(xlCellTypeLastCell)).Value2
  cTexs = Array("CODE", "BRAND", "TYPE", "ORIGIN", "QUANTITY")
  ReDim arr(1 To UBound(a, 1), 1 To UBound(cTexs) + 1)
  ReDim cCols(0 To UBound(cTexs))

  For j = 0 To UBound(cTexs)
    cCols(j) = Application.Match(cTexs(j), Application.Index(a, 1), 0)

  For i = 1 To UBound(a, 2)
    If a(i, cCols(0)) = "Q8" Then
      k = k + 1
      For j = 0 To UBound(cCols)
        arr(k, j + 1) = a(i, cCols(j))
    End If
  Sheets("Sheet2").Range("A2").Resize(k, UBound(arr, 2)).Value = arr
End Sub


The code appears to allow that k=0 which would trigger an exception.
Variatus (rep: 4864) Sep 1, '20 at 10:18 am
is it problem this k=0     this   strat  from first row   when  begins copy data  but  my error about sheet2 !
leopard (rep: 64) Sep 1, '20 at 1:49 pm
Your code defines a range and reads its value. The error is from defining the range. The sheet doesn't matter. Resize(0, 5) doesn't define a range because no range can have 0 rows. Fix the k problem and that line of code will work just fine.
Variatus (rep: 4864) Sep 1, '20 at 8:36 pm
actually i don't understand where i start fixing  the  line  code  sorry  i said  that   i try to understand  this  code  but    i no  know   where i start  fix 
leopard (rep: 64) Sep 2, '20 at 4:54 am
Add to Discussion



The code line For i = 1 To UBound(a, 2) loops through all visible columns of the range called a. (If you wonder what a is, use descriptive names. It saves time on trouble shooting.)

In each loop the code checks If a(i, cCols(0)) = "Q8". This line probably doesn't work. An array read from a sheet has this structure: Arr( [Row], [Column] ). Therefore 1 To UBound(a, 2) counts from 1 to the number of columns in array a. But in If a(i, cCols(0)) the counter i points to the array's row vector, picking the same column cCols(0) in each row looking for "Q8". Because of this fault "Q8" is probably never found, and therefore the next few lines of code never run.

The next line (which, presumably, never runs) is k = k + 1. But if this line never runs k will remain 0. So, when the code arrives at the line Sheets("Sheet2").Range("A2").Resize(k, UBound(arr, 2)).Value = arr it crashes.

Put a break point on the line If a(i, cCols(0)) = "Q8" Then and then step through the code line by line using F8. Hover the curser over each value to see if it is what you expect. Enable the Locals pane. That's where you can also read the values of each element of arrays.


Answer the Question

You must create an account to use the forum. Create an Account or Login