Counting numbers in Word VBA

JamesWood · #1 06-01-2021, 07:52 AM

Hi guys

Strange one here, but any idea how to COUNTA in Word VBA, not Excel? Or the equivalent? I'm aware this sub is totally wrong but hopefully it'll get across what I want to do:

Public Function MergeFromToTotal() As String

MergeFromToTotal = CountA(Range(Me.FromThis.Value:Me.ToThis.Value))

End Function

Where Me.FromThis.Value is a textbox that might contain any number, and the same for the ToThis value. If it was say 3-6 then I'd want MergeFromToTotal to return with 4 (e.g. 3, 4, 5, 6).

Hope that makes sense?

Thanks a lot!
James

macropod · #2 06-01-2021, 02:50 PM

Word has no equivalent to Excel's COUNTA function. In any event, Excel's COUNTA worksheet function is not a method that can be used outside the Excel worksheet context. Besides which, Excel's COUNTA function doesn't do anything like what you envisage.

Guessed · #3 06-01-2021, 04:36 PM

I think you need to iterate through the array and test each value

Code:

Sub CountIt()
  Dim arr() As String, i As Integer, iCount As Integer
  arr = Split("1,Dog,2,Apple,3.7,22,Green", ",")
  For i = LBound(arr) To UBound(arr)
    If IsNumeric(arr(i)) Then iCount = iCount + 1
  Next i
  MsgBox "Array contains " & iCount & " numbers", vbOKOnly
End Sub

JamesWood · #4 06-02-2021, 12:41 AM

Hmm okay! This is essentially for a progress bar on a mail merge, so here's my dilemma. If recordNumber = 3 To 7 (potentially, if that's the merge range), how can I get a progress bar to read that? Stuck on this!

Me.ProgressBar1.Value = Round(100 / MergeCountTotal * recordNumber)

the above obviously is actually saying times 3, and the next would be times 4, until it reaches 7... but for the percentage to make sense, it needs to see 3 as actually being 1, of 4... damn this is confusing haha

Guessed · #5 06-02-2021, 05:23 AM

Perhaps you could try

Code:

If iStart = 0 then iStart = recordNumber
Me.ProgressBar1.Value = Round(100*(recordNumber-iStart)/MergeCountTotal)

JamesWood · #6 06-02-2021, 06:14 AM

I've managed to figure it out guys!!

So just before the merge starts, I've got this:

Dim n As Integer
n = 1

Then the merge loop starts:

For recordNumber = MergeStart To MergeFinish

And then just before "Next recordNumber", I now have

'Progress bar based on how many records in merge
If Me.OptionButtonAll.Value = True Or Me.OptionButtonCurrent.Value = True Or Me.OptionButtonFromTo.Value = True Then
Me.ProgressBar1.Value = Round(100 / MergeCountTotal * n)
Me.ProgressPercentage.Caption = Me.ProgressBar1.Value & "%"
End If
n = n + 1

This way it adds +1 to n just before it loops again. It's working perfectly now for calculating the correct percentage based on how many merge records it's processing. Thank f**k. This was driving me wild!

Thanks for your help though guys!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Counting footnotes in Word	Sylvain	Word VBA	0	03-10-2021 06:45 AM
Counting Consecutive Periods In Rows Using Numbers	Yaaseen23	Excel	18	03-24-2017 11:15 AM
counting "x" in a cell and sum the numbers in weekday	rayramirezdvm	Excel	4	04-02-2015 02:58 PM
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook	JaxV	Excel	9	11-14-2014 12:25 AM
Word counting macro for newly added portion?	New Daddy	Word VBA	24	09-30-2013 07:30 PM