VBA formule si à étendre

Version imprimable

Voir 40 message(s) de cette discussion en une page

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:

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 !

09/08/2017, 18h32
mjpmjp

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
09/08/2017, 18h42
mjpmjp

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

Citation:

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

@+JP

re,
ou alors , avec VBA :

Code:

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

10/08/2017, 09h51
Samsams

Merci JP

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 !

bonjour,
j'avais pas compris :aie:
si tu veux mettre 1,2,3

Code:

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

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:

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

10/08/2017, 10h55
mjpmjp

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

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
10/08/2017, 11h04
Samsams

jp

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....
10/08/2017, 11h17
mjpmjp

re,

Citation:

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

explique : ???

Citation:

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

une boucle ???

Citation:

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 : ???

Citation:

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

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

explique : ???

@+JP
10/08/2017, 11h29
Samsams

jp

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 !
10/08/2017, 12h19
mjpmjp

bonjour,
non , il n'y a pas de bêtise , ;)
c'est juste que je ne comprend pas obligatoirement le code :mouarf:

par exemple

Citation:

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

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

par exemple

Citation:

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

ICI tu vise la colonne G

Citation:

ActiveCell.FormulaR1C1 = "Type 1"

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

donc

Citation:

Range (F:F)= Val(Nombre)

tu veux faire quoi exactement ?

@+JP
10/08/2017, 12h31
Samsams

jp

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 !
10/08/2017, 13h11
mjpmjp
bonjour,
j'ai utilisé l'enregistreur de macros :
Code:

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

bonjour,
j'ai testé , çà fonctionne

Code:

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

10/08/2017, 14h51
Samsams

merci

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 !

Colorer ligne avec conditions si

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:

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

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 8O
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:

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

10/08/2017, 16h26
Samsams

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...

1 pièce(s) jointe(s)

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:

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

Pièce jointe 302094

@+JP

Voir 40 message(s) de cette discussion en une page