simplifie une formule

[tinet]

bonjour à tous,

je souhaite dans mon fichier alléger une formule.
mon fichier comporte plusieurs classeurs.
Les classeurs qui sont uniquement concernés par cette macro sont numérotés de 1 à 32.
voici la formule que j'exécute pour chacun des classeurs.

Dim i As Long
    Sheets("1").Select
    ActiveSheet.Unprotect Password:="AZA"
    Columns("O:O").Select
    Selection.EntireColumn.Hidden = False
    
    For i = 12 To 1500
    If Range("a" & i) <> "" Then
    Range("Q" & i) = Range("C" & i)
    Else
    Exit For
    End If
    
        If Range("a" & i) <> "" Then
    Range("O" & i) = Range("D" & i)
    Else
    Exit For
    End If
    Next i
    
    Sheets("2").Select
    ActiveSheet.Unprotect Password:="AZA"
    Columns("O:O").Select
    Selection.EntireColumn.Hidden = False
    
    For i = 12 To 1500
    If Range("a" & i) <> "" Then
    Range("Q" & i) = Range("C" & i)
    Else
    Exit For
    End If
    
    If Range("a" & i) <> "" Then
    Range("O" & i) = Range("D" & i)
    Else
    Exit For
    End If
    Next i

cette formule et reproduit jusqu'aux classeurs 32.
Je pense que les connaisseurs pourront me trouver une solution plus légère.

 

[Excel-lent]

Re : simplifie une formule

Bonjour Tinet,

Lorsqu'on travail avec des feuilles en VBA, il y a deux façons de les appeler, par leurs noms (visible sur l'onglet) et par le numéro de l'onglet.

Cette dernière solution est la plus simple lorsqu'on veut faire des boucles.

Prenons ton cas comme exemple concret :

Si tes onglets nommés "1" à "32" sont les onglets numéro 4 à 36 (pour le cas où tu aurais trois onglets avant ces 32 onglets) cela te donnerais ceci :

Dim i As Long
Dim NbOnglet As Byte

For NbOnglet = 4 To 36
    Sheets(NbOnglet).Select
    ActiveSheet.Unprotect Password:="AZA"
    Columns("O:O").Select
    Selection.EntireColumn.Hidden = False
    
    For i = 12 To 1500
         If Range("a" & i) <> "" Then
              Range("Q" & i) = Range("C" & i)
          Else
              Exit For
          End If
    
          If Range("a" & i) <> "" Then
              Range("O" & i) = Range("D" & i)
          Else
              Exit For
          End If
    Next i

Next NbOnglet

Ce qui comme tu le constate t'évite de répéter 32 fois ton code (une fois par onglet).

Bonne après midi

Cordialement

 

[CBernardT]

Re : simplifie une formule

Bonjour à tous,

Si j'ai bien compris le projet, ce code devrait donner satisfaction :

Sub ListeFichiers()
Dim Dossier As Object, Fichier As Object, Chemin As String, I As Long, Ws As Byte

'Chemin du dossier à analyser (à adapter au besoin)
Chemin = ThisWorkbook.Path
'Attribue une référence d'objet à la variable
Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(Chemin)
For Each Fichier In Dossier.Files
If IsNumeric(Left(Fichier.Name, 1)) Or IsNumeric(Left(Fichier.Name, 2)) Then
Workbooks.Open Filename:=Fichier
With ActiveWorkbook
For Ws = 1 To 2
With .Sheets(Ws)
.Unprotect Password:="AZA"
.Columns("O:O").EntireColumn.Hidden = False
For I = 12 To 1500
If .Range("A" & I) <> "" Then
.Range("Q" & I) = .Range("C" & I)
Else
Exit For
End If
If .Range("A" & I) <> "" Then
.Range("O" & I) = .Range("D" & I)
Else
Exit For
End If
Next I
End With
Next Ws
Application.DisplayAlerts = False
.Close
Application.DisplayAlerts = True
End With
End If
Next Fichier
End Sub

 

[tinet]

Re : simplifie une formule

Bonjour,

merci pour ton aide.
Je prend note de travailler avec le numéro d'onglet
à l'exécution la formule se bloquait sur

Sheets(NbOnglet).Select

j'ai modifié (NbOnglet) par le nombre de feuille concernées.
ce qui donne

Sheets(32).Select

Suis-je dans le juste
apparemment cela fonctionne

 

[tinet]

Re : simplifie une formule

bonjour Cbernardt,

Je suis novice, et j'ai plus de mal à comprendre ton retour.

 

[Excel-lent]

Re : simplifie une formule

Bonsoir Tinet,

Ton fichier comporte combien d'onglet?

Pour connaître le numéro de tes onglets, voici une petit macro très pratique :

Sub NomsOnglets()

Dim NumOnglet As Integer
    
    For NumOnglet = 1 To Worksheets.Count
         Sheets(NumOnglet).Range("A1") = NumOnglet
    Next NumOnglet

End Sub

Cette macro écrira dans la cellule A1 de chacune des feuilles sont numéro d'onglet.

Bizarre que tu obtienne cette erreur!

Peut-être as-tu fais une erreur dans le numéro des onglets? La macro ci-dessus devrait t'aider à résoudre cela. Sinon essaye de remplacer "byte" par "Integer", mais comme à mon avis ton classeur doit avoir moins de 255 onglets, le problème ne doit pas être là.

Au faite, pour information tu n'es pas obligé de "sélectionner" les onglets avec lesquels tu travail. Cela permet à la macro de tourner plus vite.

A te lire

Cordialement

 

[tinet]

Re : simplifie une formule

Bonjour Excel-lent

j'ai trouvé la réponse à la formule.
Je ne suis pas un expert, mais c'est en forgeant qu'on devient forgeron.

Je réponds pour permettre aux autres utilisateurs de trouvés la solution.
La formule ne correspond pas aux numéros de feuilles comme tu me l'as indiqué
mais au positionnement de la feuille dans le fichier.
C'est-à-dire que si il y a des fichiers cachés, ce qui était mon cas positionné au démarrage la formule ne marche pas.
En clair on indique dans ta formule le numéro de position du premier classeur concerné ainsi que le dernier. Les classeurs bien sûrs doivent se suivre dans l'ordre.

À plus

 

[Excel-lent]

Re : simplifie une formule

La formule ne correspond pas aux numéros de feuilles comme tu me l'as indiqué
mais au positionnement de la feuille dans le fichier.

???

C'est pourtant bien ce que j'ai écrit.

Lorsqu'on travail avec des feuilles en VBA, il y a deux façons de les appeler, par leurs noms (visible sur l'onglet) et par le numéro de l'onglet.

(...)
Si tes onglets nommés "1" à "32" sont les onglets numéro 4 à 36 (pour le cas où tu aurais trois onglets avant ces 32 onglets)

Le nom de l'onglet : c'est le nom que tu donnes à ton onglet!

Le numéro de l'onglet : n'est pas le nom de l'onglet! Même si le nom de l'onglet est un nombre[/QUOTE]

Mais content que la petite macro ci-dessous t'a permis de le comprendre.

Sub NomsOnglets()

Dim NumOnglet As Integer

For NumOnglet = 1 To Worksheets.Count
Sheets(NumOnglet).Range("A1") = NumOnglet
Next NumOnglet

End Sub

Bon apprentissage

 

[Excel-lent]

Re : simplifie une formule

Bonsoir,

Si l'on joue sur les mots et les définitions :

C'est-à-dire que si il y a des fichiers cachés, ce qui était mon cas positionné au démarrage la formule ne marche pas.
En clair on indique dans ta formule le numéro de position du premier classeur concerné ainsi que le dernier. Les classeurs bien sûrs doivent se suivre dans l'ordre.

À plus

Il faut remplacer chaque mot "classeur" situé ci-dessus par le mot "feuille" ou "onglet". Non?

Bonne fin de soirée et sans rancune.

 

[kjin]

Re : simplifie une formule

Bonsoir le fil,
Histoire de mettre mon grain de sel...

cette formule et reproduit jusqu'aux classeurs 32.
Je pense que les connaisseurs pourront me trouver une solution plus légère.

Ceci n'est pas une formule mais un code VBA ou une macro
Un classeur contient des feuilles et non des classeurs, que l'on peut sélectionner via la barre des onglets
Les feuilles sont définies
- par leur nom de code (codeName) qui apparait dans la fenêtre propriétés des objets de VBE que l'on peut modifier soit manuellement, soit par le code au moment de l'ajout.
- par leur nom, celui qui apparait dans la barre des onglets du classeur que l'on peut toujours modifier
- par leur index, que l'on ne peut pas modifier, correspondant à la position des feuilles dans la barre d'onglets en partant de gauche, donc à utiliser avec prudence car si l'on vient à déplacer les feuilles...
A+
kjin

 

[Softmama]

lu tlm,

Ceci, plus rapide et plus court, est (sous réserve de le tester) équivalent au code du post #2

Sub test()
Dim NbOnglet As Byte, c as range
For NbOnglet = 4 To thisworkbook.sheets.count 'Boucle de la4ème feuille à la dernière
    with Sheets(NbOnglet)
        .Unprotect Password:="AZA" 'vire le mdp
        .Columns(15).Hidden = False 'masque colonne O
        with .Range("A12:A" & Range("A12").End(xlDown)) ' Pour la zone qui part de A12 jusqu'à temps que y ait un vide.
              .offset(0,16)=.offset(0,2).value 'colonne Q prend valeur de colonne C
              .offset(0,14)=.offset(0,3).value 'colonne O prend valeur de colonne D
        end with
    end With
Next NbOnglet