exclure des cellules d'une boucle

[matteopacino]

Bonjour ä tous,

j'ai une macro qui fonctionne très bien dont je poste le code en dessous.

Dim c As Range
Dim nb As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(ws_name)
nb = ws.Range("B" & Rows.Count).End(xlUp).Row

For Each c In ThisWorkbook.Sheets(ws_name).Range("U" & fisrtChk & ":U" & nb).Cells 'myRange.Cells
With ThisWorkbook.Sheets(ws_name).Labels.Add(c.Left, c.Top, c.Width, c.Height)
.Name = c.Address
.Caption = ""
.OnAction = "'U_TOC_ch """ & c.Address & """, """ & ws.Name & """'"
.Locked = True
.LockedText = False
.Placement = xlMoveAndSize
.PrintObject = True
End With
c.Value = 1
Next


Ma question est comment réussir à exclure certaines cellules de la colonne U de cette boucle ? par exemple "U164"

Merci

Cordialement

 

[youky(BJ)]

Re : exclure des cellules d'une boucle

Salut bien,
exemple:
For Each c In ThisWorkbook.Sheets(ws_name).Range("U" & fisrtChk & ":U" & nb).Cells 'myRange.Cells
If c.row=164 Or c.row=166 then goto saute 'ici on saute 164 et 166
With ThisWorkbook.Sheets(ws_name).Labels.Add(c.Left, c.Top, c.Width, c.Height)
.Name = c.Address
.Caption = ""
.OnAction = "'U_TOC_ch """ & c.Address & """, """ & ws.Name & """'"
.Locked = True
.LockedText = False
.Placement = xlMoveAndSize
.PrintObject = True
End With
c.Value = 1
saute:
Next

Bruno

 

[mapomme]

Re : exclure des cellules d'une boucle

Bonjour matteopacino,

Un code de ce type: ?

Dim c As Range
Dim nb As Long
Dim ws As Worksheet
Const Exclusion = "-164-66-88-132-"

Set ws = ThisWorkbook.Sheets(ws_name)
nb = ws.Range("B" & Rows.Count).End(xlUp).Row

For Each c In ThisWorkbook.Sheets(ws_name).Range("U" & fisrtChk & ":U" & nb).Cells 'myRange.Cells
If InStr(Exclusion, "-" & c.row & "-") = 0 Then
  With ThisWorkbook.Sheets(ws_name).Labels.Add(c.Left, c.Top, c.Width, c.Height)
  .Name = c.Address
  .Caption = ""
  .OnAction = "'U_TOC_ch """ & c.Address & """, """ & ws.Name & """'"
  .Locked = True
  .LockedText = False
  .Placement = xlMoveAndSize
  .PrintObject = True
  End With
  c.Value = 1
End If
Next

 

[matteopacino]

Re : exclure des cellules d'une boucle

Merci beaucoup ä vous deux !

je n'ai pas eu le temps de tester la premiüre solution,

uniquement la deuxième, elle marche très bien !

merci