Excel VBA 用另一个值替换列值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5248272/
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
Excel VBA to replace column values with another value?
提问by Dave Mackey
I have a worksheet with around 3,000 rows. One of these columns contains ethnicity data but it is in numeric format, rather than text format, e.g.:
我有一个大约有 3,000 行的工作表。这些列之一包含种族数据，但它是数字格式，而不是文本格式，例如：
- 1 = American Indian or Alaskan Native
- 2 = Asian, Asian American, or Pacific Islander
- 3 = African American or Black
- 4 = Mexican or Mexican American
- 1 = 美洲印第安人或阿拉斯加原住民
- 2 = 亚洲人、亚裔美国人或太平洋岛民
- 3 = 非裔美国人或黑人
- 4 = 墨西哥人或墨西哥裔美国人
I'd like to write a module in VBA that uses a Select Case (or...?) to swap out the values in this column from the integer to text. Can someone show me how to accomplish this?
我想在 VBA 中编写一个模块，该模块使用 Select Case（或...？）将此列中的值从整数交换为文本。有人可以告诉我如何做到这一点吗？
回答by Mikeb
A non VBA (simpler, in my opinion) approach in Excel would be to create a lookup table with that data - the integer in the first column, and the text in the second, and then in the column next to your data, use VLOOKUP to find the corresponding text.
Excel 中的非 VBA（在我看来更简单）方法是使用该数据创建一个查找表 - 第一列中的整数，第二列中的文本，然后在数据旁边的列中，使用 VLOOKUP找到相应的文字。
回答by Maverik
Heres a quick VBA mashup that will replace your Current Selection values with substitutes:
这是一个快速的 VBA 混搭，它将用替代品替换您的当前选择值：
Option Explicit
Option Base 1
Public Sub ReplaceData()
Dim RangeCells As Range
Dim LookupArray(4) As String, RangeCell As Range
LookupArray(1) = "Test A"
LookupArray(2) = "Test B"
LookupArray(3) = "Test C"
LookupArray(4) = "Test D"
For Each RangeCell In Selection
If IsNumeric(RangeCell.Value) And RangeCell.Value <= UBound(LookupArray) And RangeCell.Value >= LBound(LookupArray) Then
RangeCell.Value = LookupArray(Val(RangeCell.Value))
End If
Next RangeCell
End Sub
Assumptions: all possible cell values are covered in array index. Otherwise select case is probably what you want to switch to. Let me know if you can't figure how to switch to that.
假设：所有可能的单元格值都包含在数组索引中。否则 select case 可能是您想要切换到的。如果您不知道如何切换到那个，请告诉我。
Hope this solves your problem.
希望这能解决您的问题。
回答by GrzMat
To do something like VLOOKUP, but with replacement you can try the below
要执行类似 VLOOKUP 的操作，但要替换，您可以尝试以下操作
Option Explicit
Sub ReplaceData()
Dim i As Integer
Dim ABCArray() As Variant, DEFArray As Variant
ABCArray = Array("A", "b", "C")
DEFArray = Array("D", "E", "F")
With ActiveSheet.Range("A:A")
For i = LBound(ABCArray) To UBound(ABCArray)
.Replace What:=ABCArray(i), _
Replacement:=DEFArray(i), _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False
Next i
End With
End Sub
I cannot agree with VLOOKUP being better for one simple reason: we do macros to automate repeating actions and I assume someone is looking for macro solution mostly in cases like that.
我不能同意 VLOOKUP 更好的一个简单原因：我们使用宏来自动执行重复操作，我认为有人主要在这种情况下寻找宏解决方案。
