Formule excel VBA

tontonced · 3 Août 2012

Hello à tous,

J'ai une ptit souci que je n'arrive pas à résoudre. En fait j'ai mis une formule qui va de B3 à B1300 car quelquefois mes fichiers font 1300 lignes à peu près. Le problème c'est que ça alourdi mon fichier. Je m'explique :
Mes formules se base sur ma cellule A et même si je n'es que 2 lignes (A3 et A4 par exemple) les formules se colleront quand meme de b3 à b1300. J'aimerais que lorsque je n'ai que deux lignes il ne me mettes les formules que de B3 à B4. Voici une de mes nombreuses formules en vba

Code:

Range("B3.B1300").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R1C1&RC[-1],INVMUL!C[-1]:C[31],33,FALSE)),"""",VLOOKUP(R1C1&RC[-1],INVMUL!C[-1]:C[31],33,FALSE))"

Merci d'avance

cdt

tontonced · 3 Août 2012

Re : Formule excel VBA

hello,

merci pour ta réponse.

c'est presque bon sauf que

Quand j'ai des données de A3 à A33 la formule ne se colle que en B33 et non pas de B3 à B33.

As tu la solution?

merci d'avance

tontonced · 3 Août 2012

Re : Formule excel VBA

ça m'as l'air pas mal...

Par contre, comme dit précédemment je n'aurais pas forcément 1300 ligne, je peux en avoir moins mais je ne pense pas que ça pose de problème au code...

Je dois appliquer cette méthode sur les colonne C D E F... si j'applique ton code en rajoutant des "each" il me met une erreur...

tontonced · 4 Août 2012

Re : Formule excel VBA

J'aurais bien voulu mais le problème c'est que c'est des données confidentielles (professionelles 🙂

c'est pourquoi je ne t'ai pas envoyer le fichier. je voudrais pouvoir appliquer mes autres formules pour les colonnes c d e ...

tontonced · 4 Août 2012

Re : Formule excel VBA

le code que tu m'as fournit, correspond aux formules pour la colonne B qui va chercher les données dans la colonne A
j'ai d'autres formules a appliquer (RECHERCHEV) a appliquer dans les autre colonnes c d e... qui vont aussi chercher dans la colonne A. Si je devais applquer le code dans les autres colonne avec d'autres formules comment devrait je faire?

jsais pas si c'est très clair mais c'est vrai que sans fichier c'est pas facile je le conçois...

tontonced · 4 Août 2012

Re : Formule excel VBA

Voici le code que tu m'as envoyé que j'ai adapté et en dessous j'ai les autres formules. je voudrais donc appliquer la meme méthode sur les autres colonnes. Je pense que j'utilise mal le next...

Code:

Dim c As Range
For Each c In Range("B3:B1300")
     
    If c.Offset(0, -1) <> "" Then c.Offset(0, 1).FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R1C1&RC[-1],INVMUL!C[-1]:C[31],33,FALSE)),"""",VLOOKUP(R1C1&RC[-1],INVMUL!C[-1]:C[31],33,FALSE))"
      
      Next c

      
      Range("C3.C1300").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R1C1&RC[-2],HISINV!C[-2]:C[30],33,FALSE)),"""",VLOOKUP(R1C1&RC[-2],HISINV!C[-2]:C[30],33,FALSE))"


Range("D3.D1300").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-RC[-2])"

Range("E3.E1300").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R1C1&RC[-4],INVMUL!C[-4]:C[32],37,FALSE)),"""",VLOOKUP(R1C1&RC[-4],INVMUL!C[-4]:C[32],37,FALSE))"

tontonced · 4 Août 2012

Re : Formule excel VBA

C'est encore moi 🙂. C'est bon c'est reglé y'avait un problème avec mon dim et mon next maintenant c'est ok. Je te remercie pour ton aide jvais pouvoir rentrer chez moi tranquille 🙂. Bon week end à toi

tontonced · 4 Août 2012

Re : Formule excel VBA

C'est encore mieux jtesterais lundi merci 🙂

tontonced · 8 Août 2012

Re : Formule excel VBA

Merci pour ton aide ça a fonctionné. Par contre j'ai tenté de faire la même formule mais cette fois j'ai des données de B1 à B... tout dépendra de mes fichiers j'ai donc essayé t'appliquer ta formule. Cela fonctionne mais la formule s'écrit ligne par ligne c'est donc très si j'ai 1000 lignes ou plus. Peux tu regarder s'il n' a pas un souci avec mon code?

merci

Code:

Sub MISE_EN_FORME()
'
' MISE_EN_FORME Macro
' Macro enregistrée le 08/08/2012 par komoro1
'
Sheets("HISINV J-1").Select
Dim c As Range
For Each c In Range("A1:A1300")

If c.Offset(0, 1) <> "" Then

c.FormulaR1C1 = "=RC[4]&TRIM(RC[7])&TRIM(RC[6])"

End If

Next c

Sheets("PROCEDURE").Select



End Sub

Une dernière chose dans mon userform, lorsque je demande d'insérer un fichier excel, il me met le nom entier du fichier. J'ai réussi à faire change le nom grace au left devant le c.name par mon code bloque au niveau de la ligne en rouge. Comment ça se fait?

Code:

Private Sub UserForm_Initialize()
Dim c As Workbook 'déclare la variable o (Classeur)

For Each c In Workbooks 'boucle sur tous les claeeurs ouverts
    'alimente la ListBox1 du nom des classeurs ouverts sauf le nom de ce classeur et "PERSO.XLS"
    If Left(c.Name, 6) = "invmul" Then Me.ListBox1.AddItem c.Name
Next c 'prochain classeur ouvert de la boucle
End Sub

Private Sub OK_Click()
Dim cc As Workbook 'déclare la variable cc (Classeur Cible)
Dim oc As Object 'déclare la variable oc (Onglet Cible)
Dim x As Integer 'déclare la variable x (incrément)
Dim dest As Range 'déclare la variable dest (cellule de DESTination)
Dim cs As Workbook 'déclare la variable cs (Classeur Source)
Dim os As Object 'déclare la variable os (Onglet Source)

Set cc = ThisWorkbook 'définit le classeur cible
Set oc = cc.Sheets("INVMUL") 'définit l'onglet cible
With Me.ListBox1 'prend en compte la ListBox1
    For x = 0 To .ListCount - 1 'boucle sur tous les éléments de la ListBox1
        If .Selected(x) = True Then 'condition : si l'élément est sélectionné
            'définit la cellule de destination (A1 de l'onglet cible si A1 est vide, sinon la première cellule vide de la colonne A)
            Set dest = IIf(oc.Range("B1") = "", oc.Range("B1"), oc.Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0))
           [COLOR="#FF0000"]Set cs = Workbooks(.List(x)) 'définit le classeur source    [/COLOR]     
            Set os = cs.Sheets(1) 'définit l'onglet source
            os.UsedRange.Copy dest 'copie la plage des cellules éditées de l'onglet source et la copie dans dest
            End If 'fin de la condition
    Next x 'prochain élément séolectionné de la boucle
End With 'fin de la prise en compte de la ListBox1
Unload Me 'vide et ferme l'Userform
oc.Activate 'sélectionne l'onglet cible
Sheets("Feuil1").Select
End Sub

tontonced · 8 Août 2012

Re : Formule excel VBA

la ligne rouge c'est celle ci Set cs = Workbooks(.List(x)). Apparrement le rouge ne veux pas fonctionner 🙂

Staple1600 · 8 Août 2012

Re : Formule excel VBA

Bonjour à tous

tontonced à dit:
J'aurais bien voulu mais le problème c'est que c'est des données confidentielles (professionelles 🙂

c'est pourquoi je ne t'ai pas envoyer le fichier. je voudrais pouvoir appliquer mes autres formules pour les colonnes c d e ...

Quand un classeur contient des données confidentielles, on le laisse bien au chaud là ou il est. 😉

Par contre, rien n'empêche de créer un fichier exemple fait avec la même structure, les mêmes formules, le même code VBA, avec les données confidentielles remplacées par des données fictives.
(ex: NOM1 PRENOM1 ADRESSE1 CP1 VILLE1 etc...)
Et si ce fichier contient pas plus d'une cinquantaine de lignes de données "bidons", c'est encore mieux 😉

Donc tontonced, on attend ton fichier anonymisé pour pouvoir tester ce que l'on a en tête 😉

tontonced · 8 Août 2012

Re : Formule excel VBA

ok jvais tenter de faire tout de suite

tontonced · 8 Août 2012

Re : Formule excel VBA

voici le fichier,

j'ai tout modifier donc les chiffres seront n'importe quoi hein 🙂

J'ai du supprimer le code pour layout et pour l'autre aussi car le fichier est trop gros je t'envoye le code en balise

cdt

Je cherche a faire une comparation entre l'onglet bla et l'onglet INVMUL tout ça dans l'onglet resultat

J'ai du t'envoyer 4 fichiers car c'était trop gros. Chaque fichier correpond à un onglet (EXEMPLE1 = onlet1...)

Bonne chance

merci

'LAYOUT

Code:

Sub Filtre_sur_Place()

Sheets("Résultat").Select

Dim c As Range
For Each c In Range("B3:B1300")

If c.Offset(0, -1) <> "" Then

'colonne B

c.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-1],INVMUL!C[-1]:C[31],33,FALSE)),"""",VLOOKUP(R1C1&RC[-1],INVMUL!C[-1]:C[31],33,FALSE))"

'colonne C

c.Offset(0, 1).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-2],HISINV!C[-2]:C[30],33,FALSE)),"""",VLOOKUP(R1C1&RC[-2],HISINV!C[-2]:C[30],33,FALSE))"

'colonne D

c.Offset(0, 2).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-RC[-2])"

'colonne E

c.Offset(0, 3).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-4],INVMUL!C[-4]:C[32],37,FALSE)),"""",VLOOKUP(R1C1&RC[-4],INVMUL!C[-4]:C[32],37,FALSE))"

'colonne F
c.Offset(0, 4).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-5],HISINV!C[-5]:C[31],37,FALSE)),"""",VLOOKUP(R1C1&RC[-5],HISINV!C[-5]:C[31],37,FALSE))"

'colonne G

c.Offset(0, 5).FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(RC[-2]<RC[-1],""REFORCAGE"",""ok""))"

'colonne H

c.Offset(0, 6).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-7],INVMUL!C[-7]:C[22],30,FALSE)),"""",VLOOKUP(R1C1&RC[-7],INVMUL!C[-7]:C[22],30,FALSE))"

'colonne I

c.Offset(0, 7).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-8],HISINV!C[-8]:C[21],30,FALSE)),"""",VLOOKUP(R1C1&RC[-8],HISINV!C[-8]:C[21],30,FALSE))"


'colonne J

c.Offset(0, 8).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-RC[-2])"

'colonne K

c.Offset(0, 9).FormulaR1C1 = "=IF(RC[-1]="""","""",(RC[-3]-RC[-2])/RC[-2])"

'colonne L

c.Offset(0, 10).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-11],INVMUL!C[-11]:C[24],36,FALSE)),"""",VLOOKUP(R1C1&RC[-11],INVMUL!C[-11]:C[24],36,FALSE))"

'colonne M

c.Offset(0, 11).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-12],HISINV!C[-12]:C[40],53,FALSE)),"""",VLOOKUP(R1C1&RC[-12],HISINV!C[-12]:C[40],53,FALSE))"

'colonne N

c.Offset(0, 12).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-RC[-2])"



'colonne O

c.Offset(0, 13).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-14],INVMUL!C[-14]:C[20],35,FALSE)),"""",VLOOKUP(R1C1&RC[-14],INVMUL!C[-14]:C[20],35,FALSE))"


'colonne P

c.Offset(0, 14).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-15],HISINV!C[-15]:C[19],35,FALSE)),"""",VLOOKUP(R1C1&RC[-15],HISINV!C[-15]:C[19],35,FALSE))"


'colonne Q

c.Offset(0, 15).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]-RC[-2])"

'colonne R

c.Offset(0, 16).FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(R1C1&RC[-17],HISINV!C[-17]:C[-7],11,FALSE)),"""",VLOOKUP(R1C1&RC[-17],HISINV!C[-17]:C[-7],11,FALSE))"

'colonne S

c.Offset(0, 17).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(R1C1&RC[-18],INVCAH!C[-18]:C[18],37,FALSE)),"""",VLOOKUP(R1C1&RC[-18],INVCAH!C[-18]:C[18],37,FALSE))"

'colonne T

c.Offset(0, 18).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(R1C1&RC[-19],INVCAH!C[-19]:C[-5],15,FALSE)),"""",VLOOKUP(R1C1&RC[-19],INVCAH!C[-19]:C[-5],15,FALSE))"

End If
Next c

Sheets("Feuil1").Select

End Sub

USERFORM1

Code:

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Initialize()
Dim c As Workbook 'déclare la variable o (Classeur)

For Each c In Workbooks 'boucle sur tous les claeeurs ouverts
    'alimente la ListBox1 du nom des classeurs ouverts sauf le nom de ce classeur et "PERSO.XLS"
    If Left(c.Name, 6) = "hisinv" Then Me.ListBox1.AddItem c.Name
Next c 'prochain classeur ouvert de la boucle
End Sub

Private Sub OK_Click()
Dim cc As Workbook 'déclare la variable cc (Classeur Cible)
Dim oc As Object 'déclare la variable oc (Onglet Cible)
Dim x As Integer 'déclare la variable x (incrément)
Dim dest As Range 'déclare la variable dest (cellule de DESTination)
Dim cs As Workbook 'déclare la variable cs (Classeur Source)
Dim os As Object 'déclare la variable os (Onglet Source)

Set cc = ThisWorkbook 'définit le classeur cible
Set oc = cc.Sheets("bla") 'définit l'onglet cible
With Me.ListBox1 'prend en compte la ListBox1
    For x = 0 To .ListCount - 1 'boucle sur tous les éléments de la ListBox1
        If .Selected(x) = True Then 'condition : si l'élément est sélectionné
            'définit la cellule de destination (A1 de l'onglet cible si A1 est vide, sinon la première cellule vide de la colonne A)
            Set dest = IIf(oc.Range("B2") = "", oc.Range("B2"), oc.Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0))
            Set cs = Workbooks(.List(x)) 'définit le classeur source
            Set os = cs.Sheets(1) 'définit l'onglet source
            os.UsedRange.Copy dest 'copie la plage des cellules éditées de l'onglet source et la copie dans dest
        End If 'fin de la condition
    Next x 'prochain élément séolectionné de la boucle
End With 'fin de la prise en compte de la ListBox1
Unload Me 'vide et ferme l'Userform
oc.Activate 'sélectionne l'onglet cible
Sheets("Feuil1").Select
End Sub

Private Sub ANNULER_Click()
Unload Me 'vide et ferme l'Userform
End Sub

Staple1600 · 8 Août 2012

Re : Formule excel VBA

Re

J'ai omis de préciser que tu pouvais zipper ton fichier (ou tes fichiers). Désolé 😉
(Clic-droit sur le fichier -> Envoyer vers Dossiers compressés)
Tu obtiens un fichier *.zip (qui prendra beaucoup moins de place)

Je finis de boire l'apéro et je repasse plus tard.

tontonced · 8 Août 2012

Re : Formule excel VBA

Bon ben trop tard, j'ai tout supprimé j'espère que tu t'en sortiras 🙂

Bois un coup à ma santé, moi jsuis au taf 🙁

Edit :

En fait je l'ai retrouvé mais même zippé il est toujours trop gros

Effectuez une recherche sur Excel Downloads...

Formule excel VBA

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

Staple1600

XLDnaute Barbatruc

tontonced

XLDnaute Nouveau

tontonced

XLDnaute Nouveau

Pièces jointes

Staple1600

XLDnaute Barbatruc

tontonced

XLDnaute Nouveau

Discussions similaires

Nous accordons de l'importance à votre vie privée