Isblank Function not working in VBA

[ExcelMonkey]

I have a For Each loop that loops thru cells in a
worksheet. I am trying use the isblank function from
Excel on it. Howevere I am getting an Run time error 438
saying Object does not support this property or method.
Why is this?

If UserForm1.IgnoreBlanksBttn = True And _
Application.WorksheetFunction.Isblank
(cell) = True Then 'New

 

[Guest]

Hello,

IsBlank() is not included in the Worksheet function class. How about testing
the length of the cell's value, e.g.,

If Not Len(ActiveCell.Value) Then _
MsgBox "0-Length Value or Empty"

Regards,
Nate Oliver

 

[Guest]

Better safe than sorry, use the Type Conversion Function, CBool(), to
generate a Boolean.

E.g.,

If Not CBool(Len(ActiveCell.Value)) Then _
MsgBox "0-Length Value or Empty"

Regards,
Nate Oliver

 

[Bernie Deitrick]

Use

IsEmpty(Cell)

HTH,
Bernie
MS Excel MVP

 

[ExcelMoneky]

So here is my dilemma. I have errors generated in the
spreadsheet. This is fine becuase I want to deal with
them in the Else part of the If stmt. However, they are
getting caught up in the Not CBool(Len(cell.Value)) part
of the code. If I put an On Error Resume Next before the
If stmt, then my cells with errors do not get dealt with
properly in the later part of the If.

How do I test for blank cells in the first part of the If
without letting my code crash on errors which I plan to
deal with after the Else in my If?


For Each cell In sh.UsedRange
If UserForm1.IgnoreBlanksBttn = True And _
Not CBool(Len(cell.Value)) Then
'do nothing and let loop advance
Else
Other Code
End If
Next

 

[ExcelMonkey]

Thanks bernie, that solved everything

-----Original Message-----
Use

IsEmpty(Cell)

HTH,
Bernie
MS Excel MVP





.

 

[Myrna Larson]

Your post reminds me of something that I discovered this weekend that I find
curious.

If you have a cell formatted as general, or with a number format, say A10, and
in VBA you write

Range("A10").Value = ""

that's the same as

Range("A10").ClearContents

i.e. the cell ends up blank.

BUT.... if the cell is already formatted as text,

Range("A10").Value = ""

gives you a cell containing a zero length string, which is NOT blank. To clear
it, you must write

Range("A10").ClearContents

Example: you have numbers in A1:A20, and set the value of A10 to "". From A1,
End,Down takes you to A9, End,Down again to A11, etc. But if A10 is formatted
as text and you set A10 = "", then from A1, End,Down takes you to A20.

I guess it's logical, but it surprised me.

Another item: if a CSV file contains this text =10-3 and you import the file,
Excel carries out the arithmetic and the cell value is 7, not the text =10-3

 

[Richard]

Your post reminds me of something that I discovered this weekend that I find
curious.
[snipped]


Another item: if a CSV file contains this text =10-3 and you import the file,
Excel carries out the arithmetic and the cell value is 7, not the

text =10-3

Myrna,

I've also stumbled across something similar to that last point before.

I have some macros which handle Print (.prn) files. Essentially they
import a file and parse it with the fixed length option. The macro then
strips out all the non essential stuff like banner headlines,
superfluous total rows and other textual stuff so that I end up with a
nice neat database which I can then procede to handle with Excel's
tools and formulae.

One of the problems I've encountered is where for instance a row
contains say a Supplier Name with a hyphen '-' within it, and the
parsing just happens to break the name before the hyphen. e.g a name
like "AB Smith - Trading as XYZ" breaks into two columns "AB Smith "
and "-trading as XYZ".

Part of my macro examines all the cells in the second column, which for
the bulk of the report are number values, and tests that they are
values. However when it encounters the example above, it thinks the
cell is a number, because it starts with a minus '-' and VBA falls over
since it can't handle it. I've had to devise some more complicated code
which references other columns of the .prn file in order to get round
this.

What I'd really like is a neater elegant solution in VBA that could
handle this condition.

Any ideas / comments - anyone??

Kind regards and usual TIA

Richard Buttrey

 

[Richard]

Your post reminds me of something that I discovered this weekend that I find
curious.
[snipped]


Another item: if a CSV file contains this text =10-3 and you import the file,
Excel carries out the arithmetic and the cell value is 7, not the

text =10-3

Myrna,

I've also stumbled across something similar to that last point before.

I have some macros which handle Print (.prn) files. Essentially they
import a file and parse it with the fixed length option. The macro then
strips out all the non essential stuff like banner headlines,
superfluous total rows and other textual stuff so that I end up with a
nice neat database which I can then procede to handle with Excel's
tools and formulae.

One of the problems I've encountered is where for instance a row
contains say a Supplier Name with a hyphen '-' within it, and the
parsing just happens to break the name before the hyphen. e.g a name
like "AB Smith - Trading as XYZ" breaks into two columns "AB Smith "
and "-trading as XYZ".

Part of my macro examines all the cells in the second column, which for
the bulk of the report are number values, and tests that they are
values. However when it encounters the example above, it thinks the
cell is a number, because it starts with a minus '-' and VBA falls over
since it can't handle it. I've had to devise some more complicated code
which references other columns of the .prn file in order to get round
this.

What I'd really like is a neater elegant solution in VBA that could
handle this condition.

Any ideas / comments - anyone??

Kind regards and usual TIA

Richard Buttrey