A partir de données de ma feuille excel je rempli des textbox dans un userform. Plus tard ce userform me permettra de selectionner les textbox que je souhaite utiliser.
Ensuite je souhaite remplir un deuxième tableau dans ma feuille excel en fonction des données figurant dans les textbox
Mon problème est que la variable "p" se remet à zéro après une boucle loop.
Voici mon code
Private Sub UserForm_Initialize()
Dim tboextract As Variant
Dim tboextract3 As Variant
Dim I
Dim Unseul As New Collection
Dim Unseul3 As New Collection
Dim strElt As Variant
Dim strElt3 As Variant
Dim j
Set f = ActiveSheet
'LISTE TEXTBOX
For n = 1 To 8
If f.Cells(2, n) <> "" Then
suite = suite & f.Cells(2, n) & ";"
End If
Next n
tboextract = Split(suite, ";")
On Error Resume Next
For I = 0 To UBound(tboextract)
Unseul.Add tboextract(I), tboextract(I)
Next I
For Each strElt In Unseul
j = j + 1
UserForm1.Controls("TextBox" & j).Value = strElt
Next
On Error GoTo 0
'REMPLIR LE TABLEAU
k = 0
p = 0
For j = 1 To 3
tboextract3 = ""
suite2 = ""
strElt3 = ""
I = ""
k = 0
If UserForm1.Controls("TextBox" & j).Value <> "" Then
For n = 1 To 8
If f.Cells(2, n) = UserForm1.Controls("TextBox" & j).Value Then
suite2 = suite2 & f.Cells(1, n) & ";"
End If
Next n
tboextract3 = Split(suite2, ";")
On Error Resume Next
For I = 0 To UBound(tboextract3)
Unseul3.Add tboextract3(I), tboextract3(I)
Next I
For Each strElt3 In Unseul3
Do Until Cells(6 + p, 2) = ""
p = p + 1
Loop
k = k + 1
f.Cells(6 + p + k, 2).Value = strElt3
'--------------------------------
' LIGNE QUI ME POSE PROBLEME
f.Cells(6 + p + k, 1).Value = UserForm1.Controls("TextBox" & j).Value
'--------------------------------
Next
End If
Next j
On Error GoTo 0
End Sub
A voir également:
[déplacé VB.NET -> VBA] problème de variables boucle loop
Private Sub UserForm_Initialize()
Dim tboextract As Variant
Dim tboExtract2 As Variant
Dim I
Dim Unseul As New Collection
Dim Unseul2 As New Collection
Dim strElt As Variant
Dim strElt2 As Variant
Dim j
Set f = ActiveSheet
'LISTE DS TEXTBOX
For n = 1 To 10
If f.Cells(2, n) <> "" Then
suite = suite & f.Cells(2, n) & ";"
End If
Next n
tboextract = Split(suite, ";")
On Error Resume Next
For I = 0 To UBound(tboextract)
Unseul.Add tboextract(I), tboextract(I)
Next I
For Each strElt In Unseul
j = j + 1
UserForm1.Controls("TextBox" & j).Value = strElt
Next
On Error GoTo 0
'LISTE DE SUITE 3
Dim tboextract3 As Variant
Dim Unseul3 As New Collection
Dim strElt3 As Variant
k = 0
CheckBox1 = True
For j = 1 To 3
Set Unseul3 = Nothing
Set Unseul3 = New Collection
suite2 = ""
k = 0
If UserForm1.Controls("checkbox" & j) = True And UserForm1.Controls("TextBox" & j).Value <> "" Then
For n = 1 To 10
If f.Cells(2, n) = UserForm1.Controls("TextBox" & j).Value Then
suite2 = suite2 & f.Cells(1, n) & ";"
End If
Next n
tboextract3 = Split(suite2, ";")
On Error Resume Next
For I = 0 To UBound(tboextract3)
Unseul3.Add tboextract3(I), tboextract3(I)
Next I
Do Until Cells(6 + P, 2) = ""
P = P + 1
Loop
For Each strElt3 In Unseul3
k = k + 1
f.Cells(5 + P + k, 2).Value = strElt3
If f.Cells(5 + P + k, 2).Value <> "" Then
f.Cells(5 + P + k, 1).Value = UserForm1.Controls("TextBox" & j).Value
End If
'If k = 1 Then
'f.Cells(6 + p, 1).Value = UserForm1.Controls("TextBox" & j).Value
'End If
Next
End If
Next j
On Error GoTo 0
End Sub
