☰

if 函数不检查 else if 语句

tag-icon • tag-icon microsoft-excel worksheet-function vba
if 函数不检查 else if 语句

我有一段代码，如果我的目标单元格值为空，那么它将跳转到跳过行。如果目标单元格值不为空，那么代码将运行。

以下是代码：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Integer
Dim copyRange As String
Dim ws As Worksheet
Dim pwd As String

    row = Range("$I$2").Value
    copyRange = "A" & row
    Set ws = Worksheets("Sheet1")
    

If Target.Cells = "$I$2" And Target.Value = "" Then
    GoTo Skip

ElseIf Target.Cells = "$I$2" And Target.Value <> "" Then
    Range(copyRange).EntireRow.Insert
    Range("M" & row & ":O" & row).Clear
    Range(copyRange & ":AA" & row).Interior.Color = RGB(217, 217, 217)
    Range(copyRange).Value = "General"
    Range(copyRange & ":I" & row).Merge
    Range(copyRange & ":I" & row).Locked = True
    Range(copyRange).EntireRow.Font.Bold = True
    Range(copyRange).EntireRow.Font.Size = 16
    Range(copyRange).EntireRow.Font.Name = "Arial"
    Range("J" & row).Value = "Type"
    Range("J" & row & ":L" & row).Merge
    Range("J" & row & ":L" & row).HorizontalAlignment = xlRight
    Range("J" & row & ":L" & row).IndentLevel = 1
    Range("J" & row & ":L" & row).Locked = True
    Range("M" & row & ":O" & row).Merge
    Range("M" & row & ":O" & row).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
    Range("M" & row & ":O" & row).IndentLevel = 2
    Range("M" & row & ":O" & row).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Belly 270,Tonello 420,Avantec 420,Acid Wash 270,Ozone 420"
    Range("P" & row).Value = "Qty"
    Range("P" & row & ":R" & row).Merge
    Range("P" & row & ":R" & row).HorizontalAlignment = xlRight
    Range("P" & row & ":R" & row).IndentLevel = 2
    Range("T" & row).Value = "pcs"
    Range("T" & row).Font.Size = 14
    Range("U" & row & ":AA" & row).Merge
End If

Skip:

MsgBox "End Of VBA!"

End Sub

但问题是，当我的目标单元格为空时，它会成功跳转到跳过行，但当目标单元格不为空时，它也会跳转到跳过行。它不会检查 else if 语句中的代码。帮帮忙吧！

答案1

尝试这个语法：

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Not Intersect(Target, Range("I2")) Is Nothing Then
Select Case True
Case Target.Value = vbNullString
MsgBox "Empty"
Case Not IsNumeric(Target.Value)
MsgBox "Enter a number"
Case IsNumeric(Target.Value)
    Dim rw As Integer
    Dim copyRange As String
    Dim pwd As String
    rw = Range("$I$2").Value
    copyRange = "A" & rw
    Range(copyRange).EntireRow.Insert Range("M" & rw & ":O" & rw).Clear
    Range(copyRange & ":AA" & rw).Interior.Color = RGB(217, 217, 217)
    Range(copyRange).Value = "General"
    Range(copyRange & ":I" & rw).Merge
    Range(copyRange & ":I" & rw).Locked = True
    Range(copyRange).EntireRow.Font.Bold = True
    Range(copyRange).EntireRow.Font.Size = 16
    Range(copyRange).EntireRow.Font.Name = "Arial"
    Range("J" & rw).Value = "Type"
    Range("J" & rw & ":L" & rw).Merge
    Range("J" & rw & ":L" & rw).HorizontalAlignment = xlRight
    Range("J" & rw & ":L" & rw).IndentLevel = 1
    Range("J" & rw & ":L" & rw).Locked = True
    Range("M" & rw & ":O" & rw).Merge
    Range("M" & rw & ":O" & rw).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
    Range("M" & rw & ":O" & rw).IndentLevel = 2
    Range("M" & rw & ":O" & rw).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Belly 270,Tonello 420,Avantec 420,Acid Wash 270,Ozone 420"
    Range("P" & rw).Value = "Qty"
    Range("P" & rw & ":R" & rw).Merge
    Range("P" & rw & ":R" & rw).HorizontalAlignment = xlRight
    Range("P" & rw & ":R" & rw).IndentLevel = 2
    Range("T" & rw).Value = "pcs"
    Range("T" & rw).Font.Size = 14
    Range("U" & rw & ":AA" & rw).Merge
MsgBox "End Of VBA!"
End Select
End If

End Sub

我将您的 row 变量更改为 rw，请尝试避免使用语言已经使用的变量名（例如 sheet1.range("I2").Row ）

相关内容