检查单元格是否是 Excel VBA 中的数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44733098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if cell is a number in Excel VBA
提问by Joe
in the code below, I am working on doing numeric calculations on cells. Sometimes though, one of the cells(say for example the"LosLimit") may contain a non-numeric value like "---". How do I check if the cell is numeric or not.
在下面的代码中，我正在对单元格进行数值计算。但有时，其中一个单元格（例如“LosLimit”）可能包含非数字值，如“---”。如何检查单元格是否为数字。
If it is a number, then do calculation, else return value from "MeasValue"
如果是数字，则进行计算，否则从“MeasValue”返回值
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)
.Range("P2:P" & lastRow).Formula = "=IF(ISNUMBER(" & .Cells(2, lowLimCol).Value2 & ")," & Cells(2, measLimCol).Address(False, False) & "-" & Cells(2, lowLimCol).Address(False, False) & "," & Cells(2, measLimCol).Address(False, False) & ")"
.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
回答by jamheadart
Your answer was in the question! There is a function isNumeric(variable_goes_here) which will return a true or false e.g.
你的答案在问题中！有一个函数 isNumeric(variable_goes_here) 它将返回真或假，例如
if isNumeric(x) then msgbox("Woop!")
will return a true and give you the message box for x = 45but will skip if x = "Not a number"
将返回一个 true 并为您提供消息框，x = 45但如果x = "Not a number"
