比較簡單的問題。我有一個巨大的電子表格，里面裝滿了產品代碼。我需要更改其中的大約 200 個。我有下面的代碼。在實時系統上使用它之前，我只是在測驗它。當我運行宏時，它只是洗掉另一張表上單元格中的所有資料，而不是用第二列中的新產品代碼替換它。非常感謝所有幫助。
Sub Multi_FindReplace()
Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Set tbl = Worksheets("Sheet4").ListObjects("Table1")
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)
fndList = 1
rplcList = 2
For x = LBound(myArray, 1) To UBound(myArray, 2)
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> tbl.Parent.Name Then
sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End If
Next sht
Next x
End Sub
uj5u.com熱心網友回復：
嘗試這個：
Sub Multi_FindReplace()
Dim sht As Worksheet, wb As Workbook, x As Long
Dim fnd As String, rplc As String, tbl As ListObject, data As Variant
Set wb = ActiveWorkbook
Set tbl = wb.Worksheets("Sheet4").ListObjects("Table1")
data = tbl.DataBodyRange.Value 'no need to transpose
For x = LBound(data, 1) To UBound(data, 1) 'loop over data rows
fnd = data(x, 1) 'find value
rplc = data(x, 2) 'replace value
If Len(fnd) > 0 And Len(rplc) > 0 Then 'make sure there are a pair of values
For Each sht In wb.Worksheets
If sht.Name <> tbl.Parent.Name Then
sht.Cells.Replace What:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End If
Next sht
End If 'have a pair of values
Next x
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/524854.html
標籤：擅长vba
上一篇：CentOS6/7 配置守護行程