PDA

View Full Version : Count of cells using VBA



karira
06-13-2013, 10:32 AM
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
06-13-2013, 12:11 PM
Here are the first two parts:
For USED:
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

For FORMULAS:
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

karira
06-13-2013, 02:14 PM
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
06-13-2013, 02:18 PM
Hi gk:

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

mancubus
06-13-2013, 03:25 PM
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
06-13-2013, 05:31 PM
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
06-13-2013, 05:38 PM
Thanks!

snb
06-14-2013, 12:59 AM
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

karira
06-14-2013, 01:03 PM
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
06-14-2013, 02:13 PM
No it has nothing to do with usedrange. Dive into the VBEditor's helpfiles; lemma specialcells.