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

 

[rharn]

Sektor, thank you so much for the help you've provided so far but now I am getting a 'subscript out of range' error in the line 'With Worksheets("SearchResults")'. This section of the code just does not seem to be cooperating with me!

 

[Sektor]

Make sure this sheet exists. May be there's space in the name. Watch thoroughly.

 

[diddi]

silly question... have you checked that you have a worksheet called 'SearchResults'

spelling error (or a space etc) will cause this error

 

[rharn]

Funny that you guys should say that because that was the first thing I checked too. My sheet is named "SearchResult" and usually vba automatically capitalizes the names but when I type in "searchresult" it does not auto-cap the "s" and "r". In fact it doesn't do it for any of my worksheets in the workbook at all. I am also positive I am spelling it the same way I name the worksheets too. Maybe if you guys can explain why my code isn't doing this it will correct the bug as well? Thanks!

 

[RoryA]

The VBEditor will autocorrect variable names, not pieces of text, which is what you have there.
Incidentally the cause of your initial error was that your loop counter was i1 and your Next statement was Next il1
You should also declare row variables as Long, not as Integer.

 

[Sektor]

...and usually vba automatically capitalizes the names...

When it has good mood?

 

[rharn]

haha Sketor, that seems to be what VBA is doing right now. And rorya, I actually corrected the counter il1 to i1 awhile ago, but the typo must have carried through in the posts. However I am still receiving the 'subscript out of range' error. In addition, I have also changed my row counters to Long variables too. Maybe VBEditor is just plain evil...

 

[RoryA]

If the subscript error occurs on that line, then either the sheet name is wrong (perhaps there's a leading or trailing space, or space in the middle?) or the wrong workbook is active (assuming the code is in a normal module, not ThisWorkbook).
Note: we are assuming that "searchresults" is the actual caption written on the sheet tab. If searchresults is a variable containing the sheet name, then remove the quotes.

 

[Sektor]

Listen, you paste ENTIRE row into cell in D column. Since copied entire row has 256 columns (I assume you have Excel 2003), then you try to insert 256 columns into 253 columns (256 minus columns A,B and C). This row won't fit, so you get error.

 

[rharn]

That would make sense, but my previous code which had a similar structure did not have a problem with it. Perhaps I should just start over from scratch in this section of the code...VBEditor is being funny, when I type 'Worksheets("SearchResults")' it auto-formats and buts a space in between Worksheets and ("SearchResults") which it did not do previously...