[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.
Re: How to force Next before end?
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
Re: How to force Next before end?
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
Re: How to force Next before end?
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
Re: How to force Next before end?
Thank you anhn and all. Resolved. It was the GoTo Label I needed. :)
Re: How to force Next before end?
it is not considered to be good to use goto or gosub in VB, except for error handling
Re: How to force Next before end?
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
Re: How to force Next before end?
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
Re: How to force Next before end?
You're a genius. Thank you so much! Resolved.