encom
Nouveau membre
bonjour a tous,
voila j'ai l'erreur en entete sur le code suivant mais pas dans tout les private sub.
le traitement j'ouvre un fichier banque pour créer un fichier de comptabilisation en piochant des infos dans un fichier de mapping.
merci de votre aide.
voila j'ai l'erreur en entete sur le code suivant mais pas dans tout les private sub.
le traitement j'ouvre un fichier banque pour créer un fichier de comptabilisation en piochant des infos dans un fichier de mapping.
merci de votre aide.
Code:
Option Explicit
Dim ADYEN As Worksheet
Dim adyenCpta As Worksheet
Dim Boutiques As Workbook
Dim mag As Worksheet
Dim numMagA As String
Dim numBoutique As String
Dim j As Long
Dim finalrow As Long
Dim i As Long
Dim Nextrowac As Long
Dim finalrowb As Long
Sub ADYEN_CB()
'création feuille2
Set adyenCpta = Worksheets.Add(after:=Worksheets(1))
'renomage feuille 2
adyenCpta.Name = "Comptabilisation"
' initialisation feuille
Set ADYEN = Worksheets(1)
Set adyenCpta = Worksheets(2)
Set Boutiques = GetObject("C:XXXXXXXXXXXX\liste mag.xlsx")
Set mag = Boutiques.Worksheets(1)
'calcul derniere ligne fichier Boutiques
finalrowb = mag.Cells(mag.Rows.Count, 1).End(xlUp).Row
'initialisation compteur
Nextrowac = 1
' calcul du nombre de ligne du fichier adyen
finalrow = ADYEN.Cells(Rows.Count, 1).End(xlUp).Row
For i = finalrow To 2 Step -1
If ADYEN.Cells(i, 38) = "" And ADYEN.Cells(i, 8) = "Balancetransfer" Then
Call report
ElseIf ADYEN.Cells(i, 38) = "" And ADYEN.Cells(i, 8) = "Fee" Then
Call frais
ElseIf ADYEN.Cells(i, 38) = "" And ADYEN.Cells(i, 8) = "MerchantPayout" Then
Call modepayment
ElseIf ADYEN.Cells(i, 38) = "" And ADYEN.Cells(i, 8) = "InvoiceDeduction" Then
Call frais
Else
Call compta
End If
Next i
'Call datevaleur
Set adyenCpta = Nothing
Set ADYEN = Nothing
Set Boutiques = Nothing
Set mag = Nothing
Application.ScreenUpdating = True
End Sub
Private Sub ecriture()
For j = 2 To finalrowb
numBoutique = Boutiques.Sheets(1).Cells(j, 4).Value
numMagA = CStr(ADYEN.Cells(i, 38).Value)
If numMagA = numBoutique Then
Call ecriturecpta
End If
Next j
End Sub
Private Sub compta()
Select Case ADYEN.Cells(i, 5)
Case Is = "wechatpay_pos"
Call ecriture
Case Is = "alipay"
Call ecriture
Case Is = "visa"
Call ecriture
Case Is = "mc"
Call ecriture
Case Is = "diners"
Call ecriture
Case Is = "discover"
Call ecriture
Case Is = "maestro"
Call ecriture
Case Is = "visadankort"
Call ecriture
Case Is = "bijcard"
Call ecriture
Case Is = "amex"
Call ecriture
Case Is = "cup"
Call ecriture
Case Is = "jcb"
Call ecriture
End Select
End Sub
Private Sub frais()
Call cptafrais
adyenCpta.Cells(Nextrowac, 22) = " FRAIS ADYEN " & CDate(Mid(ADYEN.Cells(i, 6), 1, 10))
adyenCpta.Cells(Nextrowac, 9) = "F0050"
'adyenCpta.Cells(Nextrowac, 29) = REF RAPPROCHEMENT
Nextrowac = Nextrowac + 1
End Sub
Private Sub cptafrais()
adyenCpta.Cells(Nextrowac, 5) = Left(ADYEN.Cells(i, 2), 5)
adyenCpta.Cells(Nextrowac, 6) = "627800"
adyenCpta.Cells(Nextrowac, 7) = 0
adyenCpta.Cells(Nextrowac, 8) = 0
If ADYEN.Cells(i, 15) > 0 Then
adyenCpta.Cells(Nextrowac, 17) = ADYEN.Cells(i, 15)
ElseIf ADYEN.Cells(i, 16) > 0 Then
adyenCpta.Cells(Nextrowac, 17) = ADYEN.Cells(i, 16)
End If
adyenCpta.Cells(Nextrowac, 10) = 0
Call Pays
'adyenCpta.Cells(Nextrowac, 12) = mag.Cells(j, 1)
'adyenCpta.Cells(Nextrowac, 13) = mag.Cells(j, 8)
adyenCpta.Cells(Nextrowac, 14) = 0
adyenCpta.Cells(Nextrowac, 15) = 0
Call Devise
End Sub
Private Sub modepayment()
' type ecriture
adyenCpta.Cells(Nextrowac, 5) = Left(ADYEN.Cells(i, 2), 5)
' numero de la societe
Call comptecpta
adyenCpta.Cells(Nextrowac, 7) = 0
adyenCpta.Cells(Nextrowac, 8) = 0
adyenCpta.Cells(Nextrowac, 9) = 0
adyenCpta.Cells(Nextrowac, 10) = 0
Call Pays
adyenCpta.Cells(Nextrowac, 11) = mag.Cells(j, 7) <----- ici problème
'adyenCpta.Cells(Nextrowac, 12) = mag.Cells(j, 1)
'adyenCpta.Cells(Nextrowac, 13) = mag.Cells(j, 8)
adyenCpta.Cells(Nextrowac, 14) = 0
adyenCpta.Cells(Nextrowac, 15) = 0
Call Devise
If ADYEN.Cells(i, 15) > 0 Then
adyenCpta.Cells(Nextrowac, 17) = ADYEN.Cells(i, 15)
ElseIf ADYEN.Cells(i, 16) > 0 Then
adyenCpta.Cells(Nextrowac, 17) = ADYEN.Cells(i, 16)
End If
adyenCpta.Cells(Nextrowac, 22) = " VIREMENT ADYEN"
'adyenCpta.Cells(Nextrowac, 29) = REF RAPPROCHEMENT
Nextrowac = Nextrowac + 1
End Sub
Private Sub comptecpta()
Select Case Left(ADYEN.Cells(i, 2), 5)
Case Is = "G0006"
adyenCpta.Cells(Nextrowac, 6) = "512140"
Case Is = "G0017"
adyenCpta.Cells(Nextrowac, 6) = "512540"
Case Is = "G0027"
adyenCpta.Cells(Nextrowac, 6) = "512300"
Case Is = "G0035"
adyenCpta.Cells(Nextrowac, 6) = "512100"
End Select
End Sub
Private Sub ecriturecpta()
' type ecriture general pour brut
adyenCpta.Cells(Nextrowac, 5) = Left(ADYEN.Cells(i, 2), 5)
Call ecriturecptacorp
Nextrowac = Nextrowac + 1
If WorksheetFunction.Sum(ADYEN.Range(ADYEN.Cells(i, 17), ADYEN.Cells(i, 21))) <> 0 Then
'type ecriture general pour frais
adyenCpta.Cells(Nextrowac, 5) = Left(ADYEN.Cells(i, 2), 5)
Call fraisbrut
Nextrowac = Nextrowac + 1
End If
End Sub
Private Sub ecriturecptacorp()
adyenCpta.Cells(Nextrowac, 6) = "511300"
adyenCpta.Cells(Nextrowac, 7) = 0
adyenCpta.Cells(Nextrowac, 8) = 0
adyenCpta.Cells(Nextrowac, 7) = 0
adyenCpta.Cells(Nextrowac, 9) = mag.Cells(j, 4)
adyenCpta.Cells(Nextrowac, 10) = 0
adyenCpta.Cells(Nextrowac, 11) = mag.Cells(j, 7) <-----alors que la ca passe bien
adyenCpta.Cells(Nextrowac, 12) = mag.Cells(j, 1)
adyenCpta.Cells(Nextrowac, 13) = mag.Cells(j, 8)
adyenCpta.Cells(Nextrowac, 14) = 0
adyenCpta.Cells(Nextrowac, 15) = 0
Call Devise
If ADYEN.Cells(i, 12) > 0 Then
adyenCpta.Cells(Nextrowac, 18) = ADYEN.Cells(i, 12)
ElseIf ADYEN.Cells(i, 11) > 0 Then
adyenCpta.Cells(Nextrowac, 17) = ADYEN.Cells(i, 11)
End If
Select Case ADYEN.Cells(i, 5)
Case Is = "wechatpay_pos"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 10)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "alipay"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 11)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "visa"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 12)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "mc"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 13)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "diners"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 14)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "discover"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 15)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "maestro"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 16)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "visadankort"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 17)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "bijcard"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 18)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "amex"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 19)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "cup"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 20)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "jcb"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 21)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
End Select
If Mid(ADYEN.Cells(i, 4), 5, 1) = "-" Then
adyenCpta.Cells(Nextrowac, 29) = ADYEN.Cells(i, 38) & " " & Left(ADYEN.Cells(i, 4), 10)
Else
adyenCpta.Cells(Nextrowac, 29) = Mid(ADYEN.Cells(i, 4), 6, 8)
End If
End Sub
Private Sub fraisbrut()
adyenCpta.Cells(Nextrowac, 6) = "627800"
adyenCpta.Cells(Nextrowac, 7) = 0
adyenCpta.Cells(Nextrowac, 8) = 0
adyenCpta.Cells(Nextrowac, 9) = mag.Cells(j, 4)
adyenCpta.Cells(Nextrowac, 10) = 0
adyenCpta.Cells(Nextrowac, 11) = mag.Cells(j, 7)
adyenCpta.Cells(Nextrowac, 12) = mag.Cells(j, 1)
adyenCpta.Cells(Nextrowac, 13) = mag.Cells(j, 8)
adyenCpta.Cells(Nextrowac, 14) = 0
adyenCpta.Cells(Nextrowac, 15) = 0
Call Devise
If ADYEN.Cells(i, 12) > 0 Then
If WorksheetFunction.Sum(ADYEN.Range(ADYEN.Cells(i, 17), ADYEN.Cells(i, 21))) > 0 Then
adyenCpta.Cells(Nextrowac, 17) = Abs(WorksheetFunction.Sum(ADYEN.Range(ADYEN.Cells(i, 17), ADYEN.Cells(i, 21))))
Else
adyenCpta.Cells(Nextrowac, 18) = Abs(WorksheetFunction.Sum(ADYEN.Range(ADYEN.Cells(i, 17), ADYEN.Cells(i, 21))))
End If
End If
If ADYEN.Cells(i, 11) > 0 Then
If WorksheetFunction.Sum(ADYEN.Range(ADYEN.Cells(i, 17), ADYEN.Cells(i, 21))) > 0 Then
adyenCpta.Cells(Nextrowac, 18) = Abs(WorksheetFunction.Sum(ADYEN.Range(ADYEN.Cells(i, 17), ADYEN.Cells(i, 21))))
Else
adyenCpta.Cells(Nextrowac, 17) = Abs(WorksheetFunction.Sum(ADYEN.Range(ADYEN.Cells(i, 17), ADYEN.Cells(i, 21))))
End If
End If
Select Case ADYEN.Cells(i, 5)
Case Is = "wechatpay_pos"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 10)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "alipay"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 11)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "visa"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 12)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "mc"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 13)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "diners"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 14)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "discover"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 15)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "maestro"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 16)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "visadankort"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 17)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "bijcard"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 18)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "amex"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 19)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "cup"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 20)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
Case Is = "jcb"
adyenCpta.Cells(Nextrowac, 22) = UCase(mag.Cells(j, 21)) & " " & mag.Cells(j, 4) & " " & mag.Cells(j, 5)
End Select
End Sub