在Excel VBA中isNumeric返回错误状态 [英] isNumeric returning wrong state in Excel VBA

问题描述

在下面的代码中，如果特定单元格中的数字是数字，我试图进行计算，否则从其他单元格返回数字.我认为我的实现是不正确的，因为如果第一个单元格不是数字，则只会填充else状态，反之亦然.你能告诉我如何解决这个问题吗?

In the code below, I was attempting to do a calculation if the number in the specific cell was numeric, else return number from other cell. I think that my implementation is incorrect as I only get the else state populating if the first cell is not numeric and vise versa. Can you tell me how to fix this?

这是数据示例:

第六项应在Meas-LO列中返回27.

The 6th entry should return 27 in the Meas-LO column.

谢谢

这是代码

Sub ReturnMarginal()

    'UpdatebySUPERtoolsforExcel2016
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWks As Worksheet
    Dim InterSectRange As Range
    Dim lowLimCol As Integer
    Dim hiLimCol As Integer
    Dim measCol As Integer

    Application.ScreenUpdating = False
    Set xWb = ActiveWorkbook
    For Each xWks In xWb.Sheets
        xRow = 1
        With xWks
           FindString = "LowLimit"
           If Not xWks.Rows(1).Find(FindString) Is Nothing Then
               .Cells(xRow, 16) = "Meas-LO"
               .Cells(xRow, 17) = "Meas-Hi"
               .Cells(xRow, 18) = "Min Value"
               .Cells(xRow, 19) = "Marginal"
               LastRow = .UsedRange.Rows.Count
               lowLimCol = Application.WorksheetFunction.Match("LowLimit", xWks.Range("1:1"), 0)
               hiLimCol = Application.WorksheetFunction.Match("HighLimit", xWks.Range("1:1"), 0)
               measLimCol = Application.WorksheetFunction.Match("MeasValue", xWks.Range("1:1"), 0)
                If IsNumeric(Cells(2, lowLimCol).Address(False, False)) Then
           .Range("P2:P" & LastRow).Formula = "=" & Cells(2, measLimCol).Address(False, False) & "-" & Cells(2, lowLimCol).Address(False, False)
                 Else
           .Range("P2:P" & LastRow).Formula = "=" & Cells(2, measLimCol).Address(False, False)
                 End If                   

               .Range("Q2:Q" & LastRow).Formula = "=" & Cells(2, hiLimCol).Address(False, False) & "-" & Cells(2, measLimCol).Address(False, False)

               .Range("R2").Formula = "=min(P2,Q2)"
               .Range("R2").AutoFill Destination:=.Range("R2:R" & LastRow)

               .Range("S2").Formula = "=IF(AND(R2>=-3, R2<=3), ""Marginal"", R2)"
               .Range("S2").AutoFill Destination:=.Range("S2:S" & LastRow)

           End If

        End With
        Application.ScreenUpdating = True 'turn it back on
    Next xWks

End Sub

推荐答案

理解问题第二部分的含义后，我认为关于公式设置方法的最快解决方法是在整个列中填充一个公式.

After understanding what you meant for the second part of your problem, I think the quickest fix for how your formula is set up is to fill the whole column with a formula.

这比遍历代码中的每个单元格 更快，以检查其是否为数字.您可以使用一个公式来填充整个范围，该公式可以对电子表格本身进行检查:例如=IF(ISNUMBER(C1),C1-D1,C1)

This will be quicker than looping through each cell in code to check if it is a number. You can fill the whole range with a formula that does the check on the spreadsheet itself: e.g. =IF(ISNUMBER(C1),C1-D1,C1)

要在您的代码中得到它，我将替换整个if isNumeric then...

To get that in your code, I would replace the whole if isNumeric then...

替换此部分:

If IsNumeric(Cells(2, lowLimCol).Address(False, False)) Then
    .Range("P2:P" & LastRow).Formula = "=" & Cells(2, measLimCol).Address(False, False) & "-" & Cells(2, lowLimCol).Address(False, False)
Else
    .Range("P2:P" & LastRow).Formula = "=" & Cells(2, measLimCol).Address(False, False)
End If  

此行:

.Range("P2:P" & lastRow).Formula = "=IF(ISNUMBER(" & .Cells(2, measLimCol).Value2 & ")," & Cells(2, measLimCol).Address(False, False) & "-" & Cells(2, lowLimCol).Address(False, False) & "," & Cells(2, measLimCol).Address(False, False) & ")"

这篇关于在Excel VBA中isNumeric返回错误状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！