XL 2013 2 code vba qui se suive

lestoiles1

XLDnaute Occasionnel
Bonjour à ts,

J'aimerais fusionner ces 2 codes dans une même feuille:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address

Case Is = "$C$2"

If Target.Value = "test" Then Range("F2").Value = Target.Value Else Range("F2").Value = "all"

Case Is = "$I$3"

If Target.Value = "test" Then Range("I4").Value = Target.Value Else Range("I4").Value = "all"

Case Is = "$I$2"

If Target.Value = "" Then Range("I3").Value = Target.Value Else Range("I3").Value = "all"

Case Is = "$I$4"

If Target.Value = "" Then Range("I5").Value = Target.Value Else Range("I5").Value = "all"

End Select

End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cel As Range

For Each Cel In Range("b71:b1000")

If Cel.Value <> "" And Cel.Value = 0 Then

Cel.EntireRow.Hidden = True

Else: Cel.EntireRow.Hidden = False

End If


Next

End Sub

Merci
 

vgendron

XLDnaute Barbatruc
Bonjour
un essai ici

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents=false
Select Case Target.Address
Case Is = "$C$2"
If Target.Value = "test" Then Range("F2").Value = Target.Value Else Range("F2").Value = "all"

Case Is = "$I$3"
If Target.Value = "test" Then Range("I4").Value = Target.Value Else Range("I4").Value = "all"

Case Is = "$I$2"
If Target.Value = "" Then Range("I3").Value = Target.Value Else Range("I3").Value = "all"

Case Is = "$I$4"
If Target.Value = "" Then Range("I5").Value = Target.Value Else Range("I5").Value = "all"
End Select
end if 'c'est le end de quel if?? n'aurais tu pas un bug sur cette ligne ?

Dim Cel As Range

For Each Cel In Range("b71:b1000")
If Cel.Value <> "" And Cel.Value = 0 Then
Cel.EntireRow.Hidden = True
Else: Cel.EntireRow.Hidden = False
End If
Next
application.enablevents=true
End Sub
 

lestoiles1

XLDnaute Occasionnel
Bonjour
un essai ici

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents=false
Select Case Target.Address
Case Is = "$C$2"
If Target.Value = "test" Then Range("F2").Value = Target.Value Else Range("F2").Value = "all"

Case Is = "$I$3"
If Target.Value = "test" Then Range("I4").Value = Target.Value Else Range("I4").Value = "all"

Case Is = "$I$2"
If Target.Value = "" Then Range("I3").Value = Target.Value Else Range("I3").Value = "all"

Case Is = "$I$4"
If Target.Value = "" Then Range("I5").Value = Target.Value Else Range("I5").Value = "all"
End Select
end if 'c'est le end de quel if?? n'aurais tu pas un bug sur cette ligne ?

Dim Cel As Range

For Each Cel In Range("b71:b1000")
If Cel.Value <> "" And Cel.Value = 0 Then
Cel.EntireRow.Hidden = True
Else: Cel.EntireRow.Hidden = False
End If
Next
application.enablevents=true
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim Cel As Range
For Each Cel In Range("AG77:AG5000")
If Cel.Value <> "" And Cel.Value = 0 Then
Cel.EntireRow.Hidden = True
Else: Cel.EntireRow.Hidden = False
Application.ScreenUpdating = True
End If
Next
Select Case Target.Address
Case Is = "$C$2"
If Target.Value = "patrice" Then Range("F2").Value = Target.Value Else Range("F2").Value = "All"
Case Is = "$I$3"
If Target.Value = "patrice" Then Range("I4").Value = Target.Value Else Range("I4").Value = "all"
Case Is = "$I$2"
If Target.Value = "patrice" Then Range("I3").Value = Target.Value Else Range("I3").Value = "all"
Case Is = "$I$4"
If Target.Value = "patrice" Then Range("I5").Value = Target.Value Else Range("I5").Value = "all"
End Select
Application.EnableEvents = True
End Sub
J'ai fait comme ça mais le temps d'execution est encore lente, pourrait-tu me donner une autre suggestion? Merci
 

job75

XLDnaute Barbatruc
S'il n'y a pas de formules en AG77:AG5000 vous pouvez utiliser cette macro, très rapide :
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
With Range("AG77:AG5000")
    .Rows.Hidden = False
    If Application.CountIf(.Cells, 0) Then
        .Replace 0, "#N/A", xlWhole
        .SpecialCells(xlCellTypeConstants, 16).EntireRow.Hidden = True
        .Replace "#N/A", 0
    End If
End With
'---suite du code---
Application.EnableEvents = True
End Sub
 

job75

XLDnaute Barbatruc
Autre solution, très rapide aussi, avec ou sans formules en AG77:AG5000 :
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
With Range("AG77:AG5000")
    .Rows.Hidden = False
    If Application.CountIf(.Cells, 0) Then
    .EntireColumn.Insert 'insère une colonne auxiliaire
    With .Columns(0)
        .Formula = "=1/(""""&RC[1]=""0"")"
        .SpecialCells(xlCellTypeFormulas, 1).EntireRow.Hidden = True
        .EntireColumn.Delete 'supprime la colonne auxiliaire
    End With
    End If
End With
'---suite du code---
Application.EnableEvents = True
End Sub
 

vgendron

XLDnaute Barbatruc
Hello tous
Pouvez m'expliquer pourquoi et me donner la solution svp
au hasard, il te manque un application.enableevents=true
les macros ne fonctionnent plus (= ne sont plus lancées) parce que les évènements ne sont plus actifs..
mais comme le dit @job75 : on ne connait pas ton fichier.. argggg;. ces gens qui ne mettent pas leur fichier exemple.... :-D (private joke)
 

Discussions similaires

Réponses
1
Affichages
283
Réponses
1
Affichages
410

Statistiques des forums

Discussions
315 098
Messages
2 116 189
Membres
112 679
dernier inscrit
Yupanki