vba 使用宏替换 Excel 文件中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10722451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replace data in an Excel file using macros
提问by Wassim AZIRAR
I have an Excel file wich contains some data in a 2d array.
我有一个 Excel 文件，其中包含二维数组中的一些数据。
What I want to do is to create a macro which can replace the asterisk '*' by the header of the column of the table (toto, or tata, or titi).
我想要做的是创建一个宏，它可以用表（toto、tata 或titi）列的标题替换星号“*”。
回答by Siddharth Rout
Like this?
像这样？
Option Explicit
Sub Sample()
Dim oRange As Range, aCell As Range, bCell As Range
Dim ws As Worksheet
Dim ExitLoop As Boolean
On Error GoTo Whoa
'~~> Change this to the relevant sheet name
Set ws = Worksheets("Sheet1")
Set oRange = ws.Cells
Set aCell = oRange.Find(What:="~*", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell
'~~> Assuming that the headers are in row 2
aCell.Value = Cells(2, aCell.Column)
Do While ExitLoop = False
Set aCell = oRange.FindNext(After:=aCell)
If Not aCell Is Nothing Then
If aCell.Address = bCell.Address Then Exit Do
'~~> Assuming that the headers are in row 2
aCell.Value = Cells(2, aCell.Column)
Else
ExitLoop = True
End If
Loop
End If
Exit Sub
Whoa:
MsgBox Err.Description
End Sub
回答by andy holaday
Using just worksheet tools (no VBA):
仅使用工作表工具（无 VBA）：
Ctrl-F- Find what = ~*
Find AllCtrl-Ato select all the Find resultsClosethe Find dialog- Assuming your headers in row two, and assuming the cursor lands in column C somewhere (mine did twice, YMMV), type
formula
=C$2 - Press
Ctrl-Enter
Ctrl-F- 找出什么 = ~*
Find AllCtrl-A选择所有查找结果Close查找对话框- 假设您的标题位于第二行，并假设光标位于 C 列某处（我的做了两次，YMMV），输入公式
=C$2 - 按
Ctrl-Enter
回答by Bryan
Here is a simple way I came up with.
这是我想出的一个简单方法。
i = 3
While Cells(2, i).Value <> ""
Range(Cells(3, i), Cells(6, i)).Select
Selection.Replace What:="~*", Replacement:=Cells(2, i).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
i = i + 1
Wend
Cells(x,y): x refers to row, y refers to column.
Cells(x,y)：x 代表行，y 代表列。
A more refined range select can be used instead of this basic one to have the code choose the appropriate range.
可以使用更精细的范围选择来代替这个基本的范围选择，以使代码选择适当的范围。
To implement in excel simply open up the code window and paste this code in the desired macro/subroutine.
要在 excel 中实现，只需打开代码窗口并将此代码粘贴到所需的宏/子程序中。
