CODEWITHSUNDEEP
excelvba
user1717622
user1717622

Reputation: 323

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

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

Upvotes: 1

Views: 393

Answers (2)

cardmagik
cardmagik

Reputation: 1698

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

Upvotes: 3

user2063626
user2063626

Reputation:

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

Upvotes: 1

Related Questions