Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Missing in Loop for Data Validation

0

Hi

I would like to ask that in the given code, in ranges between C3 to C178

I would create Data Validation from C3 to C16

Then again it will start from C21 end to C34

And this carry on to C178

Note taht the below code starts from C3 and again it starts from C21 by adding Step Statement which will not fullfill the above request.

Sub NewLoopDV()
    Dim x As Integer

        For x = 3 To 178 Step 18
            With Range("C" & x & "").Validation
                .Delete
                .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=C" & x & "<=B" & x & ""
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = "Invalid Quantity"
                .InputMessage = ""
                .ErrorMessage = "It Must be enter less than or equal to Quantity Delivered"
                .ShowInput = True
                .ShowError = True
            End With
        Next x
End Sub
exceldata-validation
MS Office Version: 2013
posted by: smuneeb (rep: 2) on Wed Mar 8, 2017 at 11:06 am
Answer
Discuss

Discussion

So, every 13 rows, you want to skip 5 rows and then start the data validation over again?
don (rep: 1989) Mar 8, '17 at 11:49 am
Yes you get understand the concept but not 13 rows it would be 14 rows that data validation applied and then skip 5 rows.
Thanks for response
smuneeb (rep: 2) Mar 8, '17 at 2:51 pm
Add to Discussion

Answers

0
Selected Answer

Try this:

Sub NewLoopDV()
    Dim x, xcalc1 As Integer

    xcalc1 = 1

    For x = 3 To 178

        If xcalc1 < 14 And xcalc1 > 0 Then

            With Range("C" & x & "").Validation
                .Delete
                .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=C" & x & "<=B" & x & ""
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = "Invalid Quantity"
                .InputMessage = ""
                .ErrorMessage = "It Must be enter less than or equal to Quantity Delivered"
                .ShowInput = True
                .ShowError = True
            End With

            xcalc1 = xcalc1 + 1

        End If

        If xcalc1 = 14 Then
            xcalc1 = -5
        End If

        If xcalc1 < 1 Then
            xcalc1 = xcalc1 + 1
        End If

    Next x


End Sub

It might not be the most elegant solution but it seems to work just fine.

posted by: don (rep: 1989) on Thu Mar 9, 2017 at 8:00 am
Discuss

Discussion

Thanks for response
smuneeb (rep: 2) Mar 9, '17 at 9:17 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login