|
发表于 2022-1-5 20:56
|
显示全部楼层
本楼为最佳答案
贴上代码,与附件中代码相比,只是把没有用到的变量给删了
- Sub 按文件名()
- Dim r&, Arr, Brr, fn$, n%, x%
-
- fn = Sheet1.Range("F1")
- If Len(fn) = 0 Then Exit Sub
- With Sheet2
- r = .Cells(Rows.Count, 1).End(xlUp).Row
- Arr = .Range("a2:c" & r)
- End With
- ReDim Brr(1 To 3, 1 To 1)
- x = 0
- For i = 1 To UBound(Arr)
- If InStr(Arr(i, 1), fn) > 0 Then
- x = x + 1
- n = n + 1 ' 符合条件时的记录数(记数器)
- ReDim Preserve Brr(1 To 3, 1 To x)
- For j = 1 To 3
- Brr(j, x) = Arr(i, j)
- Next
- End If
- Next
- With Sheet1
- .Range("a2:c" & .Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
- .Range("a2").Resize(UBound(Brr, 2), UBound(Brr, 1)) = Application.WorksheetFunction.Transpose(Brr)
- .Range("F3") = n
- End With
- End Sub
复制代码
|
|