Sub CollectingRenewedDep() '<<<<< Corrected
Dim i As Byte, j As Byte, y As Byte, X As Byte, Z As Byte, Compteur As Byte, BankNew As Byte, Matching As Byte
Dim TabBankSelected() As String
Dim TabBankOrigine() As String
Dim ColBankUniqueSelected As Collection
Dim ColBankUniqueOrigine As Collection
Dim TabBankUniqueOrigine() As Variant
Dim BankMulti As Byte
Dim Existing As Byte
Dim BankItem As Variant
Dim Bank1 As String, Bank2 As String, Bank3 As String, Bank4 As String
Dim Cell As Range
Dim FirstAddress As String
Dim TheBank As String
Dim TotalTxbMoney() As Double
Dim MoneyToSend As Double
Dim SQLSearch As String
Dim SendingMoneyOut As Boolean
CleaningFaxMat
CleaningFaxNew
With SelectedMatured
For i = 1 To .Range('B255').End(xlUp).Row
If .Cells(i, 11) = '' Then
ReDim Preserve TabBankOrigine(X)
TabBankOrigine(X) = .Cells(i, 2)
X = X + 1
End If
Next
End With
X = 0
For i = 1 To 4
If USFSplitDep.Controls('TxbBank' & i).Value <> '' Then
BankNew = BankNew + 1
End If
Next
For i = 1 To BankNew
If USFSplitDep.Controls('TxbBank' & i).Value <> '' Then
ReDim Preserve TabBankSelected(X)
TabBankSelected(X) = USFSplitDep.Controls('TxbBank' & i).Value
X = X + 1
End If
Next i
Set ColBankUniqueSelected = New Collection
For i = 0 To UBound(TabBankSelected)
On Error Resume Next
ColBankUniqueSelected.Add TabBankSelected(i), TabBankSelected(i)
Next
For Each BankItem In ColBankUniqueSelected
BankMulti = BankMulti + 1
Compteur = 0
For i = 0 To UBound(TabBankSelected)
If BankItem = TabBankSelected(i) Then
Compteur = Compteur + 1
Existing = 0
For X = 0 To UBound(TabBankOrigine)
If BankItem = TabBankOrigine(X) Then
Existing = Existing + 1
End If
Next X
End If
Next i
ReDim Preserve TabBankUniqueSelected(3, Z)
TabBankUniqueSelected(0, Z) = BankItem
TabBankUniqueSelected(1, Z) = Existing
TabBankUniqueSelected(2, Z) = Compteur
Z = Z + 1
Next BankItem
Set ColBankUniqueOrigine = New Collection
Z = 0
For i = 0 To UBound(TabBankOrigine)
On Error Resume Next
ColBankUniqueOrigine.Add TabBankOrigine(i), TabBankOrigine(i)
Next
For Each BankItem In ColBankUniqueOrigine
Compteur = 0
For i = 0 To UBound(TabBankOrigine)
If BankItem = TabBankOrigine(i) Then
Compteur = Compteur + 1
Existing = 0
For X = 0 To UBound(TabBankSelected)
If BankItem = TabBankSelected(X) Then
Existing = Existing + 1
End If
Next X
End If
Next i
ReDim Preserve TabBankUniqueOrigine(3, Z)
TabBankUniqueOrigine(0, Z) = BankItem
TabBankUniqueOrigine(1, Z) = Existing
TabBankUniqueOrigine(2, Z) = Compteur
Z = Z + 1
Next BankItem
NombreBankMaturity = UBound(TabBankUniqueOrigine, 2)
NombreBankRenewing = UBound(TabBankUniqueSelected, 2)
BuildingRenewedDep
ReDim Preserve TotalTxbMoney(4)
TotalTxbMoney(0) = TbxMoneyVal1
TotalTxbMoney(1) = TbxMoneyVal2
TotalTxbMoney(2) = TbxMoneyVal3
TotalTxbMoney(3) = TbxMoneyVal4
On Error GoTo 0
SQLSearch = ''' & SelectedMatured.Range('C1') & '''
With MatrixMat
.Range('I3') = Date
.Range('I4') = 'D-' & Format(Home.Range('A1') + 1, '00000')
If ReNewIngDep = True Then: Home.Range('A1') = Home.Range('A1') + 1
.Range('I5') = SelectedMatured.Range('J1')
.Range('A1') = USFSplitDep.LabelCompany
.Range('A2') = CompanyDetailADOQuery(SQLSearch, 4)
.Range('C5') = TabBankUniqueOrigine(0, 0) & ', ' & VlookupBankData(TabBankUniqueOrigine(0, 0), 3)
.Range('C6') = VlookupBankDetails(TabBankUniqueOrigine(0, 0), 5)
.Range('C7') = VlookupBankDetails(TabBankUniqueOrigine(0, 0), 6)
End With
If TabBankUniqueOrigine(1, 0) > 0 Then
For y = 0 To NombreBankRenewing
With SelectedMatured.UsedRange
If TabBankUniqueOrigine(0, 0) = TabBankUniqueSelected(0, y) Then
CountMaturityFax = TabBankUniqueOrigine(2, 0)
WriteClosingDeposit CountMaturityFax, CStr(TabBankUniqueOrigine(0, 0))
CountRenewingFax = TabBankUniqueSelected(2, y)
'HERE >>>>>>>> WAS MISSING THE FOLLOWING LINE :
WriteRenewingDepositSameBank CountRenewingFax, CStr(TabBankUniqueSelected(0, y))
'========================================================================================================
For Z = 1 To NombreBankRenewing
If TabBankUniqueOrigine(0, 0) <> TabBankUniqueSelected(0, Z) Then
TheBank = TabBankUniqueSelected(0, Z)
MoneyToSend = 0
For j = 1 To 4
If USFSplitDep.Controls('TxbBank' & j).Value = TheBank Then
Set Cell = .Find(TheBank, , , xlWhole)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.Offset(0, 12) = 'Tagged'
Set Cell = .FindNext(Cell)
Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
End If
MoneyToSend = MoneyToSend + TotalTxbMoney(j - 1)
End If
Next j
WriteOtherBanksToSend TheBank, MoneyToSend
WriteInterestToCurrentAccount
SendingMoneyOut = True
End If
Next Z
If SendingMoneyOut = False Then WriteInterestToCurrentAccount
Else
End If
End With
Next y
Else
For y = 0 To NombreBankRenewing
With Renewed.UsedRange
CountMaturityFax = TabBankUniqueOrigine(2, 0)
WriteClosingDeposit CountMaturityFax, CStr(TabBankUniqueOrigine(0, 0))
TheBank = TabBankUniqueSelected(0, y)
MoneyToSend = 0
For j = 1 To 4
If USFSplitDep.Controls('TxbBank' & j).Value = TheBank Then
Set Cell = .Find(TheBank, , , xlWhole)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.Offset(0, 12) = 'Tagged'
Set Cell = .FindNext(Cell)
Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
End If
MoneyToSend = MoneyToSend + TotalTxbMoney(j - 1)
End If
Next j
WriteOtherBanksToSend TheBank, MoneyToSend
WriteInterestToCurrentAccount
End With
Next y
End If
HiddingLinesMat
End Sub
