Automatiser la facturation via VBA [Résolu]

you_el 6 Messages postés vendredi 25 avril 2014Date d'inscription 30 avril 2014 Dernière intervention - 25 avril 2014 à 16:38 - Dernière réponse : you_el 6 Messages postés vendredi 25 avril 2014Date d'inscription 30 avril 2014 Dernière intervention
- 30 avril 2014 à 20:33
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

Afficher la suite 

Votre réponse

10 réponses

jordane45 21706 Messages postés mercredi 22 octobre 2003Date d'inscriptionModérateurStatut 22 juillet 2018 Dernière intervention - 28 avril 2014 à 15:28
0
Merci
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




Commenter la réponse de jordane45
you_el 6 Messages postés vendredi 25 avril 2014Date d'inscription 30 avril 2014 Dernière intervention - 29 avril 2014 à 16:59
0
Merci
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
Commenter la réponse de you_el
jordane45 21706 Messages postés mercredi 22 octobre 2003Date d'inscriptionModérateurStatut 22 juillet 2018 Dernière intervention - Modifié par jordane45 le 29/04/2014 à 17:11
0
Merci
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)"
Commenter la réponse de jordane45
you_el 6 Messages postés vendredi 25 avril 2014Date d'inscription 30 avril 2014 Dernière intervention - Modifié par jordane45 le 30/04/2014 à 12:42
0
Merci
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
jordane45 21706 Messages postés mercredi 22 octobre 2003Date d'inscriptionModérateurStatut 22 juillet 2018 Dernière intervention - 30 avril 2014 à 12:41
y-a-t il un problème de syntaxe dans ce cas?
Dans la formule que tu as écrit ? Oui..

Cells(i, "Q").FormulaLocal = "=N" & i & "/" & "K" & i
you_el 6 Messages postés vendredi 25 avril 2014Date d'inscription 30 avril 2014 Dernière intervention - 30 avril 2014 à 12:53
Ok, merci
jordane45 21706 Messages postés mercredi 22 octobre 2003Date d'inscriptionModérateurStatut 22 juillet 2018 Dernière intervention - 30 avril 2014 à 14:34
Si la réponse te convient merci de passer le sujet en RESOLU.
(cliquer sur le lien " Marquer comme résolu " qui se trouve sous le titre de votre quéstion.)
Commenter la réponse de you_el
you_el 6 Messages postés vendredi 25 avril 2014Date d'inscription 30 avril 2014 Dernière intervention - 30 avril 2014 à 17:39
0
Merci
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
jordane45 21706 Messages postés mercredi 22 octobre 2003Date d'inscriptionModérateurStatut 22 juillet 2018 Dernière intervention - 30 avril 2014 à 19:46
Relis ta formule. .. la concaténation n'est pas bonne.

Ps:sur ce forum. .on ne pose qu'une question par discussion.
you_el 6 Messages postés vendredi 25 avril 2014Date d'inscription 30 avril 2014 Dernière intervention - 30 avril 2014 à 20:33
Ok, je vais la revoir.

Merci pour ton aide.
Cordialement
Commenter la réponse de you_el

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.