GoodDay to you all!
祝大家好!
I am working on an excel project and I am looking for some help. I don't mind whether this is solved via a formula or a script.
我正在开展一个excel项目,我正在寻求一些帮助。我不介意这是通过公式还是脚本解决的。
To begin with I will provide the big picture. I have a whole bunch of SQL Queries that call on various fields from different locations. These field titles have now changed. I would like to update the SQL Queries with the new names of the fields. As such, I was going to copy all the queries into an excel spreadsheet and then lookup the field names in the string and replace them with the new field names from a table in another sheet. The problem is that there is approximately 10 field names per query that need replacing.
首先,我将提供大局。我有一大堆SQL查询调用来自不同位置的各种字段。这些字段标题现在已经改变。我想用字段的新名称更新SQL查询。因此,我打算将所有查询复制到Excel电子表格中,然后在字符串中查找字段名称,并将其替换为另一个表格中的表格中的新字段名称。问题是每个查询大约有10个字段名称需要替换。
So this is what I was thinking. Essentially, if I have a string in cell A1 with the words: "We are the very best". For every word that exists in the string I would like to check if it exists in a table in another sheet. If the word exists in the table, replace it. If not then move onto the next word. Continue for every word in the cell.
所以这就是我的想法。基本上,如果我在单元格A1中有一个字符串,其中包含“我们是最好的”字样。对于字符串中存在的每个单词,我想检查它是否存在于另一个表中的表中。如果表中存在该单词,请将其替换。如果没有,那么转到下一个单词。继续单元格中的每个单词。
Any help that can be provided is very much appreciated. Thanks in advance!
我们非常感谢您提供的任何帮助。提前致谢!
1 个解决方案
#1
0
Consider:
Sub FixIt()
Dim r As Range, A As Range
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Set A = s1.Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
For Each r In A
v = " " & r.Value & " "
For j = 1 To 10
oldv = " " & s2.Cells(j, 1).Text & " "
newv = " " & s2.Cells(j, 2).Text & " "
v = Replace(v, oldv, newv)
Next j
r.Value = Trim(v)
Next r
End Sub
In this example the replacement table is in Sheet2, cells A1 thru B10...............I pad with "spaces" to insure whole word replacement and to avoid the need to Split and Join.
在这个例子中,替换表在Sheet2中,单元格A1到B10 ................我用“空格”填充以确保整个单词替换并避免需要拆分和连接。
#1
0
Consider:
Sub FixIt()
Dim r As Range, A As Range
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Set A = s1.Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
For Each r In A
v = " " & r.Value & " "
For j = 1 To 10
oldv = " " & s2.Cells(j, 1).Text & " "
newv = " " & s2.Cells(j, 2).Text & " "
v = Replace(v, oldv, newv)
Next j
r.Value = Trim(v)
Next r
End Sub
In this example the replacement table is in Sheet2, cells A1 thru B10...............I pad with "spaces" to insure whole word replacement and to avoid the need to Split and Join.
在这个例子中,替换表在Sheet2中,单元格A1到B10 ................我用“空格”填充以确保整个单词替换并避免需要拆分和连接。