locked
Please help me with the below Question RRS feed

  • Question

  • Hi, I am the first time user of this service...... I am in a process of written the below code to build a universal Auto-filter macro which I would be able to use with any data...... However, I am getting RUN TIME ERROR 1004 (Application-defined or Object-defined error) while executing the array coding when I am using only one array I am not getting this error msg but when using more than one array I am getting error msg with execution of array..... Since I am a new user please advise will I be informed through email once my question is answered.....

    Sub Autofilter2()
    'Apply an Autofilter
    Dim i, A, B, C, D, E, F As Integer

    ReDim K(15) As String
    ReDim L(15) As String
    ReDim M(15) As String


    ActiveWindow.ActivateNext


    D = InputBox("Please enter the Column number where 1st Filter is required")
    E = InputBox("Please enter the Column number where 2nd Filter is required")
    F = InputBox("Please enter the Column number where 3rd Filter is required")
    A = InputBox("Please enter the Column number where 1st Filter criteria is placed")
    B = InputBox("Please enter the Column number where 2nd Filter criteria is placed")
    C = InputBox("Please enter the Column number where 3rd Filter criteria is placed")

    For i = 1 To 15 Step 1

    K(i) = Cells(i, A) '' Getting error while executing this code"
    L(i) = Cells(i, B) '' Getting error while executing this code"
    M(i) = Cells(i, C) '' Getting error while executing this code"

    Next i


    Sunday, November 17, 2013 7:38 AM

All replies

  • In VBA, unlike most other programming languages, you have to specify the data type for each variable separately. The line

    Dim i, A, B, C, D, E, F As Integer

    causes only F to be an Integer, the others will be Variants. And since the InputBox function returns a string, the value of A, B, C, D and E will be a string: "4" instead of 4. This causes the error: Cells(3, "4") is not valid.

    Change the above line to

    Dim i As Long, A As Long, B As Long, C As Long, D As Long, E As Long, F As Long

    (Long is more efficient than Integer even if you use only small numbers)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2013 11:08 AM
  • Thank you sir........ Your suggestion made my macro work.... Thanks a lot....... I have learnt writing macros by recording only.... can you you please guide me how can I build my basic knowledge of VBA......
    • Edited by Excel Lover Sunday, November 17, 2013 12:32 PM
    Sunday, November 17, 2013 12:29 PM
  • You'll find an introduction in Getting Started with VBA in Excel 2010and a series of online tutorials at Excel VBA Easy.

    If you prefer a book, see Books by John Walkenbach.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2013 2:17 PM
  • Hi,

    I have a data in 12 columns and 476362 rows on which I am applying a filter. The filter is applied only on one column now after applying the filter the data I am getting is 37447 rows long..... When I tried to copy that data from the current sheet to paste it over the other one.... I am getting an error msg stating

    Microsoft office excel cannot create or use the data range reference  because it is too complex. Try one or more of the following:

    . Use data that can be selected in one contiguous rectangle.

    . Use data from the same sheet.

    Please advise how can I get this resolved since non the above options works for me.... If i had the contiguous rectangle data I would not have applied the filter. And I am using the data from one sheet only.....

    Tuesday, December 3, 2013 10:54 AM
  • Try sorting the data on the relevant column before filtering on it. That way, the filtered range will be contiguous.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 3, 2013 11:30 AM