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

 

[RoryA]

It would do that if you had nothing else on that line (since it doesn't make sense on its own) but not if you then follow that with something, or assign that to a worksheet variable.

 

[rharn]

Oh, that makes sense, it still bothers me its not recognizing my worksheet name though. however, would anyone have a good idea on how i can write this seemingly simple code and have it function? I have stored cell locations in the array Results1() and I want to extract the row number from that array and copy columns D:P of the specific row and paste it onto a new sheet. I've had to have tried at least 6 methods by now and i keep getting errors! Thanks in advance!

 

[RoryA]

We've already mentioned the only causes of the subscript error. Until you can work out the issue, we can't work around it.
Can you enter a link from one sheet to a cell on your searchresults sheet and then post the exact formula here (copied from the formula bar), just so we can check it? It should be just:
=searchresults!A1
or something similar. If you get quotes round the sheet name, there must be a space somewhere.

 

[rharn]

Haha...ok well this is embarrassing but I worked through the problem, I've been pluralizing 'searchresult'. Now I am getting a object required error on the next row. At least its something new...Thanks for everyone's help so far!

 

[rharn]

Does anyone know what "Method 'Range' of object '_Global' failed" means? the debugger highlights the line 'set p1results = Range(Results(i1))'

 

[Norie]

You don't appear to have mentioned 'p1results' before, what is it exactly?

Can you post the current code?

 

[rharn]

p1results is the value of result(i1) set as a range? I am trying to extract values from the results1() array which are cell locations, and use it to select the entire row of data from that cell address. The following code is:

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

 

[RoryA]

Is the range in question on the active worksheet?

 

[rharn]

i do not believe so, the cell locations from Results1() refers to a set of data entries on another worksheet, so by extension the p1results should refer to the same cells with the worksheet that contains the data set. And I want to copy and paste that into a "searchresult" worksheet

 

[Norie]

Can you post the rest of the code?