IsNumeric function returning true for an empty cell

Question

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")

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

Deluca

Have you try with 'IsDBNull' ? that return true or false? – Expectant

I am not sure how to use 'IsDBNull' but I used 'IsNull' and it returns false – Deluca

when I use 'IsDBNull' I get sub or function not defined – Deluca

when I open the table click "ctrl G" and chose blank those cells are not highlighted despite being empty. – Deluca

Answer 1

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...

For example try the below code

it establishes the Range used in the active worksheet then iterates through checking the length (len()) of each cell

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

Growing

Ah come one! with a 9-sec lag! :) I +1 yours because is right (like mine) although it is more completed (I wouldn't have written mine if you would have been 10 secs quicker). – Motch

@varocarbas thanks. you are right, both answers are correct. so both deserve +1 – Growing

Thanks man, I am not sure how stackoverflow works but I voted up both answers – Deluca

@Deluca thanks for voting up and welcome to stackoverflow. If you are happy with an answer you can click the green check mark below the voting to accept the answer - it's how we say thank you on stackoverflow. – Growing

@Deluca it is weird: I have 1 upvote, mehow has 1 upvote. I upvoted him and he upvoted me... I am not even sure if you have enough reputation for upvoting. Anyway... you should select the right answer as Mehow is indicating you (by clicking on the left-hand side tick); I do recognise that his answer is more complete than mine. – Motch

@varocarbas youre right again. i think it required 125 reputation to vote. so accepting an answer +2 upvotes on your question should be enough for you to be able to vote – Growing

I thought it worked and then it didn't. turns out it was just a coincidence that the first numbers matched:(. when I click CTRL+G and select blanks those empty cells that were copied from the pdf file are not highlighted – Deluca

Answer 2

This is kind of a hackish solution, but works as a simple solution.

Since 'IsNumeric(p variant)' uses a variant, you can append a "-" to the input parameter. That means null gets interpreted as "-" which is not a number, where as a true number will get treated as a negative number, and thereby meet the condition of truly being a number. (although now negative)

IsNumeric("-" & string_Value) vs. IsNumeric(str_Value)

Hemimorphic

This is a great solution. I needed to check for a number in a dataset containing both blanks and "N/A"s. Nice job. – Pointsman

Answer 3

I have checked it with an empty cell right now (without involving a PDF file at all) and you are right: IsNumeric returns True for empty cells.

I haven't ever had this problem because, when coding, I intend to not bring the in-built functions "to its limits" (determining whether an empty cell can be considered as numeric or not might be even discussion-worthy). What I do always before performing any kind of analysis on a cell (or a string in general) is making sure that it is not empty:

Dim valIsNumeric As Boolean
If (Not IsEmpty(Range("A1"))) Then
    valIsNumeric = IsNumeric(Range("A1"))
End If

Or in a more generic version (highly reliable with any kind of string under any circumstance):

If (Len(Trim(Range("A1").Value))) Then
    valIsNumeric = IsNumeric(Range("A1"))
End If

Making sure that the given cell/string is not blank represents just a small bit of code and increases appreciably the reliability of any approach.

Motch

Thanks a lot 'IsEmpty' also returns false, but the len(trim()) seems to be working fine. I'll still have to tested but it looks like it's solved – Deluca

@Deluca this is pretty weird as far as I haven't ever noticed a problem with IsEmpty when dealing with cells (I understand that you are using it with an Excel cell). In any case, you can always use the "Len(Trim(" version for strings (the ones you are taking from PDF, for example). – Motch