Sélection d'une plage de cellule non contigue
Bonjour!
J'ai fait un petit programme dans lequel j'utilise la fonction <<Range>> pour sélectionner une plage de cellule non contiguë..
Le problème c'est que le programme sélectionne de la première à la dernière cellule sans prendre en compte les " , " et les " : "
Je pense que mon problème vient de la sélection au niveau du " if " mais je ne vois pas d'où exactement...
Voici mon code:
If Optionbutton1 = True And Optionbutton2 = False And (Me.ComboBox1.Text = "1" Or Me.ComboBox1.Text = "5" Or Me.ComboBox1.Text = "9" Or Me.ComboBox1.Text = "13" Or Me.ComboBox1.Text = "17" Or Me.ComboBox1.Text = "21" Or Me.ComboBox1.Text = "25" Or Me.ComboBox1.Text = "29" Or Me.ComboBox1.Text = "33" Or Me.ComboBox1.Text = "37" Or Me.ComboBox1.Text = "41" Or Me.ComboBox1.Text = "45" Or Me.ComboBox1.Text = "49") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Union(Range("B3"), Range("B4:B7"), Range("B10:B11")).Select
Selection.Copy
TextBox8.Paste
TextBox9.MultiLine = True
Union(Range("C3"), Range("C4:C7"), Range("C10:C11")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D3"), Range("D4:D7"), Range("D10:D11")).Select
Selection.Copy
TextBox10.Paste
End If
Set sh = Sheets("Feuil1")
If Optionbutton2 = True And Optionbutton1 = False And (Me.ComboBox1.Text = "1" Or Me.ComboBox1.Text = "5" Or Me.ComboBox1.Text = "9" Or Me.ComboBox1.Text = "13" Or Me.ComboBox1.Text = "17" Or Me.ComboBox1.Text = "21" Or Me.ComboBox1.Text = "25" Or Me.ComboBox1.Text = "29" Or Me.ComboBox1.Text = "33" Or Me.ComboBox1.Text = "37" Or Me.ComboBox1.Text = "41" Or Me.ComboBox1.Text = "45" Or Me.ComboBox1.Text = "49") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Union(Range("B2"), Range("B8:B9"), Range("B13"), Range("B15"), Range("B16")).Select
Selection.Copy
TextBox8.Paste
TextBox9.MultiLine = True
Union(Range("C2"), Range("C8:C9"), Range("C13"), Range("C15"), Range("C16")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D2"), Range("D8:D9"), Range("D13"), Range("D15"), Range("D16")).Select
Selection.Copy
TextBox10.Paste
End If
Merci pour avoir prit le temps de lire mon message!
bonjour,
Un Range est un objet
Les TextBox comme leur nom l'indique ne peuvent contenir que du texte :
Tu ne peux pas coller un objet dans un TextBox.
Sinon... sur un plan purement syntaxique
Union(Range("B3"), Range("B4:B7")
c'est Range("B3:B7")
et par suite on écrirait
Range("B3:B7", B10:B11").Copy
A+
Merci de votre réponse!
Comment je pourrais m'y prendre alors?
Relire mon dernier message... Quand on ne peut pas : ON NE PEUT PAS.
Sinon,un petit classeur joint avec un exemple du résultat à obtenir est nécessaire..
Sinon... sur un plan purement syntaxique
[Inline]Union(Range("B3"), Range("B4:B7")[/Inline] c'est [Inline]Range("B3:B7")[/Inline]
et par suite on écrirait
[Inline]Range("B3:B7", B10:B11").Copy[/Inline]
C'est ce que j'avais fait mais comme je pensais que le problème pouvait peut être venir de là, mais je pensais mal
Voila
C'est pourtant bientôt midi... J'ai du laisser trainer mes luettes et neurones au plumard :
Je ne vois aucune macro correspondante aucun Optionbutton, aucun TextBox ?
... Je n'avais pas vu que c'est du MAC : Je passe la main aux spécialistes !
A+
Pardon tout le code est dans l'UF...
Je ne peux pas importer l'UF tel quel du coup voici le code:
Option Compare Text
Dim f 'Création de la variable "f" qui servira pour le "tri" des "Semaine"
Dim g 'Création de la variable "g" qui servira dans le sub "tri"
Dim ini As String 'Variable servant à 'vider' les txt box à chaque changement
Private Sub CommandButton1_Click()
Dim a As String
a = Empty
TextBox8 = a
TextBox9 = a
TextBox10 = a
End Sub
Private Sub CommandButton2_Click() ' Bouton OK, Tranfert des valeurs, fermeture du UF(si nécessaire) et vidage du UF pour libérer de la mémoire.
ActiveWorkbook.Worksheets("Feuil2").Select
Range("A1:F1").Select
Selection.Locked = True
If OPERATEUR.Value = True Then
ActiveWorkbook.Worksheets("Feuil2").Range("A1").Value = "Opé"
Else: ActiveWorkbook.Worksheets("Feuil2").Range("A1").Value = "Maint"
End If
ActiveWorkbook.Worksheets("Feuil2").Range("B1").Value = Textbox1.Value ' Transfère la valeur du texbox1 vers la cellule B1
ActiveWorkbook.Worksheets("Feuil2").Range("C1").Value = TextBox2.Value ' Transfère la valeur du texbox2 vers la cellule C1
ActiveWorkbook.Worksheets("Feuil2").Range("D1").Value = TextBox3.Value ' Transfère la valeur du texbox3 vers la cellule D1
ActiveWorkbook.Worksheets("Feuil2").Range("B2").Value = ComboBox1.Value ' Transfère la valeur de la ligne cliquée sur le ListBox vers la cellule F1
ActiveWorkbook.Worksheets("Feuil2").Range("E1").Value = TextBox4.Value ' Transfère la valeur du texbox4 vers la cellule G1
ActiveWorkbook.Worksheets("Feuil2").Range("F1").Value = TextBox6.Value ' Transfère la valeur du texbox6 vers la cellule H1
ActiveWorkbook.Worksheets("Feuil2").Range("C2").Value = TextBox8.Value ' Transfère la valeur du texbox1 vers la cellule c2
ActiveWorkbook.Worksheets("Feuil2").Range("D2").Value = TextBox9.Value ' Transfère la valeur du texbox1 vers la cellule D2
ActiveWorkbook.Worksheets("Feuil2").Range("E2").Value = TextBox10.Value ' Transfère la valeur du texbox2 vers la cellule E2
ActiveWorkbook.Worksheets("Feuil2").Range("F2").Value = TextBox11.Value ' Transfère la valeur du texbox2 vers la cellule F2
Columns("A:A").Select
Selection.ColumnWidth = 5
Range("A1").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 12
End With
Range("A2").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 18
End With
Columns("B:B").Select
Selection.ColumnWidth = 7
Range("B1").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 12
End With
Range("B2").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 18
End With
Columns("D:D").Select
Selection.ColumnWidth = 95
Range("D1").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 12
End With
Range("D2").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 18
End With
Columns("C:C").Select
Selection.ColumnWidth = 27
Range("C1").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 12
End With
Range("C2").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 18
End With
Columns("E:E").Select
Selection.ColumnWidth = 11
Range("E1").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 12
End With
Range("E2").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 18
End With
Columns("F:F").Select
Selection.ColumnWidth = 32
Range("F1").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 12
End With
Range("F2").Select
With Selection.Font
.Name = "Calibri (Corps du thème)"
.Size = 12
End With
Sheets("Feuil2").Select
Range("A1:F2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
Range("C2:E2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 1
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1:F2").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
Selection.PrintOut From:=1, To:=1, Copies:=1
End With
End Sub
Public Sub UserForm_Initialize() 'Création d'un Sub pour l'userform qui s'exécutera lors de l'initialisation
Textbox1 = Sheets("Feuil1").Range("A1") 'intégration dans une textbox, la 1, du texte présent sur la feuil1 dans la rangée "A1"
With Me.Textbox1
.Text = Range("A1").Text 'Je précise ici qu'il faut prendre en compte la case en format Text
End With
TextBox2 = Sheets("Feuil1").Range("B1") 'intégration dans une textbox, la 2, du texte présent sur la feuil1 dans la rangée "B1"
With Me.TextBox2
.Text = Range("B1").Text 'Je précise ici qu'il faut prendre en compte la case en format Text
End With
TextBox3 = Sheets("Feuil1").Range("C1")
With Me.TextBox3
.Text = Range("C1").Text
End With
TextBox4 = Sheets("Feuil1").Range("D1")
With Me.TextBox4
.Text = Range("D1").Text
End With
TextBox6 = Sheets("Feuil1").Range("F1")
With Me.TextBox6
.Text = Range("F1").Text
.MultiLine = False
.AutoSize = False
End With
Set f = Sheets("Feuil1") 'SET Attribue une référence d’objet à une variable ou une propriété, ici f=Feuil1
Dim a() 'Déclaration de la variable a
a = Application.Transpose(f.Range("A2:A" & f.[A65000].End(xlUp).Row).Value) 'Définition de la variable a et de son utilisation
Me.ComboBox1.List = SansDoublonsTriéMAC(a()) 'Affectation de la fonction 'SansDaoublonsTriéMAC(a))' à la combobox numéro 1
End Sub
Function SansDoublonsTriéMAC(a()) 'Création de la fonction 'SansDoublonsTriéMAC(a())'
Call Tri(a, LBound(a), UBound(a)) 'Transfert le contrôle au Sub nommée 'TRI'
Dim B(): ReDim B(1 To UBound(a)) 'Définition de la variable b
i = 1: J = 0
Do While i <= UBound(a) ' )
J = J + 1: B(J) = a(i) ' ) Création d'un tableur
Do While a(i) = B(J) ' )
i = i + 1: If i > UBound(a) Then Exit Do ' )
Loop ' )
Loop ' )
ReDim Preserve B(1 To J) ' ) Redimensionnement en gardant les valeurs précedemment comprises dans le 'tableur'
SansDoublonsTriéMAC = Application.Transpose(B) ' )
End Function
Sub Tri(a, gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2) 'Définition de 'ref'
g = gauc: d = droi 'Définition de 'g'
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
temp = a(g): a(g) = a(d): a(d) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call Tri(a, g, droi)
If gauc < d Then Call Tri(a, gauc, d)
End Sub
Public Sub ComboBox1_Change()
'1iere semaine du mois
Dim p As String
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
Set sh = Sheets("Feuil1")
If OPERATEUR = True And MAINTENANCE = False And (Me.ComboBox1.Text = "1" Or Me.ComboBox1.Text = "5" Or Me.ComboBox1.Text = "9" Or Me.ComboBox1.Text = "13" Or Me.ComboBox1.Text = "17" Or Me.ComboBox1.Text = "21" Or Me.ComboBox1.Text = "25" Or Me.ComboBox1.Text = "29" Or Me.ComboBox1.Text = "33" Or Me.ComboBox1.Text = "37" Or Me.ComboBox1.Text = "41" Or Me.ComboBox1.Text = "45" Or Me.ComboBox1.Text = "49") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Union(Range("B3"), Range("B4:B7"), Range("B10:B11")).Select
Selection.Copy
ComboBox2.Paste
TextBox9.MultiLine = True
Union(Range("C3"), Range("C4:C7"), Range("C10:C11")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D3"), Range("D4:D7"), Range("D10:D11")).Select
Selection.Copy
TextBox10.Paste
End If
Set sh = Sheets("Feuil1")
If MAINTENANCE = True And OPERATEUR = False And (Me.ComboBox1.Text = "1" Or Me.ComboBox1.Text = "5" Or Me.ComboBox1.Text = "9" Or Me.ComboBox1.Text = "13" Or Me.ComboBox1.Text = "17" Or Me.ComboBox1.Text = "21" Or Me.ComboBox1.Text = "25" Or Me.ComboBox1.Text = "29" Or Me.ComboBox1.Text = "33" Or Me.ComboBox1.Text = "37" Or Me.ComboBox1.Text = "41" Or Me.ComboBox1.Text = "45" Or Me.ComboBox1.Text = "49") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Union(Range("B2"), Range("B8:B9"), Range("B13"), Range("B15"), Range("B16")).Select
Selection.Copy
TextBox8.Paste
TextBox9.MultiLine = True
Union(Range("C2"), Range("C8:C9"), Range("C13"), Range("C15"), Range("C16")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D2"), Range("D8:D9"), Range("D13"), Range("D15"), Range("D16")).Select
Selection.Copy
TextBox10.Paste
End If
'2 ième semaine du mois
Set sh = Sheets("Feuil1")
If OPERATEUR = True And MAINTENANCE = False And (ComboBox1.Value = "2" Or ComboBox1.Text = "6" Or ComboBox1.Text = "10" Or ComboBox1.Text = "14" Or Me.ComboBox1.Text = "18" Or Me.ComboBox1.Text = "22" Or Me.ComboBox1.Text = "26" Or Me.ComboBox1.Text = "30" Or Me.ComboBox1.Text = "34" Or Me.ComboBox1.Text = "38" Or Me.ComboBox1.Text = "42" Or Me.ComboBox1.Text = "46" Or Me.ComboBox1.Text = "50") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Union(Range("B18:B21"), Range("B26:B26")).Select
Selection.Copy
TextBox8.Paste
TextBox9.MultiLine = True
Union(Range("C18:C21"), Range("C26:C26")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D18:D21"), Range("D26:D26")).Select
Selection.Copy
TextBox10.Paste
End If
Set sh = Sheets("Feuil1")
Sheets("Feuil1").Activate
If MAINTENANCE = True And OPERATEUR = False And (Me.ComboBox1.Value = "2" Or Me.ComboBox1.Text = "6" Or Me.ComboBox1.Text = "10" Or Me.ComboBox1.Text = "14" Or Me.ComboBox1.Text = "18" Or Me.ComboBox1.Text = "22" Or Me.ComboBox1.Text = "26" Or Me.ComboBox1.Text = "30" Or Me.ComboBox1.Text = "34" Or Me.ComboBox1.Text = "38" Or Me.ComboBox1.Text = "42" Or Me.ComboBox1.Text = "46" Or Me.ComboBox1.Text = "50") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Union(Range("B17"), Range("B22:B25")).Select
Selection.Copy
TextBox8.Paste
TextBox9.MultiLine = True
Union(Range("C17"), Range("C22:C25")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D17"), Range("D22:D25")).Select
Selection.Copy
TextBox10.Paste
End If
'3 ième semaine du mois
Set sh = Sheets("Feuil1")
If OPERATEUR = True And MAINTENANCE = False And (ComboBox1.Text = "3" Or Me.ComboBox1.Text = "7" Or Me.ComboBox1.Text = "11" Or Me.ComboBox1.Text = "15" Or Me.ComboBox1.Text = "19" Or Me.ComboBox1.Text = "23" Or Me.ComboBox1.Text = "27" Or Me.ComboBox1.Text = "31" Or Me.ComboBox1.Text = "35" Or Me.ComboBox1.Text = "39" Or Me.ComboBox1.Text = "43" Or Me.ComboBox1.Text = "47" Or Me.ComboBox1.Text = "51") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Union(Range("B28"), Range("B30"), Range("B37")).Select
Selection.Copy
TextBox8.Paste
TextBox9.MultiLine = True
Union(Range("C28"), Range("C30"), Range("C37")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D28"), Range("D30"), Range("D37")).Select
Selection.Copy
TextBox10.Paste
End If
Set sh = Sheets("Feuil1")
If MAINTENANCE = True And OPERATEUR = False And (ComboBox1.Text = "3" Or ComboBox1.Text = "7" Or Me.ComboBox1.Text = "11" Or Me.ComboBox1.Text = "15" Or Me.ComboBox1.Text = "19" Or Me.ComboBox1.Text = "23" Or Me.ComboBox1.Text = "27" Or Me.ComboBox1.Text = "31" Or Me.ComboBox1.Text = "35" Or Me.ComboBox1.Text = "39" Or Me.ComboBox1.Text = "43" Or Me.ComboBox1.Text = "47" Or Me.ComboBox1.Text = "51") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Union(Range("B27"), Range("B29"), Range("B31:B36"), Range("B38")).Select
Selection.Copy
TextBox8.Paste
TextBox9.MultiLine = True
Union(Range("C27"), Range("C29"), Range("C31:C36"), Range("C38")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D27"), Range("D29"), Range("D31:D36"), Range("D38")).Select
Selection.Copy
TextBox10.Paste
End If
'4 ième semaine du mois
Set sh = Sheets("Feuil1")
If OPERATEUR = True And MAINTENANCE = False And (ComboBox1.Text = "4" Or Me.ComboBox1.Text = "8" Or Me.ComboBox1.Text = "12" Or Me.ComboBox1.Text = "16" Or Me.ComboBox1.Text = "20" Or Me.ComboBox1.Text = "24" Or Me.ComboBox1.Text = "28" Or Me.ComboBox1.Text = "32" Or Me.ComboBox1.Text = "36" Or Me.ComboBox1.Text = "40" Or Me.ComboBox1.Text = "44" Or Me.ComboBox1.Text = "48" Or Me.ComboBox1.Text = "52") Then
Sheets("Feuil1").Activate
TextBox8.MultiLine = True
Range("B40,B42,B43,B44,B48,B49").Copy
Me.TextBox8.Paste
TextBox9.MultiLine = True
Range("C40,C42,C43,C44,C48,C49").Copy
TextBox9.Paste
TextBox10.MultiLine = True
Range("D40,D42,D43,D44, D48,D49").Copy
TextBox10.Paste
End If
Set sh = Sheets("Feuil1")
If MAINTENANCE = True And OPERATEUR = False And (Me.ComboBox1.Text = "4" Or Me.ComboBox1.Text = "8" Or Me.ComboBox1.Text = "12" Or Me.ComboBox1.Text = "16" Or Me.ComboBox1.Text = "20" Or Me.ComboBox1.Text = "24" Or Me.ComboBox1.Text = "28" Or Me.ComboBox1.Text = "32" Or Me.ComboBox1.Text = "36" Or Me.ComboBox1.Text = "40" Or Me.ComboBox1.Text = "44" Or Me.ComboBox1.Text = "48" Or Me.ComboBox1.Text = "52") Then
Sheets("Feuil1").Activate
Me.TextBox8.MultiLine = True
Union(Range("B39"), Range("B41"), Range("B45:B47"), Range("B50:B51")).Select
Selection.Copy
TextBox8.Paste
TextBox9.MultiLine = True
Union(Range("C39"), Range("C41"), Range("C45:C47"), Range("C50:C51")).Select
Selection.Copy
TextBox9.Paste
TextBox10.MultiLine = True
Union(Range("D39"), Range("D41"), Range("D45:D47"), Range("D50:D51")).Select
Selection.Copy
TextBox10.Paste
End If
Worksheets("Feuil2").Cells(7, 7).Value = TextBox11.Text
End Sub
Ça ne change rien à mon problème : Pas d'optionButton, pas de TextBox, aucun rapport avec la question initiale...
De toute façon c'est du MAC : Je passe la main aux spécialistes !
A+