使用 VBA 宏查找并替换一个单元格中的部分字符串及其下方单元格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26560775/
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
Find and replace part of string in one cell with value of cell below it with VBA Macro
提问by Danjayf
I have multiple cells on one row in excel that all contain HTML. Each cell is in a different lang but has the same URLs throughout eg: (...loc=en-uk) I need to find and replace this in each cell but with a different value for each cell eg: find "en-uk" in cell A, Replace with "it-it", mover to next cell, find "en-uk", replace with "es-es" and so on till empty cell is reached.
我在 excel 的一行中有多个单元格，所有单元格都包含 HTML。每个单元格都在不同的语言中，但在整个过程中具有相同的 URL，例如：(...loc=en-uk) 我需要在每个单元格中找到并替换它，但每个单元格的值不同，例如：find "en-uk " 在单元格 A 中，替换为 "it-it"，移动到下一个单元格，找到 "en-uk"，替换为 "es-es"，依此类推，直到到达空单元格。
Had tried the following but it only takes find and replace values from the same 2 cells:
尝试了以下操作，但它只需要从相同的 2 个单元格中查找和替换值：
Dim Findtext As String
Dim Replacetext As String
Findtext = Range("B2").Value
Replacetext = Range("C2").Value
Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
采纳答案by peege
You will want to use Cells, instead of Range, and loop through the columns using a Variable "lCol". Range(Column Letter , Row Number) has it's limitations because you are using a letter. It's hard to add 1 to C.. C + 1 = D? So use Cells(row#, col#). That way, you can incrementally work your way through the columns or rows using numbers instead of letters. In this case, the variable is the column, so we will use lCol and loop from 1 to the Last Column Number. Replacing the text from an original source string using your original post as a starting point.
您将需要使用单元格而不是范围，并使用变量“lCol”遍历列。Range(Column Letter , Row Number) 有它的局限性，因为您使用的是字母。很难给 C 加 1.. C + 1 = D？所以使用 Cells(row#, col#)。这样，您可以使用数字而不是字母逐步浏览列或行。在这种情况下，变量是列，因此我们将使用 lCol 并从 1 循环到最后一列编号。使用原始帖子作为起点替换原始源字符串中的文本。
Note: I'm not sure where your original string was. Also, if you know what you are replacing from the original string, and find yourself looking at Row2 being all the same thing across the sheet, you can just set that in your replaceString statement below.
注意：我不确定您的原始字符串在哪里。此外，如果您知道要从原始字符串中替换什么，并且发现自己在整个工作表中看到 Row2 都是相同的东西，则可以在下面的 replaceString 语句中进行设置。
Try This - It's only a few lines, once you take out all the comments.
试试这个 - 一旦你取出所有的评论，它只有几行。
Sub TextReplace()
Dim sheetName As String
Dim findText As String
Dim replaceText As String
Dim lastCol As Long
Dim lCol As Long
'Set the sheetName here, so you don't have to change it multiple times later
sheetName = "Sheet1"
'Check the Last Column with a value in it in Row 3. Assuming Row 3 has the Replacement text.
lastCol = Sheets(sheetName).Cells(3, Columns.Count).End(xlToLeft).Column
'Assuming you have an original string in a cell. Range("A1"), in this case.
origString = Sheets(sheetName).Cells(1, 1).Value
'Loop through each Column - lCol is going to increase by 1 each time.
'Starting on Column B,since Column A contains your original String.
For lCol = 2 To lastCol
'Using Cells(row#, col#), instead of Range(ColLetter, row#) gives you the ability to loop with lCol.
findText = Sheets(sheetName).Cells(2, lCol).Value
replaceText = Sheets(sheetName).Cells(3, lCol).Value
'Put the value from Range("A1") with the text replaced from Row 2 with Row 3's value.
Sheets(sheetName).Cells(1, lCol) = Replace(origString, findText, replaceText)
Next lCol
End Sub
Edit: Updated Column to start on B, instead of A.
编辑：更新列以从 B 开始，而不是从 A 开始。
回答by ZAT
Try this (for row 16 and for row 20 for example):
试试这个（例如第 16 行和第 20 行）：
Sub ReplaceTextinRow()
Dim lastCol, iter
lastCol = ActiveSheet.UsedRange.Columns.Count 'this approach is not always applicable
For iter = 1 To lastCol
'Cells(16, iter).Offset(4, 0).Value = Replace(Cells(16, iter).Value, "en-us", "en-uk")
Cells(20, iter).value = Replace(Cells(20, iter).value, Cells(20, iter).Offset(-4, 0).Value)
Next
End Sub
