excel - There's a problem with my loop (for next) and I can't figure it out?

Question

So I've got a UserForm with 54 Textboxes which I all need to be checked for their values which need to be either empty or "1", "2", "3" or "4". This is what I came up with:

dim CG(1 to 54) as string

CG(1) = NewStudent.TB1_1_1.Text
CG(2) = NewStudent.TB1_1_2.Text
CG(3) = NewStudent.TB1_1_3.Text
CG(4) = NewStudent.TB1_1_4.Text
CG(5) = NewStudent.TB1_1_5.Text
CG(6) = NewStudent.TB1_1_6.Text
CG(7) = NewStudent.TB1_2_1.Text
CG(8) = NewStudent.TB1_2_2.Text
CG(9) = NewStudent.TB1_2_3.Text

CG(10) = NewStudent.TB2_1_1.Text
CG(11) = NewStudent.TB2_1_2.Text
CG(12) = NewStudent.TB2_1_3.Text
CG(13) = NewStudent.TB2_1_4.Text
CG(14) = NewStudent.TB2_1_5.Text
CG(15) = NewStudent.TB2_1_6.Text
CG(16) = NewStudent.TB2_2_1.Text
CG(17) = NewStudent.TB2_2_2.Text
CG(18) = NewStudent.TB2_2_3.Text

CG(19) = NewStudent.TB3_1_1.Text
CG(20) = NewStudent.TB3_1_2.Text
CG(21) = NewStudent.TB3_1_3.Text
CG(22) = NewStudent.TB3_1_4.Text
CG(23) = NewStudent.TB3_1_5.Text
CG(24) = NewStudent.TB3_1_6.Text
CG(25) = NewStudent.TB3_2_1.Text
CG(26) = NewStudent.TB3_2_2.Text
CG(27) = NewStudent.TB3_2_3.Text

CG(28) = NewStudent.TB4_1_1.Text
CG(29) = NewStudent.TB4_1_2.Text
CG(30) = NewStudent.TB4_1_3.Text
CG(31) = NewStudent.TB4_1_4.Text
CG(32) = NewStudent.TB4_1_5.Text
CG(33) = NewStudent.TB4_1_6.Text
CG(34) = NewStudent.TB4_2_1.Text
CG(35) = NewStudent.TB4_2_2.Text
CG(36) = NewStudent.TB4_2_3.Text

CG(37) = NewStudent.TB5_1_1.Text
CG(38) = NewStudent.TB5_1_2.Text
CG(39) = NewStudent.TB5_1_3.Text
CG(40) = NewStudent.TB5_1_4.Text
CG(41) = NewStudent.TB5_1_5.Text
CG(42) = NewStudent.TB5_1_6.Text
CG(43) = NewStudent.TB5_2_1.Text
CG(44) = NewStudent.TB5_2_2.Text
CG(45) = NewStudent.TB5_2_3.Text

CG(46) = NewStudent.TB6_1_1.Text
CG(47) = NewStudent.TB6_1_2.Text
CG(48) = NewStudent.TB6_1_3.Text
CG(49) = NewStudent.TB6_1_4.Text
CG(50) = NewStudent.TB6_1_5.Text
CG(51) = NewStudent.TB6_1_6.Text
CG(52) = NewStudent.TB6_2_1.Text
CG(53) = NewStudent.TB6_2_2.Text
CG(54) = NewStudent.TB6_2_3.Text

x = True
For i = LBound(CG) To UBound(CG)

    If CG(i) = vbNullString Then
        Else
        If CG(i) = "1" Then
            Else
            If CG(i) = "2" Then
                Else
                If CG(i) = "3" Then
                    Else
                    If CG(i) = "4" Then
                        Else
                        x = False
                    End If
                End If
            End If
        End If
    End If
Exit For
Next i
    
'If i = UBound(CG) Then
If x = True Then

I know there's probably an easier way but I don't know anything about coding and I just couldn't figure it out. Now this is only a part of my code, in the end if x stays true it should copy the data into the worksheet.

This worked out fine but only for the first textbox. After that it just moved on to copy the data, ignoring all the other textboxes. Therefore I added "If i = UBound(CG) Then" thinking it would fix it.

However since then it just doesn't do anything anymore (no reaction, no error messages) as if that newly added condition never gets fullfilled.

Anyone pls help me out.

question from:https://stackoverflow.com/questions/65935093/theres-a-problem-with-my-loop-for-next-and-i-cant-figure-it-out

Answer 1

Your code does not work because you got Exit For so at first loop it quits the loop.

Also, you may benefit from Select Case

Try replacing:

x = True
For i = LBound(CG) To UBound(CG)

    If CG(i) = vbNullString Then
        Else
        If CG(i) = "1" Then
            Else
            If CG(i) = "2" Then
                Else
                If CG(i) = "3" Then
                    Else
                    If CG(i) = "4" Then
                        Else
                        x = False
                    End If
                End If
            End If
        End If
    End If
Exit For
Next i

with

For i = LBound(CG) To UBound(CG)
    x = True
    Select Case CG(i)
        Case 1 To 4, ""
            'WE DO NOTHING
        Case Else
            x = False
    End Select
    
    If x = True Then
        'code to copy if x=True
    End If
    
Next i

and see how it goes. Also, the copy part should be inside the loop for sure, so you check x and copy (or not) for each value in your array CG