Compiler error Invalid qualifier

[Sharid]

The code below works fine if seperated but together I get the folllowing error message

"Compiler error Invalid qualifier"

The code takes a list of url from sheet2 "URL LIST" and extracts the data from IE, pasting it into sheet1, it then deletes any blank rows. I did have a delete duplicate, but for some reason it was deleting data that were not duplicates (shown in orange below.)

The second bit of code I added is shown in blue at the bottom of the code, on its own it works fine, as does the top code. This blue code checks rows in column A and if they don't have a SPECIAL Character, in this case "www" it deletes them (Shown in green).

I get the "Compiler error Invalid qualifier" message in the bit show in red below, can someone help

Private Sub CommandButton4_Click()
Dim i, j, k, l As Integer
    i = 2
    k = 2
    l = 2
'SHEET2 as sheet with URL
    Dim wsSheet As Worksheet, Rows As Long, links As Variant, ie As Object, link As Variant
    Set wb = ThisWorkbook
    Set wsSheet = wb.Sheets("URL LIST")

'Set IE = InternetExplorer
    Set ie = CreateObject("InternetExplorer.Application")
    Rows = wsSheet.Cells(wsSheet.Rows.Count, "A").End(xlUp).Row
    links = wsSheet.Range("A1:A" & Rows)

'IE Open Time per page 5sec and check links on Sheet2 Column A
    With ie
        .Visible = True
       Application.Wait (Now + TimeValue("00:00:5"))
       
        For Each link In links
            .navigate (link)
            While .Busy Or .READYSTATE <> 4: DoEvents: Wend
            
        x = .document.body.innerText
        x = Replace(x, Chr(10), Chr(13))
        x = Split(x, Chr(13))
        Range("A1").Resize(UBound(x)) = Application.Transpose(x)
            
            For i = 1 To 5000
                On Error Resume Next

'Paste in sheet and column
                Dim rw As Long
                
                rw = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row + 1
                Sheets("Sheet1").Range("A" & rw).Value = ie.document.getElementsByTagName("a").Item(i).innerText

            Next i

'Deletes duplicates in column A Sheet1
           [COLOR=#ff8c00]'Columns(1).RemoveDuplicates Columns:=Array(1)[/COLOR]
'navigate links
        Next link

'Close IE Browser
    ie.Quit
    Set ie = Nothing
    
 ' Deletes any blank rows as data pased into excel as too many blank rows
        Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

' Deletes any rows in column A if it does not have a SPECIAL Character as shown in RED
[COLOR=#0000cd]Dim r As Long, lr As Long
lr = Cells([/COLOR][COLOR=#ff0000]Rows[/COLOR][COLOR=#0000cd].Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
 If InStr(Cells(r, 1), "[/COLOR][COLOR=#008000]www[/COLOR][COLOR=#0000cd]") = 0 Then Rows(r).Delete
Next r
       
    End With[/COLOR]
    
End Sub

Thanks

 

[Norie]

Re: Help on last bit of code - Compiler error Invalid qualifier - ERROR MESSAGE

Where do you get the error?

 

[Sharid]

Re: Help on last bit of code - Compiler error Invalid qualifier - ERROR MESSAGE

This bit

[COLOR=#0000cd]lr = Cells([/COLOR][COLOR=#ff0000]Rows[/COLOR][COLOR=#0000cd].Count, 1).End(xlUp).Row[/COLOR]

Blue code at the bottom of larger code, the word Rows as shown in red,

 

[Norie]

Re: Help on last bit of code - Compiler error Invalid qualifier - ERROR MESSAGE

You need a worksheet reference for Rows there.

PS Probably not a good idea to use Rows as a variable name as you have here.

    Rows = wsSheet.Cells(wsSheet.Rows.Count, "A").End(xlUp).Row

 

[RoryA]

Re: Help on last bit of code - Compiler error Invalid qualifier - ERROR MESSAGE

I don't see any qualifier in that code.

However, your End With should be much earlier as you are holding a reference to an application that you have already tried to quit.

 

[Rick Rothstein]

Re: Help on last bit of code - Compiler error Invalid qualifier - ERROR MESSAGE

This bit

[COLOR=#0000cd]lr = Cells([/COLOR][COLOR=#ff0000]Rows[/COLOR][COLOR=#0000cd].Count, 1).End(xlUp).Row[/COLOR]

Blue code at the bottom of larger code, the word Rows as shown in red,

The problem is in the second Dim statement at the top of your code...

[table="width: 500"]
[tr]
	[td]Dim wsSheet As Worksheet, [B][COLOR="#FF0000"]Rows As Long[/COLOR][/B], links As Variant, ie As Object, link As Variant[/td]
[/tr]
[/table]

In it you declared Rows to be a variable of type Long... Long variables do not have a Count property. You should avoid using VB or Excel keywords as variable names in order to avoid problems like this.

 

[Sharid]

Re: Help on last bit of code - Compiler error Invalid qualifier - ERROR MESSAGE

RoryA

I have moved the End With to

'navigate links
        Next link
End With

Any higher and i get an error message



Norie

Rows = wsSheet.Cells(wsSheet.Rows.Count, "A").End(xlUp).Row

I think that part of the code was written for me as i was struggling. I would not know how to change it, my VBA is very limited, I'm lucky I got this far

 

[Norie]

Re: Help on last bit of code - Compiler error Invalid qualifier - ERROR MESSAGE

Try this.

Option Explicit

Private Sub CommandButton4_Click()
Dim wb As Workbook
Dim x As Variant
Dim i, j, k, l As Integer
Dim r As Long, lr As Long
Dim wsSheet As Worksheet, links As Variant, ie As Object, link As Variant
Dim rw As Long

    i = 2
    k = 2
    l = 2
    'SHEET2 as sheet with URL
    Set wb = ThisWorkbook
    Set wsSheet = wb.Sheets("URL LIST")
    
    'Set IE = InternetExplorer
    Set ie = CreateObject("InternetExplorer.Application")
    
    rw = wsSheet.Cells(wsSheet.Rows.Count, "A").End(xlUp).Row
    links = wsSheet.Range("A1:A" & rw)
    
    'IE Open Time per page 5sec and check links on Sheet2 Column A
    With ie
       .Visible = True
       Application.Wait (Now + TimeValue("00:00:5"))
       
       For Each link In links
           .navigate (link)
           While .Busy Or .READYSTATE <> 4: DoEvents: Wend
           
           x = .document.body.innerText
           x = Replace(x, Chr(10), Chr(13))
           x = Split(x, Chr(13))
           Range("A1").Resize(UBound(x)) = Application.Transpose(x)
           
           For i = 1 To 5000
               On Error Resume Next
               
               'Paste in sheet and column
                        
               rw = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row + 1
               Sheets("Sheet1").Range("A" & rw).Value = ie.document.getElementsByTagName("a").Item(i).innerText
               
           Next i
           
           'Deletes duplicates in column A Sheet1
           'Columns(1).RemoveDuplicates Columns:=Array(1)
           'navigate links
       Next link
       
       'Close IE Browser
       .Quit
    End With
    
    Set ie = Nothing
    
    ' Deletes any blank rows as data pased into excel as too many blank rows
    Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    ' Deletes any rows in column A if it does not have a SPECIAL Character as shown in RED
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
    For r = lr To 2 Step -1
        If InStr(Cells(r, 1), "www") = 0 Then Rows(r).Delete
    Next r


End Sub

 

[Sharid]

Re: Help on last bit of code - Compiler error Invalid qualifier - ERROR MESSAGE

Thanks Norie Your code worked.

So did this bit that I did, but you had already posted. I will go with your code, as I don't really know what I'm doing

From

Dim r As Long, lr As Long
lr = Cells([COLOR=#0000cd]Rows[/COLOR].Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
 If InStr(Cells(r, 1), "www") = 0 Then [COLOR=#0000cd]Rows[/COLOR](r).Delete
Next r

To

Dim r As Long, lr As Long
lr = Cells([COLOR=#ff0000]Sheet1.[/COLOR][COLOR=#0000cd]Rows[/COLOR].Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
 If InStr(Cells(r, 1), "www") = 0 Then [COLOR=#ff0000]Sheet1.[/COLOR][COLOR=#0000cd]Rows[/COLOR](r).Delete
Next r

This is now sorted, thanks