VBA formule si à étendre

**Samsams** · 09/08/2017, 16h59

Bonjour à tous,

Etant débutant en vba je n'arrive pas à me sortir de mon casse tête ...

L'idée est :

Dans une colonne A je dispose de différents noms,
je souhaite que dans une colonne E on affiche :
*1 lorsque le nom commence par ABC
*2 lorsque le nom finit par ENT
*3 pour les autres cas
Je souhaite étendre cette formule jusqu'à la dernière ligne.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Sub Hyp()
For i = 21 To DernLigne
 
Next
Cells(i, 1) = IIf((Left(Cells(i, 5), 3) = "ABC"), "1", IIf((Right(Cells(i, 5), 3) = "ENT"), "2", "3"))
 
DernLigne = Range("E" & Rows.Count).End(xlUp).Row
Range("A21").AutoFill Destination:=Range("A21:A" & DernLigne)
 
End Sub

Mais ça ne fonctionne pas ...

Merci pour votre aide !

**mjpmjp** · 09/08/2017, 18h32

bonjour,
tu mélange tout !!!

tu n'as pas besoin d'écrire une formule dans des cellules puisque tu traite (affiche) en direct avec VBA

si tu veux vraiment écrire une formule style : "=SI(..." utilise ".FormulaLocal" avec VBA ou en direct avec Excel

autre chose ,
1) il n'y a rien dans ta boucle
2) tu utilise la variable DernLigne et la ligne de code qui la détermine est après

@+JP

**mjpmjp** · 09/08/2017, 18h42

bonjour,
donc en direct dans excel , dans la cellule E21
tu écris cette formule :

=SI(GAUCHE($A21;3)="ABC";SI(DROITE(A21;3)="ENT";$A21;"");"")

@+JP

**mjpmjp** · 09/08/2017, 18h49

re,
ou alors , avec VBA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
Private Sub Hyp()
    DernLigne = Range("A" & Rows.Count).End(xlUp).Row
    For i = 21 To DernLigne
        Range("E" & i).FormulaLocal = "=SI(GAUCHE($A" & i & ";3)=" & Chr(34) & "ABC" & Chr(34) & ";SI(DROITE(A" & i & ";3)=" & Chr(34) & "ENT" & Chr(34) & ";$A" & i & ";" & Chr(34) & Chr(34) & ");" & Chr(34) & Chr(34) & ")"
    Next
End Sub

@+JP

**Samsams** · 10/08/2017, 09h51

Salut JP,

Merci pour tes réponses.

Je passe par VBA car j'ai déjà une première macro qui vient m'extraire des données d'un logiciel et je veux ajouter une info lorsque je lance la macro...

Je pense par contre, que ta formule Excel est incomplète ...

Ce serait plutôt ça :

SI(GAUCHE($A21;3)="ABC";1;SI(DROITE(A21;3)="ENT";2;3)) mais je n'arrive pas à le mettre en VBA.

Une question à quoi sert Chr(34) dans ta boucle VBA ?

Merci à toi !

**mjpmjp** · 10/08/2017, 10h07

bonjour,
j'avais pas compris

si tu veux mettre 1,2,3

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
Private Sub Hyp()
    DernLigne = Range("A" & Rows.Count).End(xlUp).Row
    For i = 21 To DernLigne
        Range("E" & i).FormulaLocal = "=SI(GAUCHE($A" & i & ";3)=" & Chr(34) & "ABC" & Chr(34) & ";1;SI(DROITE(A" & i & ";3)=" & Chr(34) & "ENT" & Chr(34) & ";2;3))"
    Next
End Sub

chr(34) représente "
c'est le " qui doit être vu dans la formule

@+JP

**Samsams** · 10/08/2017, 10h39

MErci JPPPPPP

Par contre il m'affiche la formule au lieu de m'afficher le résultat car le format des cellules est en texte je présume. Il faut ajouter Range (F:F)= Val(Nombre) ?
Le code pour ajouter les colonnes est le suivant :

(J'ai 23 blocs de Selection.End car j'ai 23 colonnes)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Sub format()
'Rajoute les colonnes manquantes
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight
 
        Range("F1").End(xlDown).Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "Type 1"
 
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "Type 2"
 
        ActiveCell.Offset(0, 4).Select
        ActiveCell.FormulaR1C1 = "Type 3"
        With Selection
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
 
 
        Range("F13:L13").Select
        Selection.Copy
 
 
 
End Sub

Merci

**mjpmjp** · 10/08/2017, 10h55

bonjour,
j'ai testé le code , il fonctionne correctement

pour ton dernier post :
en dessous , clique sur modifier...

efface tes 23 selection...

sélectionne tout le code : de sub à end

et clique sur l'icone "#"

@+JP

**Samsams** · 10/08/2017, 11h04

En fait il y a un code pour ajouter 3 colonnes cf le post précédent et a priori ça m'ajoute des colonnes en texte...
Donc ça m'affiche =SI....

**mjpmjp** · 10/08/2017, 11h17

re,

Il faut ajouter Range (F:F)= Val(Nombre) ?

explique : ???

(J'ai 23 blocs de Selection.End car j'ai 23 colonnes)

une boucle ???

Range("F1").End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Type 1"

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Type 2"

ActiveCell.Offset(0, 4).Select
ActiveCell.FormulaR1C1 = "Type 3"

explique : ???

Range("F13:L13").Select
Selection.Copy

Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveSheet.Paste

explique : ???

@+JP

**Samsams** · 10/08/2017, 11h29

Pour le
Il faut ajouter Range (F:F)= Val(Nombre) ?
C'est ce que j'ai vu sur internet mais au vu de ta réponse c'est une bêtise

Je pensais que les
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveSheet.Paste
représentaient les 23 colonnes de mon tableau...

Pour ce qui est de ce morceau :

Range("F1").End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Type 1"

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Type 2"

ActiveCell.Offset(0, 4).Select
ActiveCell.FormulaR1C1 = "Type 3"

Il s'agit de donner un nom aux colonnes ajoutées

Range("F13:L13").Select
Selection.Copy

Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveSheet.Paste

Je pense que cette partie ne sert à rien !

**mjpmjp** · 10/08/2017, 12h19

bonjour,
non , il n'y a pas de bêtise ,

c'est juste que je ne comprend pas obligatoirement le code

par exemple

Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveSheet.Paste

pourquoi 2 fois : Selection.End(xlDown).Select ?

par exemple

Range("F1").End(xlDown).Offset(0, 1).Select

ICI tu vise la colonne G

ActiveCell.FormulaR1C1 = "Type 1"

FormulaR1C1 : çà fait quoi ?
Range("F1").End(xlDown).Offset(0, 1).value ="Type 1" --> çà fonctionne ?

donc

Range (F:F)= Val(Nombre)

tu veux faire quoi exactement ?

@+JP

**Samsams** · 10/08/2017, 12h31

J'ai enlevé les Selection.End(xlDown).Select

Range("F1").End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Type 1"

Ca veut dire que je me place en F1 je vais jusqu'à la première ligne de mon tableau en F10
et je me décale d'une colonne pour inscrire en G1 "Type 1"

Du coup je suis en G10 donc je fais

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Type 2"

Pour me décaler en H10 et inscrire "Type 2"

Je fais pareil pour Type 3 en me décalant de 4 colonnes par rapport à la colonne H.

Ce que je veux faire c'est mettre le colonne G en format nombre.

Merci !

**mjpmjp** · 10/08/2017, 13h11

bonjour,
j'ai utilisé l'enregistreur de macros :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "0.00"

qui a transformé la colonne G en format nombre avec 2 décimales

MAIS
j'avais prévu un test : j'ai écris '1 ce qui est 1 en texte (alignement à gauche)
après la macros , le '1 n'a pas bougé !!! pourtant si je tape 1 il est bien aligné à droite avec 1,00
la colonne est bien formatée en nombre

donc si tu veux transformer '1 qui est du texte , il faut enlever le caractère n°1
replace("'1","'","") --> replace(contenu cellule, caractère ' , par rien "" )
ou
cells(??).value = right(contenu cellule , len(contenu cellule) -1)

@+JP

**mjpmjp** · 10/08/2017, 13h20

bonjour,
j'ai testé , çà fonctionne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
Private Sub CommandButton1_Click()
    For n = 1 To 40
        Cells(n, 7).Value = Replace(Cells(n, 7).Value, "'", "")
    Next n
End Sub

@+JP

**Samsams** · 10/08/2017, 14h51

C'est nickel merci beaucoup !
J'ai ajouté ça après la création de la colonne G

Columns("G:G").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0.00"

ET CA MARCHE !!

Merci pour ton temps !

**Samsams** · 10/08/2017, 15h16

JP !!

J'essaie de colorer les lignes de mon tableau en fonction d'une colonne qui renvoie la date.

Exemple si date de la colonne I>31/12/2017 colorer en gris
si date colonne I > 31/12/2018 colorer en bleu

J'ai tenté avec une date ce code qui ne fonctionne pas bien sur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Colour()
 
    Date_Fixe = "31/12/2017"
    DernLigne = Range("I" & Rows.Count).End(xlUp).Row
    For i = 3 To DernLigne
 
    If Cells(i, 9).Value > Date_Fixe Then
    Rows("i:i").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Next
 
End Sub

**mjpmjp** · 10/08/2017, 16h10

bonjour,
les "dates" (inclus les heures) sont en fait un seul chiffre (peu importe ce que tu vois)
ICI : Date_Fixe = "31/12/2017" est un texte , c'est la partie que tu vois

il faut que tu transformes ce "texte" en "Date Valeur"

voila de quoi s’amuser , n' hésites pas à modifier la date "31/07/2017" pour voir le résultat

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
Private Sub CommandButton1_Click()
    'Date_Fixe = #12/31/2017#
    Date_Fixe = DateValue("31/07/2017")
    If Date_Fixe > Date Then
        MsgBox Date_Fixe & " sup à " & Date
    Else
        MsgBox Date_Fixe & " inf à " & Date
    End If
End Sub

@+JP

**Samsams** · 10/08/2017, 16h26

Je comprends pas ton code.

Tu as codé que si la date d'aujourd'hui était supérieure ou inf à la date fixée alors on affichait un messagebox...

Je vois pas le rapport...

**mjpmjp** · 10/08/2017, 16h45

bonjour,
c'était pour t'expliquer le fonctionnement des dates

voila le code qui applique l'explication et qui utilise RGB pour la couleur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
Private Sub CommandButton2_Click()
    Date_Fixe = DateValue(Range("D3").Value)
    For n = 3 To 15
        DateListe = Cells(n, 2).Value
        If Date_Fixe > DateListe Then
            Cells(n, 2).Interior.Color = RGB(0, 255, 0) 'vert
        Else
            Cells(n, 2).Interior.Color = RGB(255, 0, 0) 'rouge
        End If
    Next n
End Sub

résultat

Nom : Capture.JPG
Affichages : 1004
Taille : 29,5 Ko

@+JP

VBA formule si à étendre

Macros et VBA Excel

Discussions similaires

Partager

Partager