Need help on If statement to update my code

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,054
Office Version
  1. 2016
Platform
  1. Windows
Hi

I have a code that works in taking href from a site, I now want to incorporate an If statement into the code, so if the data is there, then extract it and if not the place the word "nil" into the cell. Something like what I have below, but I can't get it to work. I have been stuck on this for a few days now and I can not seem to work it out.

VBA Code:
For Each link In htmlDoc.getElementsByTagName("a")
            If link.getAttribute("class") = "vip" Then
                  Cells(i, 1).Value = link.getAttribute("href")
                i = i + 1
            End If
        
            If link.getAttribute("class") = "lvtitle" & innerText = "" Then
                   Cells(i, 2).Value = "nil"
                    i = i + 1
                Else
                If link.getAttribute("class") = "lvtitle" Then
                   Cells(i, 2).Value = link.getAttribute("innertext")
                 i = i + 1
            End If
            
          If link.getAttribute("class") = "hotness-signal red" & innerText = "" Then
                  Cells(i, 3).Value = "nil"
                     i = i + 1
                Else
                   If link.getAttribute("class") = "hotness-signal red" Then
                     Cells(i, 3).Value = link.getAttribute("innertext")
                 i = i + 1
            End If
            
            If link.getAttribute("class") = "prRange" & innerText = "" Then
                    Cells(i, 4).Value = "nil"
                       i = i + 1
                Else
                    If link.getAttribute("prRange") = "lvtitle" Then
                      Cells(i, 4).Value = link.getAttribute("innertext")
                   i = i + 1
            End If    
        Next link

The Full working code for the href is here
VBA Code:
Private Sub CommandButton3_Click()
    Dim ie As Object
    Dim htmlDoc As Object
    Dim nextPageElement As Object
    Dim div As Object
    Dim link As Object
    Dim URL As String
    Dim pageNumber As Long
    Dim i As Long
    
    ' Takes Url from Sheet2 A1 seach from Keyword from B1 and places IE
     URL = Sheets("Sheet1").Range("A2").Value & Replace(Worksheets("Sheet1").Range("B2").Value, " ", "+")

    Set ie = CreateObject("InternetExplorer.Application")
    
    With ie
        .Visible = True
        .navigate URL
        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop
    End With

    Application.Wait Now + TimeSerial(0, 0, 5)
    Set htmlDoc = ie.document
    pageNumber = 1
    i = 4
    Do
    
    For Each link In htmlDoc.getElementsByTagName("a")
            If link.getAttribute("class") = "vip" Then
                Cells(i, 1).Value = link.getAttribute("href")
                i = i + 1
            End If
        
            
        Next link
       ' Clicks and goes to next page
        If pageNumber >= 1 Then Exit Do 
        Set nextPageElement = htmlDoc.getElementsByClassName("gspr next")(0)
        If nextPageElement Is Nothing Then Exit Do
        
        nextPageElement.Click 'next web page
        Do While ie.Busy Or ie.readyState <> 4
            DoEvents
        Loop
        Application.Wait Now + TimeSerial(0, 0, 5)
        Set htmlDoc = ie.document
        pageNumber = pageNumber + 1
    Loop
ie.Quit
    Set ie = Nothing
    Set htmlDoc = Nothing
    Set nextPageElement = Nothing
    Set div = Nothing
    Set link = Nothing

 MsgBox "All Done"

End Sub
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
If link.getAttribute("class") = Nothing Then
     Cells(i, 1).Value ="Nil"
End If
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,054
Office Version
  1. 2016
Platform
  1. Windows
JLGWhiz

Thanks for yout reply, I keep getting this error message at "Nothing Then" It stops at the "Nothing", Not sure if I have implemented the code correctly. I have but the full code below. Could you please advise.

Thanks for having a look
1583579391294.png


Updated Code
VBA Code:
    Dim ie As Object
    Dim htmlDoc As Object
    Dim nextPageElement As Object
    Dim div As Object
    Dim link As Object
    Dim URL As String
    Dim pageNumber As Long
    Dim i As Long
    
    ' Takes Url from Sheet2 A1 seach from Keyword from B1 and places IE
     URL = Sheets("Sheet1").Range("A2").Value & Replace(Worksheets("Sheet1").Range("B2").Value, " ", "+")

    Set ie = CreateObject("InternetExplorer.Application")
    
    With ie
        .Visible = True
        .navigate URL
        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop
    End With

    Application.Wait Now + TimeSerial(0, 0, 5)
    Set htmlDoc = ie.document
    pageNumber = 1
    i = 4
    Do
    
    For Each link In htmlDoc.getElementsByTagName("a")
            If link.getAttribute("class") = Nothing Then
                Cells(i, 1).Value = "Nil"
                 Else
            If link.getAttribute("class") = "vip" Then
                Cells(i, 1).Value = link.getAttribute("href")
                i = i + 1
            End If

            If link.getAttribute("class") = Nothing Then
                Cells(i, 2).Value = "Nil"
            End If
                Else
                If link.getAttribute("class") = "lvtitle" Then
                   Cells(i, 2).Value = link.getAttribute("innertext")
                i = i + 1
                End If
            
            If link.getAttribute("class") = Nothing Then
                Cells(i, 3).Value = "Nil"
            End If
                Else
                   If link.getAttribute("class") = "hotness-signal red" Then
                     Cells(i, 3).Value = link.getAttribute("innertext")
                 i = i + 1
                End If
            
           If link.getAttribute("class") = Nothing Then
                Cells(i, 4).Value = "Nil"
                End If
                Else
                    If link.getAttribute("prRange") = "lvtitle" Then
                      Cells(i, 4).Value = link.getAttribute("innertext")
                   i = i + 1
            End If
     
        Next link
       ' Clicks and goes to next page
        If pageNumber >= 1 Then Exit Do
        Set nextPageElement = htmlDoc.getElementsByClassName("gspr next")(0)
        If nextPageElement Is Nothing Then Exit Do
        
        nextPageElement.Click 'next web page
        Do While ie.Busy Or ie.readyState <> 4
            DoEvents
        Loop
        Application.Wait Now + TimeSerial(0, 0, 5)
        Set htmlDoc = ie.document
        pageNumber = pageNumber + 1
    Loop
ie.Quit
    Set ie = Nothing
    Set htmlDoc = Nothing
    Set nextPageElement = Nothing
    Set div = Nothing
    Set link = Nothing

 MsgBox "All Done"
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,054
Office Version
  1. 2016
Platform
  1. Windows
I have changed
VBA Code:
= Nothing Then
to
VBA Code:
 Is Nothing Then
This has got rid of the Invalid Use Of Object message, but now I am getting this message Object Required ERROR Message
VBA Code:
 If link.getAttribute("class") Is Nothing Then
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

My error, replace the equal symbol with 'Is'
VBA Code:
If link.getAttribute("class") Is Nothing Then
     Cells(i, 1).Value ="Nil"
End If
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,054
Office Version
  1. 2016
Platform
  1. Windows
Thanks, I worked that out and made the changes already, but now get another error message, See my above post. I have reposted the code
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I have to admit that web interface is not my expertise, so I have no other suggestions. Perhaps someone more adept with that type of coding will jumjp in and gve some advice.
Regards, JLG
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,054
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your help so far, for some reason my post was moved to this forum , when I posted it in General forum, where I normal post question. I'm just glad that you gave it sometime and had a look, as this forum tends to be more quiter.

However your method has given me some food for thought. Hopefully someone can help
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You are in the correct forum, but not all of us who respond to issues in this forum are professional grade programmers. I happen to be one of those who is not, although on about 80% - 90% of those I respond to I have been able to help along. So I suppose I am well above novice level. It is just that I am no longe actively working so I do not get the exposure to different issues that I once did and while I might be able to solve all the issues by reseaching and testing, I just do not want to put that much effort into it as a hobby and on a volunteer basis. There should be someone who can help you get the correct syntax for the statement you want to use. Just keep bumping the thread up. I will be dropping off of it now.
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,129,529
Messages
5,636,853
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top