我的 If-ElseIf 语句遇到问题，因为单元格值没有返回正确的结果。
以下是我的模块中的代码：
第一批：
Sub compare2()
Dim i As Long
Dim A As Long
Dim B As Long
Dim c As Long
A = 14
B = 15
c = 16
Do While A <= 42
i = 2
Do Until Len(Cells(i, A)) = 0
If Cells(i, A) = "Green" And Cells(i, B) = "Rollup" Then
Cells(i, c) = "Green"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Rollup" Then
Cells(i, c) = "Rollup"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Green" Then
Cells(i, c) = "Green"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Yellow" Then
Cells(i, c) = "Yellow"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Red" Then
Cells(i, c) = "Red"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Overdue" Then
Cells(i, c) = "Overdue"
ElseIf Cells(i, A) = " " And Cells(i, B) = " " Then
Cells(i, c) = " "
Else
End If
i = i + 1
Loop
A = A + 4
B = A + 1
c = A + 2
Loop
End Sub
模块中的第二批：
Public Sub single_change(changed_cell As Range)
Dim sales_cell As Range
Dim production_cell As Range
Dim day_cell As Range
Dim col_num As Integer
col_num = changed_cell.Column
If changed_cell.Column < 14 Then 'Dont do anything before Col N
Exit Sub
Else
col_num = changed_cell.Column - 14
End If
If col_num Mod 4 = 0 Then
Set sales_cell = changed_cell
Set production_cell = changed_cell.Offset(, 1)
Set day_cell = production_cell.Offset(, 1)
ElseIf (col_num - 1) Mod 4 = 0 Then
Set sales_cell = changed_cell.Offset(, -1)
Set production_cell = changed_cell
Set day_cell = production_cell.Offset(, -1)
Else
'Dont do anything between Col N,O and their repeated values
Exit Sub
End If
On Error GoTo multiple_changes
If sales_cell = "Green" And production_cell = "Rollup" Then
day_cell = "Green"
ElseIf sales_cell = "Rollup" And production_cell = "Rollup" Then
day_cell = "Rollup"
ElseIf sales_cell = "Rollup" And production_cell = "Green" Then
day_cell = "Green"
ElseIf sales_cell = "Rollup" And production_cell = "Yellow" Then
day_cell = "Yellow"
ElseIf sales_cell = "Rollup" And production_cell = "Red" Then
day_cell = "Red"
ElseIf sales_cell = "Rollup" And production_cell = "Overdue" Then
day_cell = "Overdue"
ElseIf sales_cell = " " And production_cell = " " Then
day_cell = " "
Else
'Do nothing
End If
Exit Sub
multiple_changes:
Dim i As Long
Dim A As Long
Dim B As Long
Dim c As Long
A = 14
B = 15
c = 16
Do While A <= 42
i = 2
Do Until Len(Cells(i, A)) = 0
If Cells(i, A) = "Green" And Cells(i, B) = "Rollup" Then
Cells(i, c) = "Green"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Rollup" Then
Cells(i, c) = "Rollup"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Green" Then
Cells(i, c) = "Green"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Yellow" Then
Cells(i, c) = "Yellow"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Red" Then
Cells(i, c) = "Red"
ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Overdue" Then
Cells(i, c) = "Overdue"
ElseIf Cells(i, A) = " " And Cells(i, B) = " " Then
Cells(i, c) = " "
Else
End If
i = i + 1
Loop
A = A + 4
B = A + 1
c = A + 2
Loop
End Sub
这是在工作表中：
Private Sub Worksheet_Change(ByVal Target As Range)
Call Module1.single_change(Target)
End Sub
最初我的代码有运行时错误"应用程序定义或对象定义错误"，但所有If和ElseIf语句都返回了正确的值。将我的代码更改为用户推荐的代码后(请参阅此处 VBA 运行时错误 1004"应用程序定义或对象定义的错误"在使用 Range.Offset 时(，即使我输入了不同的值，单元格值也总是自行更改。
例如，当我在单元格 N2(销售列(中输入Rollup然后在单元格 O2(生产列(中输入Green时，单元格 P2(列日(应该根据ElseIf sales_cell = "Rollup" And production_cell = "Green" Then day_cell = "Green"返回Green。但是，当我在填写单元格O2(生产(后按Enter键时，单元格N2(销售(自动更改为Green。然后，我将单元格 N2 改回Rollup，宏工作了。
从结果打印屏幕
有谁知道发生了什么？谢谢！任何帮助都非常感谢!!
这不是您问题的解决方案。
我还没有看过你的方法的返回结果，但我建议重构你的代码以使其更具可读性。
您也许可以自己发现问题。
删除所有这些If..ElseIf...Else语句，并用一个更容易阅读(而且更快(的Select替换它们。
Option Explicit
Private Const Green As String = "Green"
Private Const Rollup As String = "Rollup"
Private Const Yellow As String = "Yellow"
Sub Something()
Dim A As Long, B As Long, C As Long, i As Long
Select Case True
Case Cells(i, A) = Green And Cells(i, B) = Rollup: Cells(i, C) = Green
Case Cells(i, A) = Rollup And Cells(i, B) = Rollup: Cells(i, C) = Rollup
Case Cells(i, A) = Rollup And Cells(i, B) = Green: Cells(i, C) = Green
'Add more cases here...
End Select
End Sub
祝你好运。