宏代码 - 检查某列文本字符串，并在下一列中放置相应的文本 [英] Macro code - check a column for certain text strings and place corresponding text in the next column

问题描述

我想找到一个宏代码，让我可以检查列的每一行是否有多个文本字符串。 如果它与特定文本字符串匹配，则会在相同的行中添加相应的文本，但在下一列中。 例如：

I'd like find the macro code that will let me check each row of a column for multiple text strings.  If it matches a particular text string it will add the corresponding text in the same row but in the next column.  For example:

如果F2 ="A组"中的文字为然后加上"词汇"这个词。在G2。

If the text in F2 = "Group A" then put the word "Vocabulary" in G2.

如果F2 ="B组"中的文字然后加上"观察"一词在G2

If the text in F2 = "Group B" then put the word "Watch" in G2

我需要它来为F列中的每一行输入22个可能的文本字符串。  F栏中也可能没有文字。 在这种情况下，G列中没有任何内容。

I need it to go through 22 possible text strings for each row in column F.  There's also a possibility that there will be no text in the F column.  In which case nothing gets put in the G column.

您如何看待？ 你能救我吗？

What do you think?  Can you help me out?

我很感激！

推荐答案

你好Claytronik，

Hi Claytronik,

你可以将特定的文本字符串及其相应的文本放在字典中，然后遍历列中的单元格F.如果字典键包含单元格中的任何值，我们可以从字典中获取其相应的文本，然后将
放入下一列。

You could put the the particular text string and its corresponding text in a dictionary and then iterate through cells in column F. If the dictionary keys contains any value in the cell, we could get its corresponding text from the dictionary and then put it in the next column.

这是示例。

Sub Macro1()
'Need add reference to Microsoft Scripting Runtime
Dim dict As Scripting.Dictionary
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "Group A", "Vocabulary"
dict.Add "Group B", "Watch"
dict.Add "Group C", "Test"
Application.ScreenUpdating = False
'get columnF's last Row index
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Row
'iterate through row 2 to last row in ColumnF
For i = 2 To lastRow
If dict.Exists(ActiveSheet.Cells(i, 6).Value) Then
ActiveSheet.Cells(i, 7).Value = dict(ActiveSheet.Cells(i, 6).Value)
End If
Next i
Application.ScreenUpdating = True
End Sub

最好的问候，

Terry

这篇关于宏代码 - 检查某列文本字符串，并在下一列中放置相应的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！