Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-05-2020, 11:17 PM
petitfleur petitfleur is offline Count occurences of 2 words (that are near each other) Windows 10 Count occurences of 2 words (that are near each other) Office 2016
Novice
Count occurences of 2 words (that are near each other)
 
Join Date: Feb 2020
Posts: 3
petitfleur is on a distinguished road
Default Count occurences of 2 words (that are near each other)

Hi all, I'm very new to VBA and to this forum. I'm wondering if there is a way to count the number of times that 2 specific words appear? As long as they are near each other, e.g. appearing within 10 characters of each other. Would appreciate any form of help or enlightenment for this. Thank you!
Reply With Quote
  #2  
Old 02-06-2020, 12:23 AM
macropod's Avatar
macropod macropod is offline Count occurences of 2 words (that are near each other) Windows 7 64bit Count occurences of 2 words (that are near each other) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could use a macro like:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "<word1>?{1,10}<word2>"
    .Replacement.Text = ""
    .Forward = True
    .Format = False
    .Wrap = wdFindStop
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found
    i = i + 1
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
MsgBox i & " instances found."
End Sub
where 'word1' & 'word2' are the two words and the '1' & '10' in ?{1,10} represents the minimum and maximum intervening characters, respectively. Note that the test is case-sensitive, though there are ways around that and, as coded, only whole words are found, though there are ways around that, too.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-06-2020, 01:08 AM
petitfleur petitfleur is offline Count occurences of 2 words (that are near each other) Windows 10 Count occurences of 2 words (that are near each other) Office 2016
Novice
Count occurences of 2 words (that are near each other)
 
Join Date: Feb 2020
Posts: 3
petitfleur is on a distinguished road
Default

Thank you so much Paul. May I know if this works regardless of the order the words appear? i.e. does it count the instances that word 2 appear before word 1 as well?
Reply With Quote
  #4  
Old 02-06-2020, 05:03 AM
macropod's Avatar
macropod macropod is offline Count occurences of 2 words (that are near each other) Windows 7 64bit Count occurences of 2 words (that are near each other) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi petitfleur,

The code only finds the words in the order they're input.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-06-2020, 05:32 AM
Guessed's Avatar
Guessed Guessed is offline Count occurences of 2 words (that are near each other) Windows 10 Count occurences of 2 words (that are near each other) Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

To resolve the order issue and allow the proximity to be measured in words rather characters the code needs to be hit the first word and then search backwards and forwards for the partner word.
Code:
Sub CountProx()
  Dim i As Integer, s1 As String, s2 As String, iWd As Long, aRng As Range, iProx As Integer
  Dim iWds As Long, iDocWordCount As Long, iStart As Long, iEnd As Long
  
  s1 = "word"
  s2 = "flash"
  iProx = 10
  
  Set aRng = ActiveDocument.Range
  iDocWordCount = ActiveDocument.Range.Words.Count
  
  With aRng.Find
    .ClearFormatting
    .Text = s1
    .Forward = True
    .MatchCase = False
    .MatchWholeWord = True
    .Wrap = wdFindStop
    .MatchWildcards = False
    .Execute
    Do While .Found
      iWd = ActiveDocument.Range(0, aRng.Start).Words.Count
      iStart = iWd - iProx
      If iStart < 1 Then iStart = 1
      iEnd = iWd + iProx
      If iEnd > iDocWordCount Then iEnd = iDocWordCount
      For i = iStart To iEnd
        If LCase(Trim(ActiveDocument.Words(i).Text)) = s2 Then
          iWds = iWds + 1
        End If
      Next i
      aRng.Collapse wdCollapseEnd
      .Execute
    Loop
  End With
  MsgBox "Pairs in Proximity: " & iWds

End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 02-06-2020, 05:58 PM
macropod's Avatar
macropod macropod is offline Count occurences of 2 words (that are near each other) Windows 7 64bit Count occurences of 2 words (that are near each other) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Another way of automatically swapping the word order, sticking to the 'within characters' approach:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, j As Long, StrFnd As String
StrFnd = InputBox("Please input the Find paramenters, as:" & vbCr & _
  "word1|word2|x,y" & vbCr & _
  "where:" & vbCr & _
  "'x' is the minimum intervening character count and" & vbCr & _
  "'y' is the maximum intervening character count.")
For i = 1 To 2
  With ActiveDocument.Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Text = "<" & Split(StrFnd, "|")(1 - i Mod 2) & ">?{" & Split(StrFnd, "|")(2) & "}<" & Split(StrFnd, "|")(2 - i) & ">"
      .Replacement.Text = ""
      .Forward = True
      .Format = False
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Execute
    End With
    Do While .Find.Found
      j = j + 1
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
Next
Application.ScreenUpdating = True
MsgBox j & " instances found."
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 02-12-2020, 10:40 PM
petitfleur petitfleur is offline Count occurences of 2 words (that are near each other) Windows 10 Count occurences of 2 words (that are near each other) Office 2016
Novice
Count occurences of 2 words (that are near each other)
 
Join Date: Feb 2020
Posts: 3
petitfleur is on a distinguished road
Default

This is really helpful. Thank you so much. However I got the following message when I run the code: "Runtime error 6". I'm not sure what I did wrongly.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count occurences of 2 words (that are near each other) How to count the words in black miguelholandes Word VBA 6 03-20-2017 03:04 PM
Count occurences of 2 words (that are near each other) Count words between quotation marks sojiro Word VBA 11 01-29-2017 12:59 PM
Count occurences of 2 words (that are near each other) Count number of words in closed docx files Pecoflyer Word VBA 2 11-25-2016 01:07 AM
Count occurences of 2 words (that are near each other) How can I count multiple usage of the same words? coffee_king Word VBA 1 03-24-2012 07:52 PM
Count occurences of 2 words (that are near each other) Creating Text to count words WITHOUT title page ingmar.s Word 3 10-08-2009 10:23 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:38 AM.


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