|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub qs() '2024/6/29
Dim arr, brr
With Sheet2
arr = .Range("a3:d" & .Cells(Rows.Count, "d").End(xlUp).Row)
For i = 1 To UBound(arr)
arr(i, 3) = TQ(arr(i, 3), "NO:")
Next
End With
With Sheet1
brr = .Range("a1").CurrentRegion.Value
For i = 1 To UBound(arr)
For j = 2 To .Cells(Rows.Count, "e").End(xlUp).Row
If CStr(arr(i, 3)) = CStr(.Range("e" & j).Value) Then
.Range("a" & j & ":w" & j).Interior.Color = RGB(255, 255, 0)
.Range("v" & j).Value = arr(i, 2): .Range("w" & j).Value = arr(i, 1)
End If
Next
Next
End With
End Sub
Function TQ(text, pattern As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
regex.pattern = pattern & "\s*(\d+)"
If regex.Test(text) Then
TQ = regex.Execute(text)(0).SubMatches(0)
Else
TQ = "No match found"
End If
Set regex = Nothing
End Function
|
|