empty range?

[sali]

just as little surprise, method specialcells(xlCellTypeFormulas) had to
return "empty" range since there were no formulas, but instead of having
..cells.count=0, or to skip loop in "for each", it thrown error "no cells in
the range"

is it expected? i had to handle it with error goto, but would be nice to
handle it with count=0
this is excel 2000 vba

am i doing wrong, or there is realy no other way than throwing error?

thnx

 

[Ryan H]

I'm not sure if you can do it with the Count Function but here are two ways
you can make your code work without an error being thrown and without using
On Error Goto statements.

Option 1:

Sub FindFormulas()

If IsNull(Sheets("Sheet1").Cells.HasFormula) Or
Sheets("Sheet1").Cells.HasFormula Then
MsgBox "Found Formulas"
' or your code here
End If
End Sub
or

Option 2:

Call the function FormulaFinder with your range you want to scan in the "(
)". For example,


Sub YourSub()

' check for formulas
If FormulaFinder(Range("A1:A100")) Then
' put your code here
End If

End Sub

Function FormulaFinder(myRange As Range) As Boolean

Dim rng As Range

For Each rng In myRange
If rng.HasFormula Then
FormulaFinder = True
Exit For
End If
Next rng

End Function


Hope this helps! If so, click "YES" below.

 

[Rick Rothstein]

Yes, it is annoying that it errors out if no cells are found (I think it is
doing this because it is a method and not a property). Anyway, I'm not sure
I know exactly what you are after, but maybe this code snippet will help...

On Error Resume Next
HowManyBlanks = Range("A1:A20").SpecialCells(xlCellTypeFormulas).Count
On Error GoTo 0

If you Dim the variable HowManyBlanks as Long, then it will equal 0 if no
cells are found by the SpecialCells method and it will equal the actual
count otherwise. If you do not Dim the variable, then it will be a Variant
by default and its value would end up being Empty if SpecialCells returned
no cells.

 

[sali]

I'm not sure if you can do it with the Count Function but here are two
ways
you can make your code work without an error being thrown and without
using
On Error Goto statements.

If IsNull(Sheets("Sheet1").Cells.HasFormula) Or
Sheets("Sheet1").Cells.HasFormula Then

thnx, "hasformula" property is new for me, i haven't noticed it befor. it
may help.
before, i used to check existence of cell's formula with "formula" property,
like:

if left(.formula,1)="=" then
....

but, anyway, it is still surprise not having count=0 for empty range

[thnx for the answer, but i have no "yes" to click in my newsreader client]

 

[sali]

Yes, it is annoying that it errors out if no cells are found (I think it
is doing this because it is a method and not a property). Anyway, I'm not
sure I know exactly what you are after, but maybe this code snippet will
help...

On Error Resume Next
HowManyBlanks = Range("A1:A20").SpecialCells(xlCellTypeFormulas).Count
On Error GoTo 0

thnx, it was exactly what i was doing, using func to return either
"populated" or "empty" range, the advantage with func is you don't need to
worry about "reseting" error handler

Function formule1(ws As Worksheet) As Range
On Error Resume Next
Set formule1 = ws.Cells.SpecialCells(xlCellTypeFormulas)
End Function

yes, as you stated, there are workarounds, but was surprised with excel
arhitecture, but it is as is

 

[Ryan H]

You just click yes in the Was this post helpful to you? section below in the
forum. You don't have it there?
--
Cheers,
Ryan

I'm not sure if you can do it with the Count Function but here are two
ways
you can make your code work without an error being thrown and without
using
On Error Goto statements.

If IsNull(Sheets("Sheet1").Cells.HasFormula) Or
Sheets("Sheet1").Cells.HasFormula Then

thnx, "hasformula" property is new for me, i haven't noticed it befor. it
may help.
before, i used to check existence of cell's formula with "formula" property,
like:

if left(.formula,1)="=" then
....

but, anyway, it is still surprise not having count=0 for empty range

[thnx for the answer, but i have no "yes" to click in my newsreader client]


.

 

[sali]

You just click yes in the Was this post helpful to you? section below in
the
forum. You don't have it there?

no, i am using classic win oe news client, it is pure text.
but please, if you can, click it for me

 

[Gord Dibben]

Sali is not posting or reading from discussions.microsoft.com interface.

If you had a real newsreader you could see his header and find that he is
using Outlook Express.

Just as I use Forte Agent and have no "Was this post helpful"


Gord Dibben MS Excel MVP