我是宏的新手，我正在嘗試查找和替換列名。我有有效的代碼，但我有疑問。我對 For X = 回圈感到困惑。我希望宏只在第一行回圈，但我的代碼就像回圈我不想要的整個作業表 [![Excel 檔案][1]][1]
我需要修復應該只讀取/回圈第一行而不是整個作業表的 for 回圈。
以下是我的代碼
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
Dim data As Variant
Set sht = ActiveWorkbook.Worksheets("ERT")
fndList = Array("colon.", _
"Selec:", _
"Sele, _
"Submi")
rplcList = Array("A", "A1", "S1", "D1")
With sht
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
'For Each sht In ActiveWorkbook.Worksheets("Exp")
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
' Next sht
Next x
End With
請幫我修復這個“For x = LBound(fndList) To UBound(fndList)”，我只想為第一行 A1 編碼：直到第一行結束。For 回圈應該只回圈并查找和替換從第一行 A1 到第一行結束的陣列值
uj5u.com熱心網友回復：
請嘗試下一個方法。它將嘗試匹配第一行標題上的第一個陣列元素，并將找到的元素替換為第二行中的相應字串：
Sub ReplaceHeaders()
Dim shT As Worksheet, lastCol As Long, fndList, rplcList, rngHd As Range, mtch, i As Long
Set shT = ActiveWorkbook.Worksheets("Final Exclusion")
lastCol = shT.cells(1, shT.Columns.count).End(xlToLeft).Column
fndList = Array("Enter one or more MSOs or ESOs to be excluded. Separate multiple values using a semicolon.", _
"Select the datasets from which you wish to exclude these MSOs:", _
"Select the reason code for the exclusion:", _
"Submitter's CWSID")
rplcList = Array("MSO", "SOCS", "Reason's", "Submitter")
Set rngHd = shT.Range(shT.cells(1, 1), shT.cells(1, lastCol))
For i = 0 To UBound(fndList)
mtch = Application.match(fndList(i), rngHd, 0)
If Not IsError(mtch) Then
rngHd(1, mtch).Value = rplcList(i)
End If
Next i
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/419970.html
標籤：
下一篇：如何計算句子中的確切單詞？