Count of cells using VBA

[karira]

Dear friends,

I did a search on this topic within the forum but was not able to find anything that addressed my specific query.

Background</SPAN> - I have a spreadsheet with 60 tabs, multiple cells with formulas, multiple cells that act as inputs (values, text).

Question</SPAN> - I need to figure the following for the entire workbook:
1. Count of cells being used within the entire workbook
2. Count of cells that have formulas in them
3. Count of Cells that act as inputs (values/text) and have formulas dependent on them
4. Count of Cells that act as inputs (values/text) and but have NO formulas dependent on them

Can someone suggest some kind of a macro to address this.

Any help is much appreciated.

Cheers, gk</SPAN>

[GarysStudent]

Here are the first two parts:
For USED:
[VBA]Sub UseCounter()
Dim s As Worksheet, r As Range
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Dim Kount As Long
Kount = 0
For Each s In Sheets
s.Select
Kount = Kount + wf.CountA(ActiveSheet.UsedRange)
Next s
MsgBox Kount
End Sub
[/VBA]
For FORMULAS:
[VBA]Sub FunctionCounter()
Dim s As Worksheet, r As Range
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Dim Kount As Long
Kount = 0
For Each s In Sheets
s.Select
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
Kount = Kount + 1
End If
Next r
Next s
MsgBox Kount
End Sub
[/VBA]

[karira]

Hi GarysStudent, thank you so much for taking the time out to look at my request. I will just test this out and post the results.

Any luck with bullet # 3 and 4?

Best, gk

[GarysStudent]

Hi gk:

I don't know how to approach items 3 & 4

[mancubus]

i dont know either.

but post 18 here may give you an idea:
http://www.xtremevbtalk.com/showthread.php?p=574302


ps: membership is required to download attachments.

[p45cal]

I don't know how to approach items 3 & 4

GarysStudent, It's past my bedtime now but you may be able to dream up something along the lines of your HasFormula solution using range.dependents which returns a range and if that range is nothing then that cell has no dependents.
There are also DirectPrecedents, DirectDependents, Dependents and Precedents properties to check out.

[GarysStudent]

Thanks!

[snb]

[vba]
Sub M_snb()
for each sh in sheets
c00=c00 +sh.usedrange.cells.count
c01=c01+sh.usedrange.specialcells(-4123)

for each it in sh.usedrange.specialcells(-4123)
for each it2 in it.precedents
if instr(c03 & "|","|" & it2.address & "|")=0 then c03= c03 & "|" & it2.address
next
next
next
msgbox "number of usedcells " & c00 &vblf & "number of formulacells " & c01 & vblf & "number of prcedents " & ubound(split(c03,"|"))

End Sub
[/vba]

[karira]

Hi snb,

Thanks for your response. The code does work but not all files. On some files I get error with the following statement
c01=c01+sh.usedrange.specialcells(-4123)

Based on my reserach, it seems like usedrange can lead to some errors at times.

Any help will be greatly appreciated.

[snb]

No it has nothing to do with usedrange. Dive into the VBEditor's helpfiles; lemma specialcells.