VBA: if HTML does not contain any of these words, edit value and re-run

yits05

Board Regular
Joined
Jul 17, 2020
Messages
56
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am hitting a wall trying to figure out how to edit my VBA script, and was hoping someone can please help. I have the below VBA script which works almost perfectly. However, there are circumstances when the HTML it returns is wrong and does not contain specific values I need, and when that occurs it is because the source URL string is incorrect. The URL string is a number. I need to add an If statement to my script which:
1) checks if the returned HTML contains any of "sampletext1" OR "sampletext2" OR "sampletext3"
2) If No, then increase the URL value by 1000 and run the HTML call again with the increased URL Value. Continue to add 1000 until the text is found in the HTML.
3) If Found, then continue script.

Current VBA:
VBA Code:
Public Sub getjdtext()
        Dim Url As String
        Dim Strresponse As Variant
        Dim i As Long
        FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
        Dim Sheet As Worksheet
        Set Sheet = Sheets("Main")
    
    For i = 2 To FinalRow
        Url = Range("C" & i).Value
        
        Dim httpReq As Object
        Set httpReq = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        With httpReq
        .Open "Get", Url, False
        .send
        Strresponse = .responseText
        Debug.Print
        Worksheets("Main").Range("D" & i).Value = Strresponse
        
        
    End With

Next i

Columns("D:D").wraptext = False

End Sub

Many thanks!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,147,497
Messages
5,741,501
Members
423,662
Latest member
Ajmal Khursand

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