Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty range?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty range?
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. -- Cheers, Ryan "sali" wrote: 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty range?
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).C ount 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. -- Rick (MVP - Excel) "sali" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty range?
"Ryan H" je napisao u poruci interesnoj
... 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 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 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] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty range?
"Rick Rothstein" je napisao u poruci
interesnoj ... 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).C ount 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty range?
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 "sali" wrote: "Ryan H" je napisao u poruci interesnoj ... 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 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 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] . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty range?
"Ryan H" je napisao u poruci interesnoj
... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
empty range?
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 On Tue, 15 Dec 2009 12:34:01 -0800, Ryan H wrote: You just click yes in the Was this post helpful to you? section below in the forum. You don't have it there? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy range from Sheet1 into empty range in Sheet2 | Excel Programming | |||
Finding next empty empty cell in a range of columns | Excel Programming | |||
Range empty? | Excel Programming | |||
why is range empty? | Excel Discussion (Misc queries) | |||
why is range empty? | Excel Programming |