XL 2016 Ecriture comptables

[yann77610]

Bonsoir à tous,


Je suis comptable et je dois intégrer automatiquement des écritures comptables à partir d’un fichier excel reçu des dépenses et recettes de ma cliente avec des centaines de lignes à la suite, exemple de ligne ci-dessous.

Date	N° compte	Intitulé	TTC	TVA	HT
03.01.20	627000	PrlV Cotis Compte à composer	2,14

Je souhaiterai en partant de l’exemple de ligne ci-dessus arriver au résultat ci-dessous afin d’intégrer le fichier retraité dans le logiciel comptable. Je n’arrive pas à rajouter/insérer « une « ligne de compte « 512 » entre les lignes du fichier d’origine de ma cliente, c’est-à-dire que pour chaque ligne de dépense, il faudra rajouter une ligne de compte « 512 banque » en dessous. Je n’ai aucune base en vba ou autre. Peut-être que quelqu’un pourrait m’aider ?

	N° compte	Intitulé	Débit	Crédit
03.01.20	627000	PrlV Cotis Compte à composer	2,14
03.01.20	512000	PrlV Cotis Compte à composer		2,14

Le même problème s’il y’a de la TVA dans le fichier d’origine, je n’arrive pas à rajouter/insérer « deux » lignes en dessous de la ligne du fichier d’origine. Ci-dessous la ligne en question du fichier d’origine pour une dépense avec TVA.

Date	N° compte	Intitulé	TTC	TVA	HT
03.01.20	626100	PayPal pour OVH	50,62	8,44	42,18

Ci-après le fichier que j’espère obtenir avec une ligne rajoutée pour la TVA en compte « 44566 » et une ligne en compte « 512 » pour le montant TTC.

Date	N° compte	Intitulé	Débit	Crédit
03.01.20	626100	PayPal pour OVH	42,18
03.01.20	445660	PayPal pour OVH	8,44
03.01.20	512000	PayPal pour OVH		50,62

Je ne sais pas si je suis claire, en tout cas si une âme charitable pouvait m’aider… Merci.

 

[chris]

Bonjour

03.01.20 n'est pas un format date pour Excel sauf formatage particulier

Il serait plus sûr que tu postes un exemple représentatif de quelques lignes

 

[Dudu2]

Bonjour,
Je vais te coder ça.

 

[yann77610]

bonjour, merci

 

[soan]

Bonjour Yann, le fil,

bienvenue sur le site XLD !

je te propose le fichier joint ci-dessous.

tu peux voir que "Feuil2" est vide ; va sur "Feuil1" ; note que j'ai d'abord corrigé ton erreur : en ligne 2, le montant 2,14 pour le compte 627000 (compte de charges) n'est pas un TTC mais un HT ! et comme tu as mis le HT en dernière colonne F, 2,14 doit être en F2 et pas en D2 ! à moins que tu aies inversé les intitulés des 2 colonnes "TTC" et "HT" ?

fais Ctrl e ➯ travail effectué. ça sera très rapide, même sur plusieurs milliers de lignes, car j'ai utilisé la méthode des Tableaux.​

---

ATTENTION : je me suis rendu compte d'un bug dans mon présent fichier : la date du 3 janvier 2020 devient le 1er mars 2020 ! ça fait cela : 03.01.20 → 01.03.20 ; ce problème est corrigé avec ma nouvelle version v2 du post #8.
​

---

salut patrick, peux-tu regarder mon fichier ci-joint ? connais-tu un moyen simple pour que ça ne fasse pas une inversion de date automatique ? c'est lors de l'exécution de cette instruction : T1 = [A2].Resize(n, 6) ; la colonne A de la feuille contient 03.01.20 (vraie date au format jj.mm.aa) et devient automatiquement, dans la 1ère colonne du tableau T1 : 01.03.20 ; mon fichier v2 donne les bonnes dates du 03.01.20, mais la seule solution que j'ai trouvée à été de faire un tableau séparé pour les dates, ce qui rend assez complexe : 4 tableaux au lieu de 2 ! ​

soan

 

[Dudu2]

En complément... Mais sans la correction de @soan, c'est à dire tel que spécifié.
Ce classeur est indépendant du classeur des Recettes & Dépenses.
Fichier supprimé, voir plus loin.

 

[yann77610]

je vais voir ce que ca donne en tous cas merci pour vos réponses.

 

[soan]

@yann77610, le fil,

je me suis rendu compte d'un bug dans mon fichier précédent : la date du 3 janvier 2020 devient le 1er mars 2020 ! ça fait cela : 03.01.20 → 01.03.20 ; ce problème est corrigé avec cette nouvelle version : 03.01.20 reste inchangé ; de plus, comme dans la 1ère version, c'est une vraie date au format jj.mm.aa, pas une date texte.

même utilisation : la 2ème feuille est vide ; va sur la 1ère feuille, et fais Ctrl e ; dans mon prochain post, je vais mettre une 3ème version où 2,14 est en D2 au lieu de F2, même si c'est la colonne TTC (alors qu'une charge est normalement en colonne HT).​

Option Explicit: Option Base 1

Sub Essai()
  If ActiveSheet.Name <> "Feuil1" Then Exit Sub
  Dim n&: n = Cells(Rows.Count, 1).End(3).Row: If n = 1 Then Exit Sub
  Dim D01() As String, T01, D02() As String, T02, k As Byte, d$, i&, j&
  n = n - 1: ReDim D01(n): T01 = [B2].Resize(n, 5)
  For i = 1 To n
    With [A1].Offset(i): D01(i) = Format(.Value, "dd.mm.yyyy"): End With
  Next i
  i = 2 * n + Application.Count(Columns(5))
  ReDim D02(i): ReDim T02(5, i): j = 1
  For i = 1 To n
    D02(j) = D01(i): T02(1, j) = T01(i, 1): T02(2, j) = T01(i, 2): T02(3, j) = T01(i, 5): j = j + 1
    k = 5 + 2 * (T01(i, 4) > 0)
    If k = 3 Then
      D02(j) = D01(i): T02(1, j) = "445660": T02(2, j) = T01(i, 2): T02(3, j) = T01(i, 4): j = j + 1
    End If
    D02(j) = D01(i): T02(1, j) = "512000": T02(2, j) = T01(i, 2): T02(4, j) = T01(i, k): j = j + 1
  Next i
  Application.ScreenUpdating = 0: Worksheets("Feuil2").Select: Columns("A:E").ClearContents
  [A1] = "Date": [B1] = "N° compte": [C1] = "Intitulé": [D1] = "Débit": [E1] = "Crédit"
  j = j - 1: [B2].Resize(j, 4) = Application.Transpose(T02)
  For i = 1 To j
    With [A1].Offset(i)
      d = D02(i): .Value = Format(DateSerial(Right$(d, 4), Mid$(d, 4, 2), Left$(d, 2)), "dd.mm.yy")
    End With
  Next i
End Sub

soan

 

[Dudu2]

Après la version corrigée de @soan, je propose une correction pour ma version pour la rendre tolérante à l'erreur qu'il a signalée.
C'est à dire que s'il n'y a pas de TVA, que le montant soit dans TTC ou HT, il sera pris là où il est.

 

[soan]

@yann77610, le fil,

voici la 3ème version ; 2,14 en D2 au lieu de F2, même si d'un point d'vue comptable, ce montant du compte de charges 627000 est censé être un HT, donc ça serait plus logique qu'il soit en F2 ; faut croire qu'il est utilisé comme montant TTC après exonération de TVA.

même utilisation : la 2ème feuille est vide ; va sur la 1ère feuille, et fais Ctrl e ➯ travail effectué !
​

Option Explicit: Option Base 1

Sub Essai()
  If ActiveSheet.Name <> "Feuil1" Then Exit Sub
  Dim n&: n = Cells(Rows.Count, 1).End(3).Row: If n = 1 Then Exit Sub
  Dim D01() As String, T01, D02() As String, T02, k As Byte, d$, i&, j&
  n = n - 1: ReDim D01(n): T01 = [B2].Resize(n, 5)
  For i = 1 To n
    With [A1].Offset(i): D01(i) = Format(.Value, "dd.mm.yyyy"): End With
  Next i
  i = 2 * n + Application.Count(Columns(5))
  ReDim D02(i): ReDim T02(5, i): j = 1
  For i = 1 To n
    k = 3 - 2 * (T01(i, 4) > 0)
    D02(j) = D01(i): T02(1, j) = T01(i, 1): T02(2, j) = T01(i, 2): T02(3, j) = T01(i, k): j = j + 1
    If k = 5 Then
      D02(j) = D01(i): T02(1, j) = "445660": T02(2, j) = T01(i, 2): T02(3, j) = T01(i, 4): j = j + 1
    End If
    D02(j) = D01(i): T02(1, j) = "512000": T02(2, j) = T01(i, 2): T02(4, j) = T01(i, 3): j = j + 1
  Next i
  Application.ScreenUpdating = 0: Worksheets("Feuil2").Select: Columns("A:E").ClearContents
  [A1] = "Date": [B1] = "N° compte": [C1] = "Intitulé": [D1] = "Débit": [E1] = "Crédit"
  j = j - 1: [B2].Resize(j, 4) = Application.Transpose(T02)
  For i = 1 To j
    With [A1].Offset(i)
      d = D02(i): .Value = Format(DateSerial(Right$(d, 4), Mid$(d, 4, 2), Left$(d, 2)), "dd.mm.yy")
    End With
  Next i
End Sub

soan

 

[yann77610]

Merci merci et encore merci Soan et Dudu2 pour vos 2 versions ! Ca marche nickel !!! Je les garde au chaud. Si j'ai un souci, je reviendrai chez vous, vous êtes des PRO

 

[yann77610]

Un grand merci pour le temps que vous avez pris pour solutionner mon problème

 

[chris]

Bonjour à tous

Une solution sans VBA avec PowerQuery (intégré à Excel)

Une fois la source mise à jour, Données, Actualiser Tout

 

[Amilo]

Bonjour à tous,

Une autre proposition avec Power Query pour le fun,

Cordialement

 

[chris]

re

Une autre proposition avec Power Query pour le fun

Très intéressant le #Table : je note précieusement