Hello I hope this will make sense,
你好,我希望这是有道理的,
I have found this script that if a letter or number is in one cell it will input a value in the another cell
我发现这个脚本如果一个单元格中有一个字母或数字,它将在另一个单元格中输入一个值
I was wondering how I could add multiple rules to this.At the moment if C or 9 is entered in a cell then it produces the sentence "word here will show up"
我想知道如何为此添加多个规则。如果在单元格中输入C或9,那么它会生成句子“这里的单词会显示”
I want it to show up in a different cell not the adjacent one, and also I want to be able to add different letter to the list to output different values.
我希望它显示在不同的单元格而不是相邻的单元格中,并且我希望能够在列表中添加不同的字母以输出不同的值。
EG
Cell Has This in Column A Output when Macro runs in Column E
当宏在E列中运行时,单元格在列A中具有此输出
C or 9 "Word0"
C或9“Word0”
HELLO OR GOODBYE "Word1"
你好还是GOODBYE“Word1”
Pink or Yellow "Word2"
粉红色或黄色“Word2”
Etc, Any help would be great and appreciated as I am very new to VB and Macros.
等等,任何帮助都会很棒并且受到赞赏,因为我是VB和Macros的新手。
The code below only does C or 9 I thought I could just add .Forumula to the code below to add more words but it didn't work
下面的代码只做C或9我以为我可以添加.Forumula到下面的代码添加更多的单词,但它不起作用
Sub CheckValues()
Application.ScreenUpdating = False
With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)
.Formula = "=IF(MIN(FIND({""C"",9},A2&""C9""))<=LEN(A2),""Word0"","""")"
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub
2 个解决方案
#1
3
If you're going for a VBA approach then there's no real need to use .Formula
. VBA has a Select Case
construct that lets you evaluate an expression and carry out a bunch of options based on the outcome of the expression.
如果你想采用VBA方法,那么就没有必要使用.Formula。 VBA有一个Select Case结构,允许您根据表达式的结果计算表达式并执行一系列选项。
Try this:
Sub CheckValues()
Application.ScreenUpdating = False
Dim startRow As Integer
startRow = 2
Dim endRow As Integer
endRow = Range("A2").End(xlDown).row
Dim row As Integer
Dim word As String
For row = startRow To endRow
Select Case Split(Cells(row, 1).Value, " ")(0)
Case "C", "9"
word = "Word0"
Case "HELLO", "GOODBYE"
word = "Word1"
Case "Pink", "Yellow"
word = "Word2"
Case Else
word = ""
End Select
Cells(row, 5).Value = word
Next row
Application.ScreenUpdating = True
End Sub
As you add more options for column a, you only need to repeat the Case *expression*
pattern.
在为列a添加更多选项时,只需重复Case *表达式*模式。
#2
0
Or you could just amend your existing formula and do it without loops:
或者您可以修改现有的公式并在没有循环的情况下进行:
Sub SO()
With Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Offset(, 3)
.FormulaR1C1 = "=IFERROR(""Word""&ROUNDDOWN((MATCH(LEFT(RC[-3],SEARCH("" "",RC[-3])-1),{""C"",""9"",""HELLO"",""GOODBYE"",""Pink"",""Yellow""},0)/2)-0.1,0),"""")"
.Value = .Value
End With
End Sub
#1
3
If you're going for a VBA approach then there's no real need to use .Formula
. VBA has a Select Case
construct that lets you evaluate an expression and carry out a bunch of options based on the outcome of the expression.
如果你想采用VBA方法,那么就没有必要使用.Formula。 VBA有一个Select Case结构,允许您根据表达式的结果计算表达式并执行一系列选项。
Try this:
Sub CheckValues()
Application.ScreenUpdating = False
Dim startRow As Integer
startRow = 2
Dim endRow As Integer
endRow = Range("A2").End(xlDown).row
Dim row As Integer
Dim word As String
For row = startRow To endRow
Select Case Split(Cells(row, 1).Value, " ")(0)
Case "C", "9"
word = "Word0"
Case "HELLO", "GOODBYE"
word = "Word1"
Case "Pink", "Yellow"
word = "Word2"
Case Else
word = ""
End Select
Cells(row, 5).Value = word
Next row
Application.ScreenUpdating = True
End Sub
As you add more options for column a, you only need to repeat the Case *expression*
pattern.
在为列a添加更多选项时,只需重复Case *表达式*模式。
#2
0
Or you could just amend your existing formula and do it without loops:
或者您可以修改现有的公式并在没有循环的情况下进行:
Sub SO()
With Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Offset(, 3)
.FormulaR1C1 = "=IFERROR(""Word""&ROUNDDOWN((MATCH(LEFT(RC[-3],SEARCH("" "",RC[-3])-1),{""C"",""9"",""HELLO"",""GOODBYE"",""Pink"",""Yellow""},0)/2)-0.1,0),"""")"
.Value = .Value
End With
End Sub