vba 用不同的值替换 Excel 工作表中的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13336915/
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 cells in an Excel sheets with a different value
提问by Snipekiller430
I have an excel sheet that has around 900 rows of data and I need to run through one of the columns and replace what that column has with a different value depending on what is currently in the column.
我有一个包含大约 900 行数据的 Excel 工作表，我需要遍历其中一列，并根据列中当前的内容用不同的值替换该列的内容。
For example, say column F has these values entered:
Adj/Aviation & Transportation
Adj/Aviation & Transportation
Adj/Aviation & Transportation
Adj ESL Instructor
Adj/ESL
Adj/Aviation & Transportation
Professor/Aviation
Professor-Aviation
Associate Chair, Professor
Professor
Prof/Aviation & Transportation
Adjunct Professor
Professor
Professor
Adj/Aviation
Professor
Dir Master's Prog/Assoc Prof
Chair/Assoc Prof/Aviation&TS
Adj Asst Prof/Aviation
Assistant Professor/Aviation
Adj.Asst. Prof-Aviation
例如，假设列 F 输入了以下值：
Adj/Aviation & Transportation
Adj/Aviation & Transportation
Adj/Aviation & Transportation
Adj ESL Instructor
Adj/ESL
Adj/Aviation & Transportation
Professor/Aviation
Professor-Aviation
Associate Chair,
Professor
Prof/航空和运输
兼职教授
教授
教授
调/航空
教授
迪尔硕士PROG /副教授
主席/副教授/航空及TS
调助理教授/航空
助理教授/航空
Adj.Asst。航空专业
I want to run through this column and if one of the cells contains the value "Aviation" I want to clear the content in that cell and enter "Aviation & Transportation" instead.
我想浏览此列，如果其中一个单元格包含值“航空”，我想清除该单元格中的内容并输入“航空和运输”。
If anyone can show me how to accomplish this task that would mean the world!
如果有人能告诉我如何完成这项任务，那将意味着世界！
采纳答案by ApplePie
Actually, I have a better solution. You can use wildcards in the Find..Replace menu.
其实我有更好的解决办法。您可以在 Find..Replace 菜单中使用通配符。
Hit Ctrl F > Replace > Enter *Aviation*then write whatever you want to replace the cell value to. I tested it and it works. You can enable or disable case sensitivity.
点击 Ctrl F > Replace > Enter*Aviation*然后写下你想替换单元格值的任何内容。我测试了它并且它有效。您可以启用或禁用区分大小写。
回答by TheRuss
Add a new column and populate it with a formula something like:
添加一个新列并使用如下公式填充它：
=IF(NOT(ISERROR(SEARCH("Aviation",B1))),"Aviation & Transportation",B1)
- This is untested, might need some syntax adjustment as I haven't put it in excel. But it should point you in the right direction.
- 这是未经测试的，可能需要一些语法调整，因为我没有把它放在 excel 中。但它应该为您指明正确的方向。
回答by Davesexcel
Try this
尝试这个
Sub replace()
Dim Rws As Long, Rng As Range, c As Range
Rws = Cells(Rows.Count, "F").End(xlUp).Row
Set Rng = Range(Cells(1, 6), Cells(Rws, 6))
For Each c In Rng.Cells
If c Like "*Aviation*" Then c = "Aviation & Transportation"
Next c
End Sub
You can also use the Autofilter in your VBA code.
您还可以在 VBA 代码中使用自动过滤器。
Start
result
开始
结果
Use the autofilter code.
使用自动过滤器代码。
Sub Button1_Click()
Dim Rws As Long, Rng As Range, c As Range
Rws = Cells(Rows.Count, "F").End(xlUp).Row
Columns("F:F").AutoFilter Field:=1, Criteria1:="=*Aviation*"
Set Rng = Range(Cells(2, 6), Cells(Rws, 6)).SpecialCells(xlCellTypeVisible)
Rng.Value = "Aviation & Transportation"
ActiveSheet.AutoFilterMode = 0
End Sub
