时间戳

首页 > 解决方案 > if then else语句在vba excel中完全相同

excel - if then else语句在vba excel中完全相同

问题描述

我试图找出代码有什么问题，但我不能。在第一个 if 语句中，我想说如果移动是 ADD 并且如果工作表 1（DataEntry）的 3 个单元格与工作表 2（目录）的 3 个单元格相等，则不要输入，否则如果它们不相等则执行。在这两种情况下，它都会输入，因此我有重复项。任何人都可以帮忙吗？

ps：如果我删除 elseif 并且我只有 if 那么它会显示消息。

更新（2020 年 1 月 24 日）：昨天一切正常。几分钟前，我意识到当我在单元格 A、B、C 中键入一个希腊词时，例如“φίλτρο”“νερού”“κ.π”。excel无法识别。即使看起来试图在目录表上输入数据也没有发生任何事情，也没有出现。虽然奇怪的事情正在发生。有时一切正常（希腊人除外），有时会出现错误的消息框。并继续运行并显示消息，即使我单击确定然后 excel 崩溃。“我头晕了”，我想不清楚。

Sub AddChange()
Dim t1, t2, t3, t4, t5, t6
Dim t10, t11, t12, t13, t14, t15
Dim t18, t19, t20, t21, t22, t23
Dim arrayData As Variant
Dim arrayData2 As Variant
Dim cleanData As Range
Dim keli As Range
Dim baseSheet As Object
Dim formaSheet As Object
Dim Stock As Object
Dim meter As Long
Dim meter2 As Long
Dim Movement As String
Dim i As Integer


    Set Stock = Sheets("StockMovements")
    Set baseSheet = Sheets("Catalogue")
    Set formaSheet = Sheets("DataEntry")
    Set t1 = formaSheet.Range("A6")
    Set t2 = formaSheet.Range("B6")
    Set t3 = formaSheet.Range("C6")
    Set t4 = formaSheet.Range("D6")
    Set t5 = formaSheet.Range("E6")
    Set t6 = formaSheet.Range("F6")
    Set t10 = baseSheet.Range("A2")
    Set t11 = baseSheet.Range("B2")
    Set t12 = baseSheet.Range("C2")
    Set t13 = baseSheet.Range("D2")
    Set t14 = baseSheet.Range("E2")
    Set t15 = baseSheet.Range("F2")

    Set t18 = Stock.Range("B2")
    Set t19 = Stock.Range("C2")
    Set t20 = Stock.Range("D2")
    Set t21 = Stock.Range("E2")
    Set t22 = Stock.Range("F2")
    Set t23 = Stock.Range("G2")

    Movement = Range("G6").Value

If Movement Like "ADD NEW" Then

 For i = 2 To 10000



    With Worksheets("DataEntry")
        If (UCase(Trim(Worksheets("DataEntry").Cells(6, "A"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "A")))) _
            And (UCase(Trim(Worksheets("DataEntry").Cells(6, "B"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "B")))) _
            And (UCase(Trim(Worksheets("DataEntry").Cells(6, "C"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "C")))) _
        Then
            MsgBox "The product already exists! Select change and continue.", vbOKCancel

        ElseIf Not (UCase(Trim(Worksheets("DataEntry").Cells(6, "A"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "A")))) _
            And (UCase(Trim(Worksheets("DataEntry").Cells(6, "B"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "B")))) _
            And (UCase(Trim(Worksheets("DataEntry").Cells(6, "C"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "C")))) _
        Then
             meter = Application.WorksheetFunction.CountA(baseSheet.Range("A:A"))
             meter2 = Application.WorksheetFunction.CountA(Stock.Range("A:A"))

             arrayData = VBA.Array(t1, t2, t3, t4, t5, t6)
             'arrayData2 = VBA.Array(t1, t2, t3, t4, t5, t6)
             Set cleanData = Union(t1, t2, t3, t4, t5)

             With cleanData.Cells
              Set keli = .Find(What:="*", LookIn:=xlValues)
              If keli Is Nothing Then
              GoTo telos
              End If
             End With

             baseSheet.Cells(meter + 1, 1).Resize(, 6) = arrayData
             Stock.Cells(meter2 + 1, 1).Resize(, 6) = arrayData
             cleanData.ClearContents
        End If
    End With
Next i

End If



If Movement Like "CHANGE" Then


 For i = 2 To 10000



    With Worksheets("DataEntry")
    If (UCase(Trim(Worksheets("DataEntry").Cells(6, "A"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "A")))) _
            And (UCase(Trim(Worksheets("DataEntry").Cells(6, "B"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "B")))) _
            And (UCase(Trim(Worksheets("DataEntry").Cells(6, "C"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "C")))) _
    Then

         MsgBox "Do you want to continue?", vbOKCancel
         Worksheets("Catalogue").Cells(i, "A") = Worksheets("DataEntry").Cells(6, "A")
         Worksheets("Catalogue").Cells(i, "B") = Worksheets("DataEntry").Cells(6, "B")
         Worksheets("Catalogue").Cells(i, "C") = Worksheets("DataEntry").Cells(6, "C")
         Worksheets("Catalogue").Cells(i, "D") = Worksheets("DataEntry").Cells(6, "D")
         Worksheets("Catalogue").Cells(i, "E") = Worksheets("DataEntry").Cells(6, "E")
         Worksheets("Catalogue").Cells(i, "F") = Worksheets("DataEntry").Cells(6, "F")

   ElseIf Not (UCase(Trim(Worksheets("DataEntry").Cells(6, "A"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "A")))) _
            And (UCase(Trim(Worksheets("DataEntry").Cells(6, "B"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "B")))) _
            And (UCase(Trim(Worksheets("DataEntry").Cells(6, "C"))) = UCase(Trim(Worksheets("Catalogue").Cells(i, "C")))) _
        Then
             MsgBox "The product does not exist. Select add new.", vbOKCancel
             Set cleanData = Union(t1, t2, t3, t4, t5)

             With cleanData.Cells
              Set keli = .Find(What:="*", LookIn:=xlValues)
              If keli Is Nothing Then
              GoTo telos
              End If
             End With
             cleanData.ClearContents
        End If
    End With
Next i

End If


telos:

End Sub

我输入一个新项目并单击更改，它应该会弹出一条消息“该产品不存在。选择添加新的。” 它显示此消息，然后弹出另一条消息并在那里崩溃。 在此处输入图像描述

当我尝试添加已经存在的产品时，发生了类似的事情。首先显示正确的消息，然后显示另一个并崩溃。

标签： excelvba

解决方案

问题不在于希腊字符.. 问题在于 ElseIF Not。所以我用这个改变代码

ElseIf (UCase(Trim(Worksheets("DataEntry").Cells(6, "A"))) <> UCase(Trim(Worksheets("Catalogue").Cells(i, "A")))) _
        And (UCase(Trim(Worksheets("DataEntry").Cells(6, "B"))) <> UCase(Trim(Worksheets("Catalogue").Cells(i, "B")))) _
    Then

推荐阅读