Wanna have my code curtailed so that it will take on a professional look



  • Hi Ranjith! Hope you are doing well. I have made a working code that is running fine. It is fetching web data from three different sites based on the names compiled in my excel sheet. I have made repetition of my code in a single sub to make it work. I am damn sure if you take a look at this code you can give me a solution how i can make it look like a professional one and concise. I pasted here the code for two sites for the lack of space. Please, go it through when you have time and lead me how can i make it small.Names for search:

    1. salon 718
    2. Lee Lees Forest

    Sorry, to have bothered and thanks in advance.

    Sub DoNa()
    
    Dim http As New MSXML2.XMLHTTP60, html As New HTMLDocument
    Dim sth As String, mth As String, z As String
    Dim cg As Object, tar As Object, mj As Object
    Dim x As Long, lrow As Long, tac As Object
    
    lrow = Range("A" & Rows.Count).End(xlUp).Row
    
    For x = 2 To lrow
    z = Cells(x, 1)
    mth = Replace(z, " ", "-")
    http.Open "GET", "https://www.yelp.com/biz/" & mth & "-new-york", False
    http.send
    html.body.innerHTML = http.responseText
    
    Set cg = html.getElementsByClassName("street-address")
    Set mj = html.getElementsByClassName("biz-phone")
        For Each tar In cg
            Cells(x, 2).Value = tar.innerText
            For Each tac In mj
            Cells(x, 3).Value = tac.innerText
            Next tac
        Next tar
    
    
    http.Open "GET", "https://www.yelp.com/biz/" & mth & "-brooklyn", False
    http.send
    html.body.innerHTML = http.responseText
    
    
    Set cg = html.getElementsByClassName("street-address")
    Set mj = html.getElementsByClassName("biz-phone")
        For Each tar In cg
            Cells(x, 2).Value = tar.innerText
            For Each tac In mj
            Cells(x, 3).Value = tac.innerText
            Next tac
        Next tar
    Next x
    End Sub

  • administrators

    Just keep writing code for different scenarios, you'll be able to optimize the code on your own soon. Good Luck :)


Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.