IsNumeric函数为空单元格返回true [英] IsNumeric function returning true for an empty cell

问题描述

我运行一个宏，该宏从PDF文件复制表格并将其保存在Excel中。
一些表包含空单元格，在我的分析中，我需要知道空单元格的数量。
我有一个遍历每一列的函数，以检查该单元格中的值是否为数字。
的麻烦是当我在一个空单元格上运行此函数时它返回true。我什至尝试使用Isblank（）函数手动检查单元格，它返回 false。 （如果我在粘贴范围之外的任何单元格上尝试此操作，它将返回 true）

I run a macro that copies tables from a PDF file and saves them on Excel. some of the tables contain empty cells and in my analysis I need to know the number of cells that are empty. I have a function that iterates through each column to check if the value within that cell is numeric or not. the trouble is when I run this function on an empty cell it returns true. I even tried manually cheeking the cells using the Isblank() function and it returns "false". (if I try this on any cell outside the pasted range it returns "true")

我猜测当我从PDF复制并粘贴内容时，它会粘贴一些值对于空单元格。

I am guessing that when I copy and paste things from PDF it somehow pastes some value for the empty cells.

有人遇到过类似的问题吗？如果是这样，有什么想法可以解决吗？

did anyone ever encounter a similar problem? if so, any ideas on how it can be solved?

如果有帮助，这是我用来复制和粘贴的代码

if it is any help here is the code I use to copy and paste

'Initialize Acrobat by creating App object
Set PDFApp = CreateObject("AcroExch.App")

'Set AVDoc object
Set PDFDoc = CreateObject("AcroExch.AVDoc")

'Open the PDF
If PDFDoc.Open(PDFPath, "") = True Then
    PDFDoc.BringToFront

    'Maximize the document
    Call PDFDoc.Maximize(True)

    Set PDFPageView = PDFDoc.GetAVPageView()

    'Go to the desired page
    'The first page is 0
    Call PDFPageView.GoTo(DisplayPage - 1)

    '-------------
    'ZOOM options
    '-------------
    '0 = AVZoomNoVary
    '1 = AVZoomFitPage
    '2 = AVZoomFitWidth
    '3 = AVZoomFitHeight
    '4 = AVZoomFitVisibleWidth
    '5 = AVZoomPreferred

    'Set the page view of the pdf
    Call PDFPageView.ZoomTo(2, 50)

End If

Set PDFApp = Nothing
Set PDFDoc = Nothing

On Error Resume Next

'Show the adobe application
PDFApp.Show

'Set the focus to adobe acrobat pro
AppActivate "Adobe Acrobat Pro"

'Select All Data In The PDF File's Active Page
SendKeys ("^a"), True

'Right-Click Mouse
SendKeys ("+{F10}"), True

'Copy Data As Table
SendKeys ("c"), True

'Minimize Adobe Window
SendKeys ("%n"), True

'Select Next Paste Cell
Range("A" & Range("A1").SpecialCells(xlLastCell).Row).Select
'Cells(1, 1).Select
'Paste Data In This Workbook's Worksheet
ActiveSheet.Paste

推荐答案

在某些情况下，最好检查单元格内字符的长度，而不是使用 isNumeric（），或检查是否存在错误等...

There are cases where it is better to check the length of the characters inside cells instead of using the isNumeric(), or check for errors etc...

例如，尝试以下代码

建立活动工作表中使用的范围，然后通过检查每个单元格的长度（len（））进行迭代

您可以在VBE中查看即时窗口 CTRL + G 来查看哪些单元格地址为空 或 等到宏完成执行后，会出现一个消息框，告诉您范围内有多少空单元格。

you can look at Immediate Window CTRL+G in VBE to see which cell addresses are empty or wait until the macro finishes executing and you will be welcomed with a Message Box saying how many empty cells are within the range

Option Explicit

Sub CheckForEmptyCells()

    Dim lastCol As Range
    Set lastCol = ActiveSheet.Cells.Find(What:="*", After:=ActiveSheet.Cells(1, 1), LookIn:=xlFormulas, _
              LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

    Dim rng As Range
    Set rng = Range("A1:" & lastCol.Address)

    Dim cnt As Long
    cnt = 0

    Dim cell As Range
    For Each cell In rng
        If Len(cell) < 1 Then
            Debug.Print cell.Address
            cnt = cnt + 1
        End If
    Next

    MsgBox "there are " & cnt & " empty cells within the range " & rng.Address
End Sub

这篇关于IsNumeric函数为空单元格返回true的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！