VBA 从网页中查找文本

Question

我创建了宏，它为我提供了任何网页上的所有 URL。
我们只需要提供 URL，它就会为我们提供该网页中存在的所有链接并将其粘贴到一栏中

Private Sub CommandButton4_Click()

'We refer to an active copy of Internet Explorer
Dim ie As InternetExplorer
'code to refer to the HTML document returned
Dim html As HTMLDocument
Dim ElementCol As Object
Dim Link As Object
Dim erow As Long
Application.ScreenUpdating = False
'open Internet Explorer and go to website
Set ie = New InternetExplorer
ie.Visible = True
ie.navigate Cells(1, 1)

'Wait until IE is done loading page
Do While ie.READYSTATE <> READYSTATE_COMPLETE

Application.StatusBar = "Trying to go to website…"
DoEvents
Loop

Set html = ie.document
'Display text of HTML document returned in a cell
'Range("A1") = html.DocumentElement.innerHTML
Set ElementCol = html.getElementsByTagName("a")

For Each Link In ElementCol
erow = Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Value = Link
Cells(erow, 1).Columns.AutoFit
Next

'close down IE, reset status bar & turn on screenupdating

'Set ie = Nothing
Application.StatusBar = ""
Application.ScreenUpdating = True
ie.Quit
ActiveSheet.Range("$A$1:$A$2752").removeDuplicates Columns:=1, Header:=xlNo

End Sub

现在任何人都可以帮助我创建宏以从列中存在的所有这些 URL 中查找特定文本，如果该文本存在，那么在下一列中它应该打印文本“找到的文本”。

例如，如果我们搜索文本“New”，那么它应该在 URL 的下一列中打印文本“Text found”。

谢谢你。

Answer 1

关键是函数 Instr，如果它找到字符串“New”，则返回它开始的位置，否则返回 0。

i=1
do until trim(Cells(i,1).Value) = vbNullString
    if instr(Cells(i,1).Value,"New") then
        Cells(i,2).value="Text found"
    end if
    i=i+1
loop