[RESOLVED] How to force Next before end?

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.

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 ...

Code:

---


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

---

vb Code:

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

vb Code:

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

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

Code:

---

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

---

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

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

So how else can it be done?

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

Code:

---

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

---

Quote:

---

Originally Posted by palantir

So how else can it be done?

---

How about

Code:

---

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

---

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