Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-30-2012, 05:33 AM
macropod's Avatar
macropod macropod is offline Identifying and counting words Windows 7 64bit Identifying and counting words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,513
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 Monique,



Try the following macro. It adds two tables after your data, the first containing the country stats, the second containing the city stats:
Code:
Sub Tabulate()
Application.ScreenUpdating = False
Dim Tbl As Table, StrCountries As String, RngSrc As Range, RngDest As Range
Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = " ("
    .Replacement.Text = "^t("
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchKashida = False
    .MatchDiacritics = False
    .MatchAlefHamza = False
    .MatchControl = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
  End With
  Set Tbl = .ConvertToTable(Separator:=wdSeparateByTabs, _
    NumColumns:=2, AutoFitBehavior:=wdAutoFitFixed)
  With Tbl
    j = .Rows.Count: m = 0
    For i = 1 To j
      Set RngSrc = .Rows(i).Cells(2).Range
      RngSrc.MoveEnd wdCharacter, -1
      If InStr(StrCountries, RngSrc.Text) = 0 Then
        StrCountries = StrCountries & "|" & RngSrc.Text
        .Rows.Add
        k = k + 1
        .Rows(j + k).Cells(1).Range.Text = RngSrc.Text
        .Rows(j + k).Cells(2).Range.Text = "1"
      Else
        l = UBound(Split(Split(StrCountries, RngSrc.Text)(0), "|")) + m
        Set RngDest = .Rows(j + l).Cells(2).Range
        RngDest.MoveEnd wdCharacter, -1
        RngDest.Text = CStr(CLng(RngDest.Text) + 1)
      End If
      .Rows(i).Cells.Merge
    Next
    StrCountries = "": m = k: n = k
    For i = 1 To j
      Set RngSrc = .Rows(i).Cells(1).Range
      RngSrc.MoveEnd wdCharacter, -1
      RngSrc.Text = Replace(RngSrc.Text, vbCr, " ")
      If InStr(StrCountries, RngSrc.Text) = 0 Then
        StrCountries = StrCountries & "|" & RngSrc.Text
        .Rows.Add
        k = k + 1
        .Rows(j + k).Cells(1).Range.Text = RngSrc.Text
        .Rows(j + k).Cells(2).Range.Text = "1"
      Else
        l = UBound(Split(Split(StrCountries, RngSrc.Text)(0), "|")) + m
        Set RngDest = .Rows(j + l).Cells(2).Range
        RngDest.MoveEnd wdCharacter, -1
        RngDest.Text = CStr(CLng(RngDest.Text) + 1)
      End If
    Next
    .Split j + 1
    .ConvertToText Separator:=vbCr
  End With
  .Tables(1).Split n + 1
End With
Set RngSrc = Nothing: Set RngDest = Nothing
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Why Words doesn’t show the style of the selected words automatically???? Jamal NUMAN Word 0 04-14-2011 03:20 PM
Identifying and counting words Counting Colors g48dd Excel 2 03-13-2011 09:28 PM
Identifying and counting words listing and counting words gencoglux Word 2 05-07-2010 05:10 PM
Counting Legal Words bulletrick Word 0 12-30-2005 03:22 AM
Identifying the Dynamic Connector cyberbhai Misc 0 12-14-2005 03:55 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:03 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