Word Equivalent to 'CountIf' 
Author Message
 Word Equivalent to 'CountIf'

Thanks:

I have a Word table which is 15 columns by 30 rows. The first few rows and
columns simply contain text (headings etc). The table is used as a work
diary so the cells represent 30 min work blocks - each 30 mins worked is
indicated by background shading. So far I have written a macro/VBA code (see
below) which will count the number of shaded cells in each column (day). It
is slow in running though, and I also need it to count the number of cells
with tick symbols in (indicating when I am on-call from home and receive a
telephone call from work).

Code so far...
............................................................................
.
Sub Addup()
Dim N As Integer
Dim Count As Integer
Dim M As Variant
Count = 0
For M = 2 To 15
For N = 4 To 27
ActiveDocument.Tables(1).Cell(N, M).Select
If Selection.Cells(1).Shading.BackgroundPatternColor = wdColorGray40 Then
Count = Count + 1
Next N

ActiveDocument.Tables(1).Cell(29, M).Select
Selection.Text = Count / 2
Count = 0
Next M
End Sub
............................................................................
......

Any help is much appreciated.

Phil


Quote:
> Hi Phil,

> No Word doesn't have this function and usermade functions can't be used in
> fields. That's why if you need a solution for this you'll need to do it
all
> (and update it again if necessary) with a macro that loops through all the
> cells in the table, checks their formatting and calculates the total you
> need.

> If this is what you're after but if you need help with coding it, could
you
> please send a reply to posting in the newsgroup and tell us which
> columns/rows etc you need in the calculation etc.

> An other option would be to store an Excel object in your document in
which
> you can use all the built-in Excel functions.

> Hope this helps,
> regards,
> Astrid

> So that all can benefit from the discussion, please post all follow-ups to
> the newsgroup.
> Visit the MVP Word FAQ site at http://www.*-*-*.com/



> > Is there anyway to implement the Excel 'countif' function in Word so
that,
> > for example, I can count the number of cells in a table column where the
> > shading is black?

> > Phil



Tue, 06 Jan 2004 23:13:09 GMT  
 Word Equivalent to 'CountIf'
Hi Phil

You don't need to select each cell in turn, you can refer to it directly. So
these 2 lines

ActiveDocument.Tables(1).Cell(N, M).Select
If Selection.Cells(1).Shading.BackgroundPatternColor = wdColorGray40 Then

can be replaced by this one,

If ActiveDocument.Tables(1).Cell(N, M).Shading.BackgroundPatternColor =
wdColorGray40 Then

(should all be on one line)

You can probably make things go a bit faster still by putting the whole loop
inside a With clause. Reducing the number of "dots" executed by the code
usually speeds things up. This would make your loop look like this (I've
indented the code to make it easier to see where the loops and branches are)

With ActiveDocument.Tables(1)
    For M = 2 To 15
        For N = 4 To 27
            If .Cell(N, M).Shading.BackgroundPatternColor _
                    = wdColorGray40 Then
            Count = Count + 1
        Next N

        .Cell(29, M).Range.Text = Count / 2
        Count = 0
    Next M
End With

For more tips of this kind, you might be interested in taking a look at this
article

The art of defensive programming
http://www.mvps.org/word/FAQs/MacrosVBA/MaintainableCode.htm

--
Regards
Jonathan West - Word MVP
MultiLinker - Automated generation of hyperlinks in Word
Conversion to PDF & HTML
http://www.multilinker.com
Word FAQs at http://www.multilinker.com/wordfaq
Please post any follow-up in the newsgroup. I do not reply to Word questions
by email


Quote:
> Thanks:

> I have a Word table which is 15 columns by 30 rows. The first few rows and
> columns simply contain text (headings etc). The table is used as a work
> diary so the cells represent 30 min work blocks - each 30 mins worked is
> indicated by background shading. So far I have written a macro/VBA code
(see
> below) which will count the number of shaded cells in each column (day).
It
> is slow in running though, and I also need it to count the number of cells
> with tick symbols in (indicating when I am on-call from home and receive a
> telephone call from work).

> Code so far...

............................................................................
Quote:
> .
> Sub Addup()
> Dim N As Integer
> Dim Count As Integer
> Dim M As Variant
> Count = 0
> For M = 2 To 15
> For N = 4 To 27
> ActiveDocument.Tables(1).Cell(N, M).Select
> If Selection.Cells(1).Shading.BackgroundPatternColor = wdColorGray40 Then
> Count = Count + 1
> Next N

> ActiveDocument.Tables(1).Cell(29, M).Select
> Selection.Text = Count / 2
> Count = 0
> Next M
> End Sub

............................................................................

- Show quoted text -

Quote:
> ......

> Any help is much appreciated.

> Phil



> > Hi Phil,

> > No Word doesn't have this function and usermade functions can't be used
in
> > fields. That's why if you need a solution for this you'll need to do it
> all
> > (and update it again if necessary) with a macro that loops through all
the
> > cells in the table, checks their formatting and calculates the total you
> > need.

> > If this is what you're after but if you need help with coding it, could
> you
> > please send a reply to posting in the newsgroup and tell us which
> > columns/rows etc you need in the calculation etc.

> > An other option would be to store an Excel object in your document in
> which
> > you can use all the built-in Excel functions.

> > Hope this helps,
> > regards,
> > Astrid

> > So that all can benefit from the discussion, please post all follow-ups
to
> > the newsgroup.
> > Visit the MVP Word FAQ site at http://www.mvps.org/word/



> > > Is there anyway to implement the Excel 'countif' function in Word so
> that,
> > > for example, I can count the number of cells in a table column where
the
> > > shading is black?

> > > Phil



Wed, 07 Jan 2004 02:35:08 GMT  
 Word Equivalent to 'CountIf'
Hi Rogue

It slows things down *a lot* if you refer to each cell by its coordinates -
you are forcing Word to calculate where the cell physically is, over and
over again. Much faster to use Next or to loop through the Cells.

And no need to select anything.

Try this:

Sub GetCountIf()

Dim oTable As Table, oCol As Column, oCell As Cell, _
ColCounter As Long, RowCounter As Long, NumCols As Long, _
ShadeCounter() As Long, TickCounter() As Long

Set oTable = ActiveDocument.Tables(1)
NumCols = oTable.Columns.Count

ReDim ShadeCounter(2 To NumCols) As Long
ReDim TickCounter(2 To NumCols) As Long

For Each oCell In oTable.Range.Cells
    ColCounter = oCell.ColumnIndex
    RowCounter = oCell.RowIndex

    If RowCounter > 3 And ColCounter > 1 Then

        If oCell.Shading.BackgroundPatternColor = wdColorGray40 Then
            ShadeCounter(ColCounter) = ShadeCounter(ColCounter) + 1
        End If

        If Asc(oCell.Range.Characters(1)) = 40 Then
            'Only way I know to find out if it's a symbol without selecting
            'it. Unfortunateky, doesn't tell you *which* symbol,
            'but you're only using one type of symbol, so that's OK

            TickCounter(ColCounter) = TickCounter(ColCounter) + 1

        End If

    End If

Next oCell

For ColCounter = 2 To NumCols
    MsgBox "Column " & ColCounter & ":" & vbCr & vbCr & _
            ShadeCounter(ColCounter) & " cells shaded" _
            & vbCr & vbCr & TickCounter(ColCounter) & " cells ticked"
Next ColCounter

End Sub

-------------------
Regards

Dave


| Thanks:
|
| I have a Word table which is 15 columns by 30 rows. The first few rows and
| columns simply contain text (headings etc). The table is used as a work
| diary so the cells represent 30 min work blocks - each 30 mins worked is
| indicated by background shading. So far I have written a macro/VBA code
(see
| below) which will count the number of shaded cells in each column (day).
It
| is slow in running though, and I also need it to count the number of cells
| with tick symbols in (indicating when I am on-call from home and receive a
| telephone call from work).
|
| Code so far...
|
............................................................................
| .
| Sub Addup()
| Dim N As Integer
| Dim Count As Integer
| Dim M As Variant
| Count = 0
| For M = 2 To 15
| For N = 4 To 27
| ActiveDocument.Tables(1).Cell(N, M).Select
| If Selection.Cells(1).Shading.BackgroundPatternColor = wdColorGray40 Then
| Count = Count + 1
| Next N
|
| ActiveDocument.Tables(1).Cell(29, M).Select
| Selection.Text = Count / 2
| Count = 0
| Next M
| End Sub
|
............................................................................
| ......
|
| Any help is much appreciated.
|
| Phil
|
|

| > Hi Phil,
| >
| > No Word doesn't have this function and usermade functions can't be used
in
| > fields. That's why if you need a solution for this you'll need to do it
| all
| > (and update it again if necessary) with a macro that loops through all
the
| > cells in the table, checks their formatting and calculates the total you
| > need.
| >
| > If this is what you're after but if you need help with coding it, could
| you
| > please send a reply to posting in the newsgroup and tell us which
| > columns/rows etc you need in the calculation etc.
| >
| > An other option would be to store an Excel object in your document in
| which
| > you can use all the built-in Excel functions.
| >
| > Hope this helps,
| > regards,
| > Astrid
| >
| > So that all can benefit from the discussion, please post all follow-ups
to
| > the newsgroup.
| > Visit the MVP Word FAQ site at http://www.mvps.org/word/
| >


| > > Is there anyway to implement the Excel 'countif' function in Word so
| that,
| > > for example, I can count the number of cells in a table column where
the
| > > shading is black?
| > >
| > > Phil
| > >
| > >
| >
| >
|
|
|



Wed, 07 Jan 2004 21:11:48 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Word equivalent to 'Countif'

2. Outlook equivalent of Word's StatusBar

3. 'through account' VBA equivalent

4. VBA's Equivalent of VBScript's SetLocale

5. VB equivalent of COBOL 'redefines'?

6. Shortcut Keys and 'SOUND' Equivalent

7. 32 bit api equivalent for 'hmemcpy'

8. VBA's Equivalent of VBScript's SetLocale

9. equivalent of 'clustered' (SQL) in ORACLE

10. equivalent of 'this'

11. Word'7 Vs. Word'9

12. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

 

 
Powered by phpBB® Forum Software