[RESOLVED] How to force Next before end?

[palantir]

In BASIC I could write something like

For x = 1 to 3
If x=2 then next x (or goto line number of above code)
print x+" is an odd number."
next x

vba does not seem to recognise the "next x" when in the if nest though.


How do I get around this? I can do x=x+1 because some of the exceptions are consecutive and will not be caught when together (unlike my even number example).

Thanks.

[JJJCR_FOX]

hi palantir, don't know exactly what you want to do but hope this is what you mean:

one more thing don't put goto line number which is above your "for loop" because it will be an eternal loop ...

For x = 1 to 3

If x = 2 then

Goto line_u_want '(goto line_u_want should be below for loop)

Exit For

End if

Next x

[westconn1]

For x = 1 to 3
  If x=2 then ' do nothing         next x (or goto line number of above code)
  else
     print x & " is an odd number."
  end if
next x

or

For x = 1 to 3
  If Not x=2 then print x &" is an odd number."
next x

note indenting to make code readable
use & rather than + to concantenate strings, + may work, but may give incorrect results sometimes

[anhn]

These simulate what you want. However, for better practice, if possible you should try to avoid using GoTo. Use the way westconn1 mentioned above.

Sub XXX()
    Dim x As Long

    For x = 1 To 10
        If x Mod 2 = 0 Then GoTo Next_x '-- use Goto Label
        '... ...
        Debug.Print x & " is an odd number."
Next_x:
    Next

End Sub

Sub XYZ()
    Dim x As Long

    For x = 1 To 10
        If x Mod 2 = 0 Then GoTo 99 '-- use Goto Line number
        '... ...
        Debug.Print x & " is an odd number."
99  Next

End Sub

[palantir]

Thank you anhn and all. Resolved. It was the GoTo Label I needed.

[westconn1]

it is not considered to be good to use goto or gosub in VB, except for error handling

[palantir]

So how else can it be done?

Here's my final working and tested code (excerpt:

Sub EDUmation()
...
For NumEntry = 1 To Range("B4").Value
FirstRow = ((NumEntry * 33) - 33) + 10 ' gets first row of each edu entry per loop
LastRow = FirstRow + 31 ' adds 31 rows to include all of entry
 For MyRow = FirstRow To LastRow ' loop through each row of the table
  If Cells(MyRow, 2).Value = "" Then GoTo Next_MyRow ' Gets next row if current row has no items
 mystr = ""
 mystr = Cells(MyRow, 1).Value & String(17 - Len(Cells(MyRow, 1).Value), " ") ' element name + horizontal spacer
 mystr = mystr & Cells(MyRow, 2).Value ' adds item in row 1
    For CheckRow = 3 To 31 ' this loop adds any other items except where empty
     If Not Cells(MyRow, CheckRow).Value = "" Then mystr = mystr & ", " & Cells(MyRow, CheckRow).Value
    Next CheckRow
 Print #1, mystr
Next_MyRow:
 Next MyRow
 Print #1, vbCrLf
Next NumEntry
Close #1
Sheets("DATA").Range("G2").ClearContents ' note that this row expects the worksheet tab to be named DATA
Sheets("DATA").Hyperlinks.Add Range("G2"), PageName
End Sub

[Hack]

So how else can it be done?

How about

For NumEntry = 1 To Range("B4").Value
    FirstRow = ((NumEntry * 33) - 33) + 10 ' gets first row of each edu entry per loop
    LastRow = FirstRow + 31 ' adds 31 rows to include all of entry
     For MyRow = FirstRow To LastRow ' loop through each row of the table
         If Cells(MyRow, 2).Value <> "" Then
            mystr = ""
            mystr = Cells(MyRow, 1).Value & String(17 - Len(Cells(MyRow, 1).Value), " ") ' element name + horizontal spacer
            mystr = mystr & Cells(MyRow, 2).Value ' adds item in row 1
               For CheckRow = 3 To 31 ' this loop adds any other items except where empty
                    If Not Cells(MyRow, CheckRow).Value = "" Then mystr = mystr & ", " & Cells(MyRow, CheckRow).Value
               Next 'CheckRow
            Print #1, mystr
         End If
     Next 'MyRow
        Print #1, vbCrLf
Next 'NumEntry
Close #1

[palantir]

You're a genius. Thank you so much! Resolved.