通过按钮运行时，代码失败

Question

我在链接到按钮的呼叫序列中具有此过程。当按钮调用时，这并不总是可行的，但在单独运行时始终工作。关于为什么有什么想法吗？此过程的目的是分析编号帐户并对其进行分类。

 Sub IndexMatchBsIS()
        'declare the variable
        Dim lr As Long
        'create formula to calculate the last row based on number of materials in column J
        lr = Sheets("JDE TB").Cells(1048576, 1).End(xlUp).Row
        'declare a variant to store the row count
        Dim x As Variant
        'row that I want to start in
        x = 2
        'declare variables as ranges
        Dim rng As Range, Cell As Range
        'initialize the Range object rng with Range("E2:the last row in column E")
        Set rng = Sheets("JDE TB").Range("n2" & ":" & "n" & lr)

        'Add the For Each Next loop.
        For Each Cell In rng
        'Perform the index match calculation without using the spreadsheet filldown method
    If Cell(x, 3) > 50000 Then
    Cells(x, 14).Value = "IS"
    Else
    Cells(x, 14).Value = "BS"
    End If

        'Add the next row to the current count
        x = x + 1

            'loop to the next cell until complete
            Next Cell
        'exit the function
        Sheets("JDE TB").Range("k1").Value = "Entity"
        Sheets("JDE TB").Range("l1").Value = "ONESHIRE HFM Account"
        Sheets("JDE TB").Range("m1").Value = "ONESHIRE HFM Account Desc."
        Sheets("JDE TB").Range("n1").Value = "BS/IS"
        Sheets("JDE TB").Range("o1").Value = "Lv4 JDE"
    End Sub

Answer 1

我要猜测你的if语句应该看起来像

  If Cell(x, 3) > 50000 Then
    Cell(x, 14).Value = "IS"
  Else
    Cell(x, 14).Value = "BS"
  End If

看看我是如何使每个单数而不是复数的？Cells是指活动工作簿，您的Cell变量来自您的合格范围。

Answer 2

使用偏移而不是x变量。

If Cell.Offset(, 3) > 50000 Then    
    Cell.Offset(, 14).Value = "IS"
Else
    Cell.Offset(, 14).Value = "BS"
End If