For... Next VBA Problem

[jlw200]

Good morning all,

I am trying to help my wife out with a little VBA. She got assigned this project for grad school and neither one of us have ever had a VBA class. Looking at this statement, can you see what is wrong. I get a Next without For errer on running it. I have rearranged it and tried moving different parts around with no luck. Thank you in advance.

Private Sub Rolled_Change()
Rolled = Rolled.Enabled

Dim i As Integer
Dim Section As String

Sheets("Database v13.0").Select

Range("A2").Select
Section = ActiveCell.Value
For i = 2 To 321
If Section = Not (Rolled.Value) Then
Next i
If Section = Rolled.Value Then Do

ActiveCell.Offset(0, 1).Select
A = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
d = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
bf = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
tw = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
tf = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
bf_2tf = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
h_tw = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Ix = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Zx = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Sx = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Iy = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Ry = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
J = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Cw = ActiveCell.Value

End If

 

[ChrisM]

Every VBA procedure/function has to have a start and stop command.

So, every For...must end with a Next, Every If must end with an End If (usually), etc.

In your case, your For...Next is not properly "ended".

You have this:

For i = 2 To 321
If Section = Not (Rolled.Value) Then
Next i

If the If-Then is False, then the code never gets to run 'Next i'. That means VBA thinks you have never properly terminated your For-Next loop.

You need something like this:

For i = 2 To 321
If Section = Not (Rolled.Value) Then
     Next i
End if
...
...
...
Next i  '<--- add this here

but obviously this won't achieve your program objective, so you'll have to add some code here to tell VBA what you want to happen when that first If-Then returns false.

 

[jlw200]

Thank you for the help.

I commented out everything following your suggestion below. I still have the header info but limited it to just this. I am still getting a Next without For on just this section. Should I be?

For i = 2 To 321
If Section = Not (Rolled.Value) Then
Next i
End If

 

[ChrisM]

Sorry, I goofed a bit, but you still don't have the Next i in the right place.

You need to restructure your If-Then a bit, to something like this:

For i = 2 To 321
  If Section = (Rolled.Value) Then
    'note I removed 'NOT' above.
    'Insert code here for what should happen when Section = Rolled
  End If

Next i

 

[jlw200]

One final question for now as that solved the For/Next problem. Is there a way to throw up an alert in VBA to see what value is being transferred or to show that you have reached this area of a program like you can in say JavaScript?

Thank you very much for your help on that problem. I will talk with her about his when I get off work and see if that gets her going. We were both just lost. Thanks again ChrisM.

 

[ChrisM]

Sorry, not familiar with the function in Javascript you are talking about.

Do you want a programming alert (for fine tuning your code), or something for the end user to see?

If it's a programming thing, VBA supports all the usual Watch, Break On, etc programming functions.

If it's an end user thing and if you don't mind halting your code, you can use a message box, but it will stop the whole macro until the user presses ok:

MsgBox "insert some text here, then follow with the value you want to show: " & Rolled.Value

If you want the macro to continue running, you could show a value temporarily in the status bar at the bottom of the screen:

Application.Statusbar = Rolled.Value

Or you could just write the value right to a blank cell on a worksheet:

Sheet1.Range("A1") = Rolled.Value

 

[jlw200]

It would be for her to fine tune the code. Basically, you make selection B of 3 choices and I am wanting to see that selection B is actually what is being passed to the next part of the application and not selection A.

In this case say you have

"If Section = Rolled.Value"

Is there a way to alert me to the value of Rolled.Value.

Hope you understand what I mean.

 

[sous2817]

Hello,

You could try

Msgbox Rolled.Value

 

[mooseman]

Actually while your cursor is in the code and you have the correct workbook open, you can use the F8 key to step through the macro one line at a time and add a watch to any variable you need and see what is happening. Between each step (hitting the F8 key), you can go look at what is happening to the open workbook and then come back to the VBA editor to continue.

 

[jlw200]

OK Thanks everyone. Much appreciated on everything. This project for her was just not expected and she has had 2 weeks to do it and basically learn it from scratch. If someone needs to close this thread I consider my questions more than answered. Thanks again everyone. I will be back. Great board!