views:

145

answers:

3

How do I achieve the following?

Sub Macro1()
'
' Macro1 Macro
'

'
    Worksheets("Drop-down").Select
    n = Cells(1, 1).End(xlDown).Row
    For i = 1 To n
        ActiveSheet.Cells(i, 2).Select
        *******************************************************
        If Worksheets("Misc").Cells(2, i).Value = "" Then
            continue i
        End If
        *******************************************************
        If Worksheets("Misc").Cells(3, i).Value <> "" Then
            Set validationRange = Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown))
        Else
            Set validationRange = Worksheets("Misc").Cells(2, i)
        End If
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=validationRange.Address
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Next i
End Sub

Thanks in advance!

A: 

Aren't vb's keywords capitalized?

For n = 1 To something
    If condition Then
        Continue For
    End If

    ' more code

Next n

The Continue keyword does what you want.

http://msdn.microsoft.com/en-us/library/801hyx6f(VS.80).aspx

Eric
I think keywords auto-capitalize when you're in the IDE, but if you're just writing a VBScript file it ignores the case.
Tom
The VBA IDE window doesn't recognize your suggestions.
stanigator
In what way? Which part?
Eric
See my edited code for what I'm trying to do. Thanks!
stanigator
That is the VB.NET syntax. It does not work in VBA.
Foole
Drat. I'd assumed the syntaxes were the same.
Eric
A: 

And to answer the other half of the question:

For n = 1 To something
    If condition Then
        Exit For
    End If
    ' more code

Next n
Tom
This doesn't do what I want, as it exits the for loop completely.
stanigator
Your question asked how to jump "out of a for loop". From your code, what you seemed to want was actually the "Continue For" statement, as answered by @Eric, I was adding "Exit For" for completeness. As another answerer mentioned, using an Else statement is good too. There are many ways to skin a cat.
Tom
+4  A: 

Isn't this a simple flow control case? I don't understand the need for special keywords to solve it:

For i = 0 To 10
 If Not condition Then 
    some other code
Next   

EDIT: Or, in your code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Worksheets("Drop-down").Select
    n = Cells(1, 1).End(xlDown).Row
    For i = 1 To n
        ActiveSheet.Cells(i, 2).Select
        *******************************************************
        If Not Worksheets("Misc").Cells(2, i).Value = "" Then
        *******************************************************
          If Worksheets("Misc").Cells(3, i).Value <> "" Then
              Set validationRange = Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown))
          Else
              Set validationRange = Worksheets("Misc").Cells(2, i)
          End If
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:=validationRange.Address
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
     ********
      End If
     ********
    Next 
End Sub
Banang
Thanks. Stupid me. I guess I can't quite code properly anymore...
stanigator
No worries, glad I could help.
Banang
This is indeed the best way of doing it. a few notes, in VBA, there is not 'Next i' it is simply Next
TerrorAustralis
Ah, thanks Dave. I've never done any VBA, so I had to improvise a bit. ;) I edited the code to match your comment.
Banang
No problem, VBA being the weakly typed monster that it is might have accepted it anyway :S
TerrorAustralis