结束 如果 没有块 If - 双精度 对于每个循环

Question

我正在尝试将一些代码放在一起进行一些简单的计算，但我正在努力通过 If 语句，它们对我来说似乎没有意义，但我的格式与我读过的类似问题的解决方案相同。

我在以下最终的"结束如果

没有块如果"上得到"结束如果"：

Sub SAP1C()
Dim i1, j1 As Range
Dim Material, Customer, Price As String
Dim MaterialStart, CustomerStart, PriceStart As Object
Dim Counter As Integer
Set i1 = Sheets("Invoice Prices").Range("B1:RL1")
Set j1 = Sheets("Invoice Prices").Range("A6:A500")
Set PriceStart = Sheets("SAP 1C").Range("A3")
Set MaterialStart = Sheets("SAP 1C").Range("J3")
Set CustomerStart = Sheets("SAP 1C").Range("I3")
Counter = 0
For Each i In i1
    If i = "" Then Exit Sub Else
        For Each j In j1
            If j <> "" Then
                Price = Cells(Application.WorksheetFunction.Row(j), Application.WorksheetFunction.Column(i)).Value
                Material = Application.WorksheetFunction.VLookup(j, Sheets("BTS").Range("F:G"), 2, 0)
                Customer = Application.WorksheetFunction.Text(Application.WorksheetFunction.VLookup(i, Sheets("Customer Hub").Range("A:G"), 7, 0), "0000000000")
                PriceStart.Offset(Counter, 0) = Price
                MaterialStart.Offset(Counter, 0) = Material
                CustomerStart.Offset(Counter, 0) = Customer
                Counter = Counter + 1
            End If
        Next j
    End If
Next i
End Sub  

我已经玩了一点代码，但无法完全弄清楚 - 有什么想法吗？
谢谢！

编辑：下面回答 - 非常感谢！

我稍微更改了代码并摆弄它以尝试使其在原始问题之外工作，但我遇到了错误。你能看看下面吗？

Sub SAP1C()
Dim i1, j1 As Range
Dim Material, Customer, Price As String
Dim MaterialStart, CustomerStart, PriceStart As Object
Dim Counter As Integer
Set i1 = Sheets("Invoice Prices").Range("B1:RL1")
Set j1 = Sheets("Invoice Prices").Range("A6:A300")
Set PriceStart = Sheets("SAP 1C").Range("A3")
Set MaterialStart = Sheets("SAP 1C").Range("J3")
Set CustomerStart = Sheets("SAP 1C").Range("I3")
Counter = 0
For Each i In i1
    If i = "" Then
        Exit Sub
    Else
        For Each j In j1
            If j <> "" Then
                If Application.WorksheetFunction.IsText(Sheets("Invoice Prices").Cells(j.Row, i.Column)) Then
                    Price = "POA"
                ElseIf Application.WorksheetFunction.IsErr(Sheets("Invoice Prices").Cells(j.Row, i.Column)) Then
                    Price = "POA"
                Else
                    Price = Round(Sheets("Invoice Prices").Cells(j.Row, i.Column).Value, 1)
                Material = Application.WorksheetFunction.VLookup(j, Sheets("BTS").Range("F:G"), 2, 0)
                Customer = "00" & Application.WorksheetFunction.VLookup(i, Sheets("Customer Hub").Range("A:G"), 7, 0)
                PriceStart.Offset(Counter, 0) = Price
                MaterialStart.Offset(Counter, 0) = Material
                CustomerStart.Offset(Counter, 0) = Customer
                Counter = Counter + 1
            End If
        Next j
    End If
Next i
End Sub  

"下一个没有为"在"下一个j"上？这究竟是如何工作的？我的 Ifs 都有 End Ifs 或似乎都以某种方式关闭，我的 For Each 都有 Next。一切似乎都在逻辑上流动，所以我真的很困惑我在这里错过了什么。谢谢！

Answer 1

如果像这样，请拆分出第一个行

    If i = "" Then
      Exit Sub
    Else
    'other code

VBA 脚本环境将您的第一个 if 语句视为内联 If，这意味着它会尝试将 else 语句评估为行中剩余的任何内容(无(。

Answer 2

在更新的代码中，命令后缺少一个End If Price = ...

If的语法为：

• 使用单行语句
• 或者你必须使用End If

例子：

If i = "" Then Exit Sub    ' no end if needed

这相当于

If i = "" Then
    Exit Sub 
End if

使用Else(和ElseIf(时也是如此

If i = "" Then Debug.print "empty" Else Debug.print i    
If i = "" Then
    Debug.print "empty"
Else
    Debug.print i 
End if

Answer 3

如下所示。

请注意，我已经更改了您的声明语句，因此您不会隐式将事物声明为变体。例如：

Dim i1, j1 As Range

i1 是变体，只有 j1 是范围。

我还对单元格使用了IsEmpty测试，并使用了Worksheets集合而不是Sheets。

待办事项：

为不返回匹配项Application.WorksheetFunction.VLookup进行一些错误处理。

Option Explicit
Public Sub SAP1C()
    Dim i1 As Range, j1 As Range, i As Range, j As Range
    Dim Material As String, Customer As String, Price As String
    Dim MaterialStart As Object, CustomerStart As Object, PriceStart As Object
    Dim Counter As Long
    Set i1 = Sheets("Invoice Prices").Range("B1:RL1")
    Set j1 = Sheets("Invoice Prices").Range("A6:A300")
    Set PriceStart = Sheets("SAP 1C").Range("A3")
    Set MaterialStart = Sheets("SAP 1C").Range("J3")
    Set CustomerStart = Sheets("SAP 1C").Range("I3")
    Counter = 0
    For Each i In i1
        If IsEmpty(i) Then
            Exit Sub
        Else
            For Each j In j1
                If Not IsEmpty(j) Then
                    If Application.WorksheetFunction.IsText(Worksheets("Invoice Prices").Cells(j.Row, i.Column)) Then
                        Price = "POA"
                    ElseIf Application.WorksheetFunction.IsErr(Worksheets("Invoice Prices").Cells(j.Row, i.Column)) Then
                        Price = "POA"
                    Else
                        Price = Round(Worksheets("Invoice Prices").Cells(j.Row, i.Column).Value, 1)
                        Material = Application.WorksheetFunction.VLookup(j, Worksheets("BTS").Range("F:G"), 2, 0)
                        Customer = "00" & Application.WorksheetFunction.VLookup(i, Worksheets("Customer Hub").Range("A:G"), 7, 0)
                        PriceStart.Offset(Counter, 0) = Price
                        MaterialStart.Offset(Counter, 0) = Material
                        CustomerStart.Offset(Counter, 0) = Customer
                        Counter = Counter + 1
                    End If
                End If
            Next j
        End If
    Next i
End Sub