Morning,
I have read the article and sorry but non the wiser. Looking at the replace function it liks I can pass down a parameter. With VB Script the parameter was pass down and all I had to do was the following:
If Ptr1 > 0 then
NoRows = FldNo * Ptr1
RowPtr = 2
Ptr1 = Ptr1 - 1
NoSubs = FldNo - 1
Ptr2 = 0
Set oArrayList = CreateObject("System.Collections.ArrayList" )
For i = 0 to Ptr1
sDate = Table2(i,0)
sDay = Left(sDate,2)
sMonth = Mid(sDate,4,2)
sYear = Mid(sDate,7)
sDate = sYear & sMonth & sDay
oArrayList.Add sDate
Next
oArrayList.Sort
' oArrayList.Reverse()
Doc.Selection.Find.ClearFormatting
With Doc.Selection.Find
.Text = "/table/"
.Replacement.Text = ""
.Forward = True
.Wrap = 1
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
Doc.Selection.Find.Execute
DummyVar = Doc.Selection.ConvertToTable(0,1,5,0)
With Doc.Selection.Tables(1)
If .Style <> "Table Grid" Then
.Style = "Table Grid"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = True
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = True
End With
Set objTable = theWordDocument.Tables(3)
objTable.Cell(1, 1).Range.Text = "Date of Change"
objTable.Cell(1, 2).Range.Text = "Sub-account" & Chr(13) & Chr(10) & "Number"
objTable.Cell(1, 3).Range.Text = "Interest Rate"
objTable.Cell(1, 4).Range.Text = "Fixed or variable"
objTable.Cell(1, 5).Range.Text = "Sub Account" & Chr(13) & Chr(10) & "Monthly" & Chr(13) & Chr(10) & "Payment"
Doc.Selection.SelectRow
Doc.Selection.Font.Bold = 9999998
'
' Start to merge data
'
For Each strItem in oArrayList
If StoredDate <> strItem then
StoredDate = strItem
for i = 0 to Ptr1
sDate = Table2(i,0)
sDay = Left(sDate,2)
sMonth = Mid(sDate,4,2)
sYear = Mid(sDate,7)
sDate = sYear & sMonth & sDay
If strItem = sDate then
For ii = 0 to NoSubs
If Table2(i,4) = Table1(ii,2) then
Table1(ii,0) = Table2(i,3)
Table1(ii,1) = Table2(i,1)
Table1(ii,3) = Table2(i,2)
End if
Next
End if
Next
TotalCMS = 0
For iii = 0 to NoSubs
TotalCMS = TotalCMS + Table1(iii,0)
SubAccNo = Table1(iii,2)
IntRate = Table1(iii,1)
ProductType = Table1(iii,3)
CMS = Table1(iii,0)
FldNo = Instr(CMS,".")
If FldNo > 0 then
FldNo2 = Len(CMS) - FldNo
If FldNo2 > 1 then
' Do nothing
else
CMS = CMS & "0"
end if
else
CMS = CMS & ".00"
end if
FldNo = Instr(TotalCMS,".")
If FldNo > 0 then
FldNo2 = Len(TotalCMS) - FldNo
If FldNo2 > 1 then
' Do nothing
else
TotalCMS = TotalCMS & "0"
end if
else
TotalCMS = TotalCMS & ".00"
end if
FldNo = Instr(IntRate,".")
FldNo2 = Len(IntRate) - FldNo
If FldNo2 > 1 then
IntRate = IntRate & "%"
else
IntRate = IntRate & "0" & "%"
end if
If Len(SubAccNO) = 1 then
SubAccNo = "0" & SubAccNo
End If
CMS = chr(163) & CMS
'
' Process product details
'
Ptr3 = Instr(ProductType,"/")
If Ptr3 > 0 then
Ptr3 = Ptr3 - 2
PYear = Mid(ProductType,Ptr3,5)
PYear = Right(PYear,2) & Left(PYear,2)
Else
PYear = CYear
End if
Ptr3 = Instr(ProductType,"EX FIX")
If Ptr3 > 0 then
ProductType = "VARIABLE"
else
Ptr3 = Instr(ProductType,"FIX")
If Ptr3 > 0 then
ProductType = "FIXED "
Else
Ptr4 = 0
Ptr3 = Instr(ProductType,"STAFF")
Ptr4 = Ptr4 + Ptr3
Ptr3 = Instr(ProductType,"HELD OVER")
Ptr4 = Ptr4 + Ptr3
Ptr3 = Instr(ProductType,"HIP 0%")
Ptr4 = Ptr4 + Ptr3
If Ptr4 > 0 then
ProductType = "FIXED"
else
ProductType = "VARIABLE"
End if
End if
If SubAccNo = "98" then
ProductType = "FIXED"
IntRate = "0.00%"
else
Ptr3 = Instr(ProductType,"TRK")
Ptr4 = Instr(ProductType,"TRACKER")
If Ptr3 > 0 or Ptr4 > 0 then
ProductType = "VARIABLE"
else
PYear = PYear + 0
If PYear < CYear then
ProductType = "VARIABLE"
If ERC_AMOUNT > 0 then
ProductType = "FIXED"
End If
End if
End if
End If
End if
TotalCMS = chr(163) & TotalCMS
If iii = 0 then
Doc.Selection.InsertRowsBelow(1)
sDate = strItem
sYear = Left(sDate,4)
sMonth = Mid(sDate,5,2)
sDay = Mid(sDate,7)
sDate = sDay & "/" & sMonth & "/" & sYear
objTable.Cell(RowPtr, 1).Range.Text = sDate
objTable.Cell(RowPtr, 2).Range.Text = SubAccno
objTable.Cell(RowPtr, 3).Range.Text = IntRate
objTable.Cell(RowPtr, 4).Range.Text = ProductType
objTable.Cell(RowPtr, 5).Range.Text = CMS
Doc.Selection.SelectRow
Doc.Selection.Font.Bold = 9999998
else
Doc.Selection.InsertRowsBelow(1)
objTable.Cell(RowPtr, 2).Range.Text = SubAccno
objTable.Cell(RowPtr, 3).Range.Text = IntRate
objTable.Cell(RowPtr, 4).Range.Text = ProductType
objTable.Cell(RowPtr, 5).Range.Text = CMS
End if
RowPtr = RowPtr + 1
Next
Doc.Selection.InsertRowsBelow(1)
objTable.Cell(RowPtr, 4).Range.Text = "Total Monthly Payment"
objTable.Cell(RowPtr, 5).Range.Text = TotalCMS
Doc.Selection.SelectRow
Doc.Selection.Font.Bold = 9999998
RowPtr = RowPtr + 1
End if
Next
Set oArrayList = Nothing
End if
Is there some way I can do the same please.