Excel VBA:将整个单元格替换为值
问题描述:
您能帮助我通过将通过单元格A3:A50的循环并用新值替换整个单元格。Excel VBA:将整个单元格替换为值
参考以下:
[ITA-IT]到IT
[JPN]到JA
[POR-BR]到PTBR
[温泉-ES]到ES
等
感谢的提示!
答
尝试:
Sub ReplaceValues()
Dim r As Range
Dim v() As Variant
Dim i As Integer
v = [{"ita-IT","IT";"jpn","JA";"por-BR","PTBR";"spa-ES","ES"}]
Set r = ActiveSheet.Range("A3:A50")
For i = LBound(v) To UBound(v)
r.Replace what:=v(i, 1), replacement:=v(i, 2), lookat:=xlWhole, MatchCase:=False
Next i
End Sub
编辑: 有有50个(或以上)更换对没有问题的,但是这将在一个表将它们存储在工作簿,而不是列出它们更容易管理在VBA数组:
可以更换具有文本前/使用通配符您查找值后单元格内容。因此,结合这些更改,您的代码现在变为:
Sub ReplaceValues2()
Dim r As Range
Dim v() As Variant
Dim i As Integer
v = Sheet1.ListObjects("tbReplacement").DataBodyRange
Set r = ActiveSheet.Range("A3:A50")
For i = LBound(v) To UBound(v)
r.Replace What:="*" & v(i, 1) & "*", Replacement:=v(i, 2), LookAt:=xlWhole, MatchCase:=False
Next i
End Sub
答
这是一种方法。如果你得到很多代码来代替它,看起来像意大利面条,如果有替代逻辑,这将是很好的建立在逻辑,但它做你的问题,并且,我希望,可读。
Sub ReplaceStrings()
Dim result As String
For Each myCell In Range("A3:A50")
Select Case myCell.Value
Case "[ita-IT]"
result = "IT"
Case "[jpn]"
result = "JA"
Case "[por-BR]"
result = "PTBR"
Case "[spa-ES]"
result = "ES"
Case Else
result = myCell.Value
End Select
myCell.Value = result
Next myCell
End Sub
编辑 - 去与原著的精神,但满足字符串中的匹配的要求,我换成案例与如果ELSEIF系列一样使用和通配符匹配。注意为了匹配方括号(假定这是你的意思),我必须将它们放在方括号中。我还修改了参考ActiveSheet是安全的,借鉴了另一个答案(我不是批评,只是表明另一种思考方式)。
Sub ReplaceStrings()
Dim result, s As String
For Each myCell In ActiveSheet.Range("A3:A50")
s = myCell.Value
If s Like ("*[[]ita-IT[]]*") Then
result = "IT"
ElseIf s Like "*[[]jpn[]]*" Then
result = "JA"
ElseIf s Like "*[[]por-BR[]]*" Then
result = "PTBR"
ElseIf s Like "*[[]spa-ES[]]*" Then
result = "ES"
Else
result = s
End If
myCell.Value = result
Next myCell
End Sub
好像你需要一个for循环和 –
里面的select case语句你当前使用的代码在哪里? – braX
为什么'[por-BR]'变成'PTBR'?没有任何有效的规则=> * ctrl * + * H *或手动执行可能会更快... –