IsNumeric function returning true for an empty cell
Solution 1
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)
Solution 2
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.
user2681358
Updated on June 04, 2022Comments
-
user2681358 about 2 years
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
-
user2681358 almost 11 yearsThanks 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
-
varocarbas almost 11 years@user2681358 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).
-
Kyle over 4 yearsThis is a great solution. I needed to check for a number in a dataset containing both blanks and "N/A"s. Nice job.