Microsoft Office Forums How to find number of coma and then add that number of rows in word using macro?

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-04-2014, 02:22 AM
PRA007's Avatar
PRA007 PRA007 is offline How to find number of coma and then add that number of rows in word using macro? Windows 7 32bit How to find number of coma and then add that number of rows in word using macro? Office 2010 32bit
Competent Performer
How to find number of coma and then add that number of rows in word using macro?
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default How to find number of coma and then add that number of rows in word using macro?

I have this table





What I have done is recorde macro and it look like this.


Selection.MoveRight Unit:=wdCell
Selection.EndKey Unit:=wdLine
Selection.TypeText Text:="@"
Selection.MoveRight Unit:=wdCell
Selection.Copy
Selection.MoveLeft Unit:=wdCell
Selection.ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=1, _


NumRows:=9, AutoFitBehavior:=wdAutoFitFixed
With Selection.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
End With
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Cut
Selection.MoveLeft Unit:=wdCell
Selection.EndKey Unit:=wdLine
Selection.TypeParagraph
Selection.PasteAndFormat (wdFormatPlainText)
Selection.TypeBackspace
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.InsertRowsBelow 1
Selection.MoveUp Unit:=wdLine, Count:=29
Selection.PasteAndFormat (wdTableOverwriteCells)
Selection.MoveRight Unit:=wdCell
Selection.MoveDown Unit:=wdLine, Count:=29
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell


My question is is there a short method to count the comma separated number of entries and then add that number of new row and then paste the data accordingly.
what I currently do is add some 3O row and then paste the data using paste special merge the entry and then removing the empty row using macro.
Reply With Quote
  #2  
Old 12-04-2014, 07:54 AM
gmaxey gmaxey is offline How to find number of coma and then add that number of rows in word using macro? Windows 7 32bit How to find number of coma and then add that number of rows in word using macro? Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 967
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

Code:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim lngStart As Long
Dim lngIndex As Long
Dim lngCount As Long
Dim arrList() As String
Dim oTbl As Table
Dim oRow As Row, oNewRow As Row
  lngStart = 1
  Set oTbl = ActiveDocument.Tables(1)
  Do
    On Error GoTo lbl_Exit
    Set oRow = oTbl.Rows(lngStart)
    lngStart = lngStart + 1
    oRow.Select
    'Your text in column 2 is a comma separtated list. You can create an array from it
    arrList = Split(Left(oRow.Range.Cells(2).Range.Text, _
                    Len(oRow.Range.Cells(2).Range.Text) - 2), ",")
    lngCount = UBound(arrList) + 1
    For lngIndex = 1 To lngCount
      lngStart = lngStart + 1
      If lngIndex <> lngCount Then
        oRow.Cells(1).Range.InsertAfter arrList(lngIndex - 1) & vbCr
      Else
        oRow.Cells(1).Range.InsertAfter arrList(lngIndex - 1)
      End If
      If lngIndex = 1 Then
        Set oNewRow = oTbl.Rows.Add(oRow.Next)
      Else
        Set oNewRow = oTbl.Rows.Add(oNewRow.Next)
      End If
  
      oNewRow.Cells(1).Range.Text = arrList(lngIndex - 1)
    Next lngIndex
  Loop
lbl_Exit:
  Exit Sub
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 12-07-2014, 10:38 PM
PRA007's Avatar
PRA007 PRA007 is offline How to find number of coma and then add that number of rows in word using macro? Windows 7 32bit How to find number of coma and then add that number of rows in word using macro? Office 2010 32bit
Competent Performer
How to find number of coma and then add that number of rows in word using macro?
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default

I Have searched for days and night to find answer to this question I Gave up. Then One day while searching on internet I found the forum. Earlier I was not sure that I will get the answer. But no matter who I am and where I am from, I am getting Answers. Not I am at some position that I can answer or at least direct people at right place. Thanks to people like you Mr. Maxey who give the answers of silly questions by People Like Me.
Thanks Mr. Maxey
Reply With Quote
  #4  
Old 03-18-2015, 09:28 PM
PRA007's Avatar
PRA007 PRA007 is offline How to find number of coma and then add that number of rows in word using macro? Windows 7 32bit How to find number of coma and then add that number of rows in word using macro? Office 2010 32bit
Competent Performer
How to find number of coma and then add that number of rows in word using macro?
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default Editing The Macro.

Quote:
Originally Posted by gmaxey View Post
Code:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim lngStart As Long
Dim lngIndex As Long
Dim lngCount As Long
Dim arrList() As String
Dim oTbl As Table
Dim oRow As Row, oNewRow As Row
  lngStart = 1
  Set oTbl = ActiveDocument.Tables(1)
  Do
    On Error GoTo lbl_Exit
    Set oRow = oTbl.Rows(lngStart)
    lngStart = lngStart + 1
    oRow.Select
    'Your text in column 2 is a comma separtated list. You can create an array from it
    arrList = Split(Left(oRow.Range.Cells(2).Range.Text, _
                    Len(oRow.Range.Cells(2).Range.Text) - 2), ",")
    lngCount = UBound(arrList) + 1
    For lngIndex = 1 To lngCount
      lngStart = lngStart + 1
      If lngIndex <> lngCount Then
        oRow.Cells(1).Range.InsertAfter arrList(lngIndex - 1) & vbCr
      Else
        oRow.Cells(1).Range.InsertAfter arrList(lngIndex - 1)
      End If
      If lngIndex = 1 Then
        Set oNewRow = oTbl.Rows.Add(oRow.Next)
      Else
        Set oNewRow = oTbl.Rows.Add(oNewRow.Next)
      End If
  
      oNewRow.Cells(1).Range.Text = arrList(lngIndex - 1)
    Next lngIndex
  Loop
lbl_Exit:
  Exit Sub
End Sub
I found the macro working but I want to edit the macro a little.

I am getting something like this.

Reply With Quote
  #5  
Old 03-19-2015, 06:47 AM
gmaxey gmaxey is offline How to find number of coma and then add that number of rows in word using macro? Windows 7 32bit How to find number of coma and then add that number of rows in word using macro? Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 967
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

Try modifying as shown:

Code:
  oRow.Cells(1).Range.InsertAfter vbCr & arrList(lngIndex - 1)
      Else
        oRow.Cells(1).Range.InsertAfter vbCr & arrList(lngIndex - 1)
      End If
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #6  
Old 05-21-2015, 11:17 PM
PRA007's Avatar
PRA007 PRA007 is offline How to find number of coma and then add that number of rows in word using macro? Windows 7 32bit How to find number of coma and then add that number of rows in word using macro? Office 2010 32bit
Competent Performer
How to find number of coma and then add that number of rows in word using macro?
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default Editing Macro.

Dear Sir,
I wanted to modify macro by editing it to make some correction I neglected while asking thinking that I will figure it out later.

I want to convert table as follow


to




The word file containing both information is as follow.
https://docs.google.com/viewer?a=v&p...I2NTkwMjA0YWE3
Reply With Quote
  #7  
Old 05-25-2015, 04:43 AM
gmaxey gmaxey is offline How to find number of coma and then add that number of rows in word using macro? Windows 7 32bit How to find number of coma and then add that number of rows in word using macro? Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 967
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

Just change the cell referenences:

Code:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim lngStart As Long
Dim lngIndex As Long
Dim lngCount As Long
Dim arrList() As String
Dim oTbl As Table
Dim oRow As Row, oNewRow As Row
  lngStart = 1
  Set oTbl = ActiveDocument.Tables(1)
  Do
    On Error GoTo lbl_Exit
    Set oRow = oTbl.Rows(lngStart)
    lngStart = lngStart + 1
    oRow.Select
    'Your text in column 2 is a comma separtated list. You can create an array from it
    arrList = Split(Left(oRow.Range.Cells(3).Range.Text, _
                    Len(oRow.Range.Cells(3).Range.Text) - 2), ",")
    lngCount = UBound(arrList) + 1
    For lngIndex = 1 To lngCount
      lngStart = lngStart + 1
      If lngIndex <> lngCount Then
        oRow.Cells(2).Range.InsertAfter vbCr & arrList(lngIndex - 1)
        Else
          oRow.Cells(2).Range.InsertAfter vbCr & arrList(lngIndex - 1)
        End If
      If lngIndex = 1 Then
        Set oNewRow = oTbl.Rows.Add(oRow.Next)
      Else
        Set oNewRow = oTbl.Rows.Add(oNewRow.Next)
      End If
      oNewRow.Cells(2).Range.Text = arrList(lngIndex - 1)
    Next lngIndex
  Loop
lbl_Exit:
  Exit Sub
End Sub
See: http://gregmaxey.com/word_tip_pages/...ng_macros.html for instructions to employ the VBA code provided above.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #8  
Old 05-27-2015, 10:45 PM
PRA007's Avatar
PRA007 PRA007 is offline How to find number of coma and then add that number of rows in word using macro? Windows 7 32bit How to find number of coma and then add that number of rows in word using macro? Office 2010 32bit
Competent Performer
How to find number of coma and then add that number of rows in word using macro?
 
Join Date: Dec 2014
Location: Ahmedabad, Gujrat, India
Posts: 145
PRA007 is on a distinguished road
Default How to add one more field into the macro.

Sorry for asking silly questions as I am dummy trying to learn macro.
I want to add one more field, something that i want to copy from the specific source and paste it somewhere else.
Questions are how to identify and index the source to copy.
In my case the source is assignee of patents ie. athena neuroscience that is a paragraph away from lngStart. I want to copy the text and paste it to simultaneously after each cell of arraylist.

I want also to remove paragraph mark from column IV marked blue.
Reply With Quote
Reply

Tags
macro vba word, microsoft word 2010, word table

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find number of coma and then add that number of rows in word using macro? Global macro across a number of different word files daffy Word VBA 6 07-08-2014 05:42 PM
Creating a table for a variable number of rows OllieOnline Mail Merge 1 03-27-2013 02:48 PM
Using macro to add variable number of rows to a protected word table Julia Word Tables 1 01-09-2013 06:04 AM
Moving data from multiple rows to 1 row for each program number ballj_35 Excel 3 08-01-2012 05:10 AM
How to find number of coma and then add that number of rows in word using macro? Fixing number or rows in a table burnsie Word 2 07-12-2011 02:59 AM


All times are GMT -7. The time now is 07:53 AM.


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