"End if without block if error" on my updated code

Question

I had this piece of code working fine:

its purpose is to output a new row with a data validation list in column B.

Sub RICH()


   Dim ws As Worksheet
   Dim fnd As Range
   Dim fndstr As String

ActiveSheet.Unprotect

   fndstr = "Targeted Premium Ads"
   Set ws = Worksheets("Inputsheet")

        Set fnd = ws.Columns(2).Find(What:=fndstr, After:=ws.Range("B11"), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not fnd Is Nothing Then


            Rows(fnd.Row - 1).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("B" & fnd.Row - 2).Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=USD"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True

            End With
        End If
End Sub

However I then tired to add a data validation for column A cell of that new row, but i get the "block if error" :(

Sub RICH()


   Dim ws As Worksheet
   Dim fnd As Range
   Dim fndstr As String

ActiveSheet.Unprotect

   fndstr = "Targeted Premium Ads"
   Set ws = Worksheets("Inputsheet")

        Set fnd = ws.Columns(2).Find(What:=fndstr, After:=ws.Range("B11"), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not fnd Is Nothing Then



            Rows(fnd.Row - 1).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("B" & fnd.Row - 2).Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=USD"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True


            Range("A" & fnd.Row - 2).Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=F6:F7"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True



            End With
        End If
End Sub

The two-cell range "Formula1:="F6:F7"? (Not too familiar with Validation, so could be off base. — chuff
– chuff, Commented Feb 19, 2013 at 18:43
no this was correct, i took it from record macro.. however i have added currency symbols now — user1717622
– user1717622, Commented Feb 19, 2013 at 18:54

cardmagik · Accepted Answer · 2013-02-19 18:47:05Z

3

Either you need to end your first With statement or not start a second with statement:

  With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=USD"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
END WITH

        Range("A" & fnd.Row - 2).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=F6:F7"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True



        End With

answered Feb 19, 2013 at 18:47

cardmagik

1,69819 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1717622 Over a year ago

this is now functional.. many thanks. quick question though... if i go for the option of not starting with the second with statement how would I control the validation?

David Zemens Over a year ago

You wouldn't. Unless the blocks are nested, you always need to END one With block before you start another one.

user2063626 · Accepted Answer · 2013-02-19 18:44:56Z

1

Change the line ... .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="USD"

Sub RICH()

   Dim ws As Worksheet
   Dim fnd As Range
   Dim fndstr As String

ActiveSheet.Unprotect

   fndstr = "Targeted Premium Ads"
   Set ws = Worksheets("Inputsheet")

        Set fnd = ws.Columns(2).Find(What:=fndstr, After:=ws.Range("B11"), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not fnd Is Nothing Then


            Rows(fnd.Row - 1).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("B" & fnd.Row - 2).Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="USD"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True

            End With
        End If
End Sub

answered Feb 19, 2013 at 18:44

user2063626

1 Comment

user1717622 Over a year ago

why does the line in the B column affect things when the addition of validation in the A column is what stopped it working?

Collectives™ on Stack Overflow

"End if without block if error" on my updated code

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related