Problème avec boucle tordue et la fonction Switch

[Magic_Doctor]

Bonjour,

Pour mieux saisir le sens de la macro que j'expose ici, je décris dabord le problème.

Je veux "traiter" 6 cellules qui se trouvent toutes sur la même ligne.
Pour ce faire, je décide de passer par une boucle.
Problème : les cellules en question ne sont pas toutes équidistantes : AE50, AO50, AY50, AZ50, BJ50, BK50.
Certaines cellules sont même contiguës.
J'ai 6 cellules, la boucle fera donc 6 tours : i = 1 To 6
Je connais le Nº de colonne de chacune de ces cellules :

i........col
1........31
2........41
3........49
4........55
5........59
6........63

Au moyen de la fonction "DROITEREG", j'essaie de trouver une fonction : f(i) = col
Malheureusement, la fonction ne marche pas à tous les coups et dans certains cas je dois réajuster.

Cette histoire-là, un tantinet rocambolesque, est uniquement pour le fun, mais ça peut toujours donner des idées...

La macro :

Sub SchizoBoucle()

Dim i As Byte, col As Variant, celda As Range, cellule As Range

'J'ai dû déclarer col comme Variant et non comme Integer à cause de Switch, sinon ça bloquait d'emblée à "Conditions par Switch"

Set celda = [AE50] 'une des cellules en question (servira uniquement à connaîre la ligne)

For i = 1 To 6
    col = Int(0.0833333333 * i ^ 3 - 1.6785714286 * i ^ 2 + 14.6666666667 * i + 18) 'fonction qui permet de calculer approximativement le N° de colonne des cellules
 
    'Conditions par IF :
    If i = 3 Then col = col + 2 'réajustement
    If i = 4 Then col = col - 3 'réajustement
    If i = 5 Then col = col + 3 'réajustement
 
    'Conditions par Switch :
    'col = Switch(i = 3, col + 2, i = 4, col - 3, i = 5, col + 3) 'réajustements

    Set cellule = Range(Split(Columns(col).Address(ColumnAbsolute:=False), ":")(1) & celda.Row) 'ne marche pas si l'on passe par "Switch"
    cellule = cellule.Formula '"traitement" de chaque cellule
Next
End Sub

Signalons au passage que s'il n'y avait pas de réajustements, ce serait magnifique pour régler bien des problèmes.

Pourquoi donc ça ne marche pas avec "Switch" (que je viens de connaître grâce à Staple1600) ?

J'en profite pour poser aussi cette question. Quand au moyen d'une macro on fait un copier-coller, la cellule qui est copiée est entourée de "traits d'union" qui scintillent. Je ne me souviens jamais de la sentence pour arrêter ce truc-là.

Merci d'avance pour tout commentaire.

 

[job75]

Re Magic_Doctor,

Si ça peut aider :

https://www.excel-downloads.com/threads/une-difficulte-avec-evaluate-resolu.20021438/page-2

A+

 

[Si...]

Bon_jour

En decrescendo (Ô caram ba !)

Sub Sw()
  For i = 1 To 6
      MsgBox Cells(50, Switch(i = 1, 31, i = 2, 41, i = 3, 51, i = 4, 52, i = 5, 61, i = 6, 62)).Address
  Next
End Sub

Pour la fonction Switch,
Résultat=Switch([condition 1,truc 1], [condition 2,truc 2], [ condition 3,truc 3],…)

Sub Ar()
  For i = 0 To 5
      MsgBox Cells(50, Array(31, 41, 51, 52, 61, 62)(i)).Address
  Next
End Sub

Sub Ra()
  For Each R In Range("AE50, AO50, AY50, AZ50, BJ50, BK50")
      MsgBox R.Address
  Next
End Sub

Sub Ev()
  For Each R In [AE50,AO50,AY50,AZ52,BJ50,BK50]
      MsgBox R.Address
  Next
End Sub

Nota : les colonnes ne sont pas identiques avec ce calcul

Sub N()
  For i = 1 To 6
      MsgBox Cells(50, Int(0.0833333333 * i ^ 3 - 1.6785714286 * i ^ 2 + 14.6666666667 * i + 18)).Address
  Next
End Sub

Je laisse le soin à Job qui contrôle souvent la vitesse de te fournir son radar !

 

[Dranreb]

Bonjour.
C'est pas Switch qui conviendrait pour ce genre de cas c'est Choose(i, 31, 41, 49, 55, 59, 63)

 

[Staple1600]

Bonsoir le fil, le forum

Quand on parle du loup, on en voit la queue
(Il manquait le Split)

Sub vE()
Dim t, i&, Z$
t = Split("AE AO AY AZ BJ BK")
For i = LBound(t) To UBound(t)
Z = Z & vbTab & Range(t(i) & 50).Address(0, 0) & vbCrLf
Next
MsgBox Z, vbInformation, "Pour le fun"
End Sub

 

[Magic_Doctor]

Bonjour,

D'abord merci à tous pour vos interventions.

Après avoir découvert la fonction "Switch", je découvre une nouvelle fonction "Choose".
Dans le cas d'une boucle où i augmente à chaque tour d'une unité, la fonction "Choose", indéniablement fait davantage l'affaire (syntaxe nettement plus simple).
Au départ je m'étais gouré en ne prenant pas en compte tous les i (ici, 6 au total).
Que ce soit "Choose" ou "Switch", j'en déduis, dans ce cas de figure, que ces fonctions n'ont pas grand intérêt.

Macro légèrement modifiée :

Sub SchizoBoucle()
Dim i As Byte, col As Byte, celda As Range, adresse As String, cellule As Range

Set celda = [AE50] 'une des cellules en question (servira uniquement à connaîre la ligne)

For i = 1 To 6
    col = Int(0.0833333333 * i ^ 3 - 1.6785714286 * i ^ 2 + 14.6666666667 * i + 18) 'fonction qui permet de calculer approximativement le N° de colonne des cellules
 
    'If i = 3 Then col = col + 2 'réajustement
    'If i = 4 Then col = col - 3 'réajustement
    'If i = 5 Then col = col + 3 'réajustement
 
    col = col + Choose(i, 0, 0, 2, -3, 3, 0) 'réajustements ---> ça marche : une valeur pour chaque i
 
    adresse = Split(Columns(col).Address(ColumnAbsolute:=False), ":")(1) & celda.Row 'adresse de la cellule qui doit ëtre "traitée"
    Set cellule = Range(adresse)
    cellule = cellule.Formula '"Traitement" de la cellule
Next
End Sub

Si... me signale, à raison, que les colonnes ne sont pas identiques avec le calcul par fonction "DROITEREG". C'est bien pour ça, qu'en REM dans la macro, je précise "fonction qui permet de calculer approximativement le N° de colonne des cellules".
C'est, du reste, la raison pour laquelle il faut faire des réajustements.
Donc tout ça n'est pas au point (si tant est que ce soit possible...) ; c'est bien pour ça que je disais que cette "stratégie" était uniquement pour le fun.
Cela va s'en dire que pour recalculer toutes les cellules en question, je choisis la solution de job.
Nonobstant, ce serait bien de trouver une solution sans réajustements à ce problème de boucle atypique. On peut toujours rêver...

En fait, le chronométrage du déroulement d'une macro est très simple (trouvé sur le Net) :

En début de la macro :
Start=timer

Avant End Sub
Msgbox "Durée du traitement : " & timer-start & " secondes"

J'ai comparé ma macro avec la routine de job :

        For Each c In [AE50,AO50,AY50,AZ52,BJ50,BK50]
          c = c.Formula
        Next

Le temps d'exécution pour les deux est # 0,95''

Je viens de voir la proposition de Staple1600, je vais étudier ça de plus près, je ne comprends pas encore toute la syntaxe.