Automatiser la facturation via VBARésolu

Question

Bonjour, 

J'ai besoin de votre aide pour automatiser une facturation que je dois répartir en fonction d'une colonne appelée "BPR" et ce, en utilisant VBA.

Voilà mon problème : 
J'ai des fichiers sous format .txt dont j'arrive à les faire importer sous excel, ces fichiers je veux les comparer avec des données déjà existante dans mon fichier Excel.
j'ai essayé de faire une macro d'importation mais j'arrive pas à compléter cette macro en utilisant l'enregistreur des macro pour faire les choses suivantes : 

- une niveau de la feuille 1, je saisis le mois M avec lequel je veux comparer le fichier.

- Je cherche à nommer l'onglet par le Mois M+1, par exemple: Décembre1 pour la 1ere macro, Décembre2 pour la 2émé macro, Décembre3 pour la 3éme macro une fois je clique sur importer (pour chaque fichier j'ai fait une macro spécifique), 

- une fois l'importation effectuée, je souhaite comparer la colonne "radical" de chaque fichier avec la colonne radical du Mois M (exemple : Radical du Novembre1 avec Radical du Décembre1,....), et si je trouve les mêmes radicaux je remplis la colonne BPR du Mois M+1 (exemple BPR du Décembre1) par la donnée trouvée dans la colonne BPR du Mois M (BPR du Novembre1) et ainsi de suite pour les autres onglets. (je dois parcourir l'ensemble de la colonne "Radical" jusqu'à la dernière ligne)


- Pour le Mois M (les onglet du Mois M), je dois pour les radicaux (colonne "radical") ,dont la colonne BPR est vide, les mettre dans la colonne "radical sans BPR". Cette dernière est composée de 2 sous colonnes (sous colonne Radical+ sous colonne BPR) et une fois je renseigne la sous colonne BPR correspondante pour ces radicaux dans la colonne "radical sans BPR", la colonne "BPR" doit être rafraîchi par ces données saisies au niveau de sous colonne BPR. 

- Aussi je dois copier les radicaux et les BPR, dans les colonnes "Confirmation" et "Réservation", dont la condition suivante est réalisé : les cellules de colonne N/K = 1.

SVP, Help me.



Merci d'avance.


Cordialement.
---

Sub import()
'importation d'un fichier dans le Poste de travail

ChDrive "C:"
    ChDir "C:"

fichier = Application.GetOpenFilename("Texte fichiers (*.txt), *.txt")

Sheets.Add After:=Sheets(Sheets.Count)
    With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fichier, Destination:=Range("$D$1"))
   
        .Name = "Fichier 4537-D2809273_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 2, 2, 2, 1, 2, 1, 1)
        .TextFileFixedColumnWidths = Array(1, 4, 4, 7, 30, 6, 9, 7, 9, 7, 9, 7)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    'insertion d'une ligne.
    
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    'Je nomme les colonnes puis je fais un peu de coloriage.
    
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Code imputation"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "N° de SOM"
    Range("F2").Select
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Radical"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Déjà retenu"
    Selection.Interior.Color = RGB(174, 240, 194)
    Range("N2").Select
    Columns("N:N").EntireColumn.AutoFit
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Reste à précompter"
    Columns("L:L").EntireColumn.AutoFit
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Mensualité"
    Selection.Interior.Color = RGB(192, 255, 64)
    Range("K2").Select
    Columns("K:K").EntireColumn.AutoFit
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Mois encours"
    Columns("I:I").EntireColumn.AutoFit
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "BPR"
    Selection.Interior.Color = RGB(255, 255, 0)
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "N/K"
    Selection.Interior.Color = RGB(180, 255, 64)
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Radicaux sans BPR"
    Columns("R:R").EntireColumn.AutoFit
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "Radical"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "BPR"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "Réservation"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "Radical"
    Range("U2").Select
    ActiveCell.FormulaR1C1 = "BPR"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Confirmation"
    Columns("V:V").EntireColumn.AutoFit
    Range("V2").Select
    ActiveCell.FormulaR1C1 = "Radical"
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "BPR"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Fichier consommation"

'Je cherche à nommer l'onglet par le Mois M+1, par exemple: Décembre1 pour la 1ere macro, Décembre2 pour la 2émé macro, Décembre3 pour la 3éme macro une fois je clique sur importer (pour chaque fichier j'ai fait une macro spécifique),

'je souhaite comparer la colonne "radical" de chaque fichier avec la colonne radical du Mois M (exemple : Radical du Novembre1 avec Radical du Décembre1,....), et si je trouve les mêmes radicaux je remplis la colonne BPR du Mois M+1 (exemple BPR du Décembre1) par la donnée trouvée dans la colonne BPR du Mois M (BPR du Novembre1) et ainsi de suite pour les autres onglets. (je dois parcourir l'ensemble de la colonne "Radical" jusqu'à la dernière ligne).
     ' Pour le Mois M (les onglet du Mois M), je dois pour les radicaux (colonne "radical") ,dont la colonne BPR est vide, les mettre dans la colonne "radical sans BPR". Cette dernière est composée de 2 sous colonnes (sous colonne Radical+ sous colonne BPR) et une fois je renseigne la sous colonne BPR correspondante pour ces radicaux dans la colonne "radical sans BPR", la colonne "BPR" doit être rafraîchi par ces données saisies au niveau de sous colonne BPR. 
     
    'J'applique la formule sur la cellule Q2
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=+RC[-3]/RC[-6]"
    'j'applique la formule jusqu'à la dernière ligne de la colonne radical
    Range("Q2").Select
    Selection.AutoFill Destination:=Range("Q2:Q1901"), Type:=xlFillDefault

'Je dois copier les radicaux et les BPR, dans les colonnes "Confirmation" et "Réservation", dont la condition suivante est réalisé : les cellules de colonne N/K = 1.

End Sub

jordane45 · Answer

Bonjour,

1 - J'ai édité ton message pour y mettre la coloration syntaxique.
Merci d'y penser la prochaine fois
(voir ici pour plus d'infos :  http://codes-sources.commentcamarche.net/faq/10686-le-nouveau-codes-sources-comment-ca-marche#balises-code )

2 - Dans ton code tu peux déjà le simplifier en remplaçant par exemple les lignes du genre :

Range("F1").Select
    ActiveCell.FormulaR1C1 = "Code imputation"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "N° de SOM"
'...
    Range("K2").Select
    Columns("K:K").EntireColumn.AutoFit


Par :

Range("F1").value = "Code imputation"
    Range("G1").value = "N° de SOM"
'...
    Columns("K:K").EntireColumn.AutoFit
 
 
=> Commence déjà par "alléger" ton code (en tenant compte de ma remarque précédente) puis reviens nous exposer tes soucis..

/!\ Attention : Une question [claire, précise, détaillée...]  par discussion.
Voir les règles du site :
http://codes-sources.commentcamarche.net/contents/11-charte-de-commentcamarche-net-conseils-d-ecriture

you_el · Answer

Bonjour,

Merci jordane45 pour ton retour,

J'ai appliqué ton conseil, et j'ai retravaillé mon code, mais je bloque sur un truc :

Comment je peux appliquer une formule sur une feuille en se basant sur les données d'une autre feuille. j'ai essayé de faire un InPutbox pour sélectionner la feuille objet de comparaison mais ça marche pas (j'ai mis juste la partie qui pose problème)


Dim NbLignes As Long
Dim i As Long

'Sur la nouvelle feuille créée j'applique la formule suivante = colonne N1 /colonne K1 au niveau de la colonne Q
    With ActiveSheet.UsedRange
    NbLignes = .Row + .Rows.Count - 1 'la dernière ligne du tableau
    End With
    For i = NbLignes To 2 Step -1 'on commence à partir de la 2ème cellule de la colonne Q
    
    Cells(i, "Q").FormulaR1C1 = "=RC[-3]/RC[-6]"
    'je sélectionne la feuille objet de la comparaison
    nomfeuil = InputBox("Veuillez entrer le nom de la feuille objet de la comparaison")
    Sheets(nomfeuil).Select
    Cells(i, "P").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(nomfeuil!RC[-1],nomfeuil!R2C15:R12C16,1,FALSE))=TRUE,""Faux"",VLOOKUP(nomfeuil!RC[-1],nomfeuil!R2C15:R12C16,2,FALSE))"
'c'est la formule que je veux appliquer : =SI(ESTERREUR(RECHERCHEV(K2;$F$2:$G$1229;1;FAUX))=VRAI;"Faux";RECHERCHEV(K2;$F$2:$G$1229;2;FAUX))
    
    Next i

----
Josef

jordane45 · Answer

La variable nomFeuil .. pour l'utiliser.. il faut utiliser la "concaténation" de chaines de caractères...

Testes le code suivant pour comprendre :

Sub test()

Dim X As String
X = " Coucou"

' Exemple 1 sans concaténation :
MsgBox ("ceci est un test pour dire X")

' Exemple2 : Cette fois avec concaténation :
MsgBox ("Ceci est un second test pour dire " & X)

End Sub

Pour écrire des formules je t'invite également à passer par FormulaLocal plutot que FormulaR1C1 (plus simple à manipuler et à lire)

'-------------------------------------------------------------------
' Les deux codes suivants font la même chose :
'-------------------------------------------------------------------

'Avec FormulaR1C1
Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[2]C)"

' Avec FormulaLocal
Range("A1").FormulaLocal = "=somme(A2:A3)"

you_el · Answer

Re: 

SVP, j'ai une question : 

Lorsqu'on utilise FormulaLocal, on peut mettre entre les guillemets les formules qu'on utilise sous excel, 
 
'pour FormulaLocal :
Cells(i, "Q").FormulaLocal = "=cells(i,"N") & "/" & cells(i,"K")"

y-a-t il un problème de syntaxe dans ce cas?

Merci

 

 
----
Josef

you_el · Answer

Re:

J'ai une autre question : 

Lorsqu'on utilise FormulaLocal, est ce qu'on peut mettre entre les guillemets les formules qu'on utilise sous excel ou bien qu'on crée sous excel?

J'ai essayé d'appliquer sur VLOOKUP mais je bloque sur la syntaxe:

Sub import()
Dim NbLignes As Long
Dim i As Long
Dim j As Long
Dim onglet As String

'j'applique la formule suivante = colonne N1 (Déjà retenu)/colonne K1 (Mensualité) au niveau de la colonne Q
    With ActiveSheet.UsedRange
    NbLignes = .Row + .Rows.Count - 1 'la dernière ligne du tableau
    End With
    For i = NbLignes To 2 Step -1 'on commence à partir de la 2ème cellule de la colonne Q
    
    Cells(i, "Q").FormulaLocal = "=N" & i & "/" & "K" & i
          
    Next i
    
    onglet = InputBox("Veuillez entrer le nom de la feuille objet de la comparaison")
    'cela servira à choisir la feuille dont je veux comparer ses cellules de la colonne O avec les cellules de la nouvelle feuille créée
    ' par exemple sur la nouvelle feuille (fichier) j'ai la colonne "P" vide .
    ' Pour la remplir je dois faire une recherche sur les données qui se trouvent dans la colonne "O" dans une autre feuille "test1".
    ' Si je trouve dans la feuille "test1"  les mêmes données:
    ' par exemple  la cellule O2 feuille "fichier" = cellule O2 feuille "test1"  donc je mets dans la colonne "P".feuille "fichier"  la valuer trouvée dans la colonne "P".feuille"test1"
    ' jusqu'à la dernière ligne touvée dans la colonne "O" de la feuille "fichier".
    'j'essaie d'utiliser VLOOKUP mais la synthaxe pose problème
        
    With Range("P2:P" & NbLignes)
    .Formula = "=VLOOKUP(O" & NbLignes , "&onglet&"  !O2: P12 , 1 , False  )"
    .Value = .Value
    End With
    
End Sub

--
----
Josef

Automatiser la facturation via VBA

5 réponses

Votre réponse

Discussions similaires