Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #12  
Old 12-04-2020, 08:44 AM
gmaxey gmaxey is offline Count Unique Instances of Multiple Strings Using Wildcards Windows 10 Count Unique Instances of Multiple Strings Using Wildcards Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,617
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

I can't read French but while I see the methods used by jpl clearly work, here is an alternative using a collection to reject the duplicates. The output file path is defined in the calling macro and created (or recreated) in the called macro (it doesn't have to be preexisting.)


Code:
Sub ScratchMacro()
Dim Prefixes As Variant, Prefix As Variant
Dim oColUnique As New Collection
Dim arrUnique() As String
Dim lngIndex As Long
Dim oRng As Range
Dim strOut As String
  'What do we want to find
  Prefixes = Array("PE-", "JEB-", "HEL-")
  For Each Prefix In Prefixes
    Set oRng = ActiveDocument.Range
    With oRng.Find
      .ClearFormatting
      .MatchWildcards = True
      .Text = Prefix & "[0-9]{1,}"
      .Forward = True
      While .Execute
        On Error Resume Next
        oColUnique.Add Trim(oRng.Text), Trim(oRng.Text)
        If Err.Number = 0 Then
          'Any duplicate will error (i.e., and error number <> 0). This code runs for unique results only.
          ReDim Preserve arrUnique(lngIndex)
          arrUnique(lngIndex) = oRng.Text
          lngIndex = lngIndex + 1
        End If
        oRng.Collapse wdCollapseEnd
      Wend
    End With
  Next Prefix
  'Sort results
  WordBasic.SortArray arrUnique
  'Form a string from the results
  strOut = Join(arrUnique, vbCr) & vbCr & "Total - " & UBound(arrUnique) + 1
  'Msgbox strOut
  WriteToTextFile "D:\Collection Results.txt", strOut
lbl_Exit:
  Exit Sub
End Sub

Sub WriteToTextFile(strPath As String, strContent As String)
Dim oFSO As Object, oFile As Object
Dim lngIndex As Long
  Set oFSO = CreateObject("Scripting.FileSystemObject")
  Set oFile = oFSO.CreateTextFile(strPath)
  oFile.Close
  lngIndex = FreeFile
  Open strPath For Output As #lngIndex
  Print #lngIndex, strContent
  Close #lngIndex
lbl_Exit:
  Set oFSO = Nothing: Set oFile = Nothing
  Exit Sub
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Function To Count All Strings In Range Pluviophile Excel 1 09-21-2020 06:22 PM
Calculate recurring instances between cell count - using trigger - URGENT HELP sfarad Excel Programming 2 07-07-2018 07:59 AM
Count Duplicate Values without a specific Unique Value Brittni Excel 1 02-01-2017 06:22 PM
Count Unique Instances of Multiple Strings Using Wildcards Count unique values that match 2 or more criteria caeiro01 Excel 1 10-25-2015 02:34 AM
Count Unique Instances of Multiple Strings Using Wildcards Display unique values and count the number of child items vthomeschoolmom Excel 2 07-25-2013 06:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft