invalid qualifier error in VBA

[rharn]

Hello,

I am currently writing a section of a code that will display the results of a search function from an array. However, I keep encountering an 'invalid qualifier' error. Can someone please let me know why it keeps doing this?

Dim Results1() as Variant
Dim i1 as Integer
Dim NextRow as Integer

        For i1 = LBound(Results1) To UBound(Results1)
            Results1(i1).EntireRow.Copy
            Worksheets("searchresults").Select
            NextRow = Range("D65536").End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
        Next il1

 

[Sektor]

Results1 is ARRAY, not Range! It doesn't have "EntireRow" property!

...
Results1(i1).EntireRow.Copy
...

If your array contains ranges, then you'd do so:

Dim Results1() as Variant
[B][COLOR="Red"]Dim rng As Range[/COLOR][/B]
Dim i1 as Integer
Dim NextRow as Integer

        For i1 = LBound(Results1) To UBound(Results1)
            [B][COLOR="red"]Set rng = Results1(i1)
            rng.EntireRow.Copy[/COLOR][/B]
            Worksheets("searchresults").Select
            NextRow = Range("D65536").End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
        Next il1

 

[rharn]

Hi Sektor,

thanks for the quick reply. The values in my array refer to a cell location within a sheet and my goal is to take the value from that array and select the entire row so i can copy/paste onto another sheet. This is what your code is also suggesting correct?

Also, on a side note. I have looked all over the forums but have not been able to find an answer to this seemingly simple question. My data entries range from columsn D:M only and I originally wanted to just take the cell address from my array and select the pertinent columns to copy/paste. Could I just do something like:

columns("D:M").range.copy?

 

[Sektor]

Yes, my code takes range from array. As you requested, only D:M columns are taken.

[COLOR="Blue"]Dim[/COLOR] Results1() [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] rng [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Dim[/COLOR] i1 [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] NextRow [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]

    [COLOR="Blue"]For[/COLOR] i1 = [COLOR="Blue"]LBound[/COLOR](Results1) [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](Results1)
        [COLOR="Blue"]Set[/COLOR] rng = Results1(i1)
        Range("D" & rng.Row & ":M" & rng.Row).Copy
        Worksheets("searchresults")
            NextRow = .Range("D65536").End(xlUp).Row + 1
            .Cells(NextRow, 1).Paste
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

Advice: Try to avoid selecting.

 

[rharn]

Hey Spektor,

Thank you so much for your help so far, but when I try to adopt your code I am getting an 'object required' error in the line.

Set rng = Results1(i1)

I have no idea how it could be wrong.

Thanks in advance.

 

[Sektor]

How do you get Results1 array? What it's filled with?

 

[rharn]

Results1 is filled with the address of the cells it finds based off of a search function. The search function is a user specified search range for a specific property the following code shows essentially how Results1 is filled:

Dim Textbox1 As Long
Dim Find1 as Range
Dim FindRange1 As Range
Dim Results1() As Variant
Dim i1 as Integer

Textbox1 = ILsearch.TextBox1.Value

                   For R1 = 1 To Max
                        For Each Find1 In FindRange1
                            If (Find1.Value < TextBox1) And (Find1.Value > 0) Then
                                i1 = i1 + 1
                                ReDim Preserve Results1(i1)
                                Results1(i1) = Find1.Address
                            End If
                        Next Find1
                    Next R1

 

[Sektor]

Dim Results1() as Variant
Dim rng As Range
Dim i1 as Integer
Dim NextRow as Integer

        For i1 = LBound(Results1) To UBound(Results1)
            Set rng = [B][COLOR="Red"]Range(Results1(i1))[/COLOR][/B]
            rng.EntireRow.Copy
            Worksheets("searchresults").Select
            NextRow = Range("D65536").End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
        Next il1

 

[rharn]

Spektor, thanks for the help again, but now I am receiving an 'overflow' error when it gets to the 'ActiveSheet.Paste' line. Any explanations why? Thanks in advance.

 

[Sektor]

Dim Results1() as Variant
Dim rng As Range
Dim i1 as Integer
Dim NextRow as Integer

        For i1 = LBound(Results1) To UBound(Results1)
            Set rng = Range(Results1(i1))
            rng.EntireRow.Copy
            With Worksheets("searchresults")
                NextRow = [B][COLOR="Red"].[/COLOR][/B]Range("D65536").End(xlUp).Row + 1
                [B][COLOR="red"].[/COLOR][/B]Cells(NextRow, 1).Paste
            End With
        Next il1