split a table into 2 halves vba

rakesh seebaruth

Active Member
Joined
Oct 6, 2011
Messages
303
Hi Guys

I have in cells A1 19,200, B1 13/05/2020 and cells C1 72. When i execute the VBA a table is created in Word as per below and it continues to 72

Instal NoAmt(Rs)Due DateInstal NoAmt(Rs)Due Date
11920013/05/2020
21920013/06/2020
31920013/07/2020
41920013/08/2020
51920013/09/2020
61920013/10/2020
71920013/11/2020
81920013/12/2020
91920013/01/2021
101920013/02/2021
111920013/03/2021
121920013/04/2021
131920013/05/2021
141920013/06/2021
151920013/07/2021
161920013/08/2021
171920013/09/2021
181920013/10/2021
191920013/11/2021
201920013/12/2021

Please note that C1 is the number of months(i,e Instal No).

What i want to achieve is to send the other half to the right of the blank of the table.Let me clarify if C1= 72 months then split it half that is send 36 months to the other side of the table.My number of months are even numbers(24,36,48,60,98)

You will notice that i have added 1 to "lngRows = Range("C1").Value + 1" because of the headings

My vba codes are as follows :-

VBA Code:
Sub CreateTableInWord()
Dim objWord As Object
Dim objDoc As Object
Dim objTbl As Object
Dim objRow As Object
Dim objCol As Object
Dim lngCols As Long
Dim lngRows As Long
Dim I As Long

    lngCols = 6
    lngRows = Range("C1").Value + 1

    Set objWord = CreateObject("Word.Application")

    objWord.Visible = True

    Set objDoc = objWord.Documents.Add(DocumentType:=0)

    Set objTbl = objDoc.Tables.Add(Range:=objDoc.Paragraphs(1).Range, NumRows:=lngRows, NumColumns:=lngCols)

    Set objRow = objTbl.Rows(1)


   objTbl.Cell(1, 1).Range.Text = "Instal No"
   objTbl.Cell(1, 1).Range.Bold = True
   objTbl.Cell(1, 2).Range.Text = "Amt(Rs)"
   objTbl.Cell(1, 2).Range.Bold = True
   objTbl.Cell(1, 3).Range.Text = "Due Date"
   objTbl.Cell(2, 3) = Range("B1").Value
   objTbl.Cell(1, 3).Range.Bold = True
   objTbl.Cell(1, 4).Range.Text = "Instal No"
   objTbl.Cell(1, 4).Range.Bold = True
   objTbl.Cell(1, 5).Range.Text = "Amt(Rs)"
   objTbl.Cell(1, 5).Range.Bold = True
   objTbl.Cell(1, 6).Range.Text = "Due Date"
   objTbl.Cell(1, 6).Range.Bold = True
   objTbl.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    For I = 2 To lngRows

    ' For j = 1 To intNoOfColumns

  objTbl.Cell(I, 1).Range = I - 1

     Next
     
   For S = 2 To lngRows
   
  objTbl.Cell(S, 2) = Range("A1").Value
   
    Next

For T = 3 To lngRows

objTbl.Cell(T, 3).Range.Text = Format(DateAdd("m", T - 2, Range("B1").Value), "dd/mm/yyyy")
Next T




    Set objCol = Nothing
    
    Set objRow = Nothing
    
    Set objDoc = Nothing
    
    Set objWord = Nothing
    
End Sub

Thanks your help

Regards

rakesh
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top