需求: 为了统计Excel函数的数量,找到了一个包含完整Excel函数列表的网页。复制网页内容后,发现格式比较混乱。如果要手动整理的话,要花不少时间。这时想到了VBA。
待整理的格式:
代码:
Sub LinkJudge()
On Error Resume Next
Dim myRange As Range
Dim myWord As String
Dim i As Integer
Worksheets("ExcelFunctions").Select
For Each myRange In ActiveSheet.UsedRange
If myRange.Hyperlinks.Count > 0 Then
'myWord = myWord & Chr(10) & Chr(13) & myRange.Address & vbTab & myRange.Hyperlinks(1).Address
myRange.Copy Worksheets("ExcelFL").Cells(i, "A")
myRange.Offset(0, 1).Copy Worksheets("ExcelFL").Cells(i, "B")
i = i + 1
End If
Next
MsgBox ("Congrats," & i & " cells extracted!")
End Sub
输出结果:
参考文章:
https://zhidao.baidu.com/question/371965001879066604.html