Excel：查找并替换第一行和excel表-Excel:Findandreplacefirstrowofandexcelsheet

I want a code to find and replace all cells in a first row of an excel sheet. I have this following code by searching google.

我想要一个代码来查找和替换excel表的第一行中的所有单元格。我通过搜索谷歌获得以下代码。

Sub FindReplace()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList = Array("Fname", "Lname", "Phone")
rplcList = Array("First Name", "Last Name", "Mobile")

For x = LBound(fndList) To UBound(fndList)
    For Each sht In ActiveWorkbook.Worksheets
        Rows(1).Replace What:=fndList(x), Replacement:=rplcList(x), _
               LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
               SearchFormat:=False, ReplaceFormat:=False
    Next sht
Next x

End Sub

This works fine. but we should mention the find and replace list in the code itself. How to make it to take input from the user end instead of manually giving it in the code. Input as text or file would be good.

这很好用。但是我们应该在代码本身中提到查找和替换列表。如何使其从用户端获取输入而不是在代码中手动提供输入。输入文本或文件会很好。

1 个解决方案

fndList = Split(Application.InputBox("List the values to be searched for in the following format: " & vbCrLf & "val1, val2, val3, ...", Type:=2), ",") '<--| this returns an array of 'String's
rplcList = Split(Application.InputBox("List the values to be replaced in the following format: " & vbCrLf & "val1, val2, val3, ...", Type:=2), ",") '<--| this returns an array of 'String's

For Each sht In ActiveWorkbook.Worksheets
    For x = LBound(fndList) To UBound(fndList)
        sht.Rows(1).Replace What:=fndList(x), Replacement:=rplcList(x), _
                        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                        SearchFormat:=False, ReplaceFormat:=False
    Next x
Next sht