Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

fixing error code create report based on criteria

0

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)
  Next

  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))
      Next
    End If
  Next
  Sheets("Sheet2").Range("A2").Resize(k, UBound(arr, 2)).Value = arr
End Sub
excel
MS Office Version: 2010
posted by: leopard (rep: 88) on Tue Sep 1, 2020 at 4:30 am
Answer
Discuss

Discussion

The code appears to allow that k=0 which would trigger an exception.
Variatus (rep: 4889) 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: 88) 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: 4889) 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: 88) Sep 2, '20 at 4:54 am
Add to Discussion

Answers

0

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.

posted by: Variatus (rep: 4889) on Wed Sep 2, 2020 at 9:17 pm
Discuss


Answer the Question

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