|
楼主 |
发表于 2020-1-2 20:21
|
显示全部楼层
试了很多代码都不行,只要一提取时间就变乱码,但以前用的相同工作薄下的提取代码就不会出现这种时间变乱码的情况,以下是我以前在相同工作薄提取数据的代码,麻烦再看看:
Sub CommandButton1_Click1()
Application.ScreenUpdating = False
Dim ran As Range
For Each ran In ActiveSheet.UsedRange
If [b3] = "" Then ran.Value = "请输入查找内容"
Next
Application.ScreenUpdating = True
Dim arr, d, i&, j%
Set d = CreateObject("scripting.dictionary")
Sheet17.Activate
[a6:as2000].Clear
tj = [b3]
arr = Sheet3.UsedRange
For i = 1 To UBound(arr)
For j = 1 To UBound(arr, 2)
If arr(i, j) Like "*" & tj & "*" Then
If Not d.Exists(i) Then d(i) = "": Exit For
End If
Next
Next
If d.Count > 0 Then
For Each a In d.keys
n = n + 1
Sheet3.Cells(a, 1).Resize(1, UBound(arr, 2)).Copy Cells(n + 5, 1)
Next
End If
End Sub
----------------------------------
下面这个是您写给我的:
Sub dsmch()
Set d = CreateObject("scripting.dictionary")
Application.ScreenUpdating = False
Sheet2.Activate
[a6:an30000].Clear
tj = [b2]
With Workbooks.Open("F:\资料\资料.xlsx")
arr = .Sheets(1).UsedRange
.Close False
End With
r = 0
For i = 1 To UBound(arr)
For j = 1 To UBound(arr, 2)
If arr(i, j) Like "*" & tj & "*" Then
If Not d.Exists(i) Then
d(i) = ""
r = r + 1
For X = 1 To UBound(arr, 2)
arr(r, X) = arr(i, X)
Next X
End If
End If
Next
Next
If d.Count > 0 Then
Sheet2.Cells(3, 1).Resize(r, UBound(arr, 2)) = arr
End If
Application.ScreenUpdating = True
End Sub |
|