PDA

View Full Version : [SOLVED] add/insert data validation cell to if column value to left is a number



mperrah
03-09-2015, 09:33 AM
Hello VBA legends,


Im trying to loop through the cells in column "E" if it has a number, add a data validation cell to the right.

I'm helping a co-worker automate a report.
He uses the workbook to track the status of an order. If an order has a shortage the report helps direct the needed actions.

The report has product orders output as an outline (2 levels)
Each order has a header (level 1), and each product has a header (level 2),
then the product items are listed from 1 to (less then 50), with a blank row above and below the order group.

I'm trying to add a validation list with four options to the right of each item: R/M, Pkg, Label, Other.

I have this code to insert the validation on selected cells.


Sub AddValidate()

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="R/M, Pkg, Label, Other, ' "
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub



I'm trying to insert the DV in a loop through the cells of product items (all items are numeric values)
(I also am adding some header labels)


Sub AddtoOrderTracking()

Dim i As Integer
Dim n As Integer
Dim c As Integer

ActiveSheet.Outline.ShowLevels RowLevels:=2

For i = 2 To 1000
If Cells(i, 5).Value = "Order Quantity" Then

Cells(i, 6).Value = "Shortages"

' this is where my code breaks<<<
' For n = 1 To 50
' If Cells(i + 1, 5).Value <> "" Then
' Cells(i + n, 6).Select
' Call AddValidate 'insert DV
' Else: End If
' Next n

Cells(i, 7).Value = "Completed"
Cells(i, 8).Value = "Comments"

End If
Next i

End Sub

So to sum up, Im trying to loop through the cells in column "E" if it has a number, add the validation to the right: pray2:

mperrah
03-09-2015, 09:42 AM
screen capture too small?
12979
12980
Hope this helps.

Thank you in advance for any help you can offer.

-Mark

mperrah
03-09-2015, 10:17 AM
Guess I needed to wrap my head around it a bit longer,
This works,
much love


Sub AddValidateLoop()

Dim i As Integer
Dim n As Integer
Dim c As Integer

ActiveSheet.Outline.ShowLevels RowLevels:=2

For i = 2 To 1000
If Cells(i, 5).Value <> "" And Cells(i, 6).Value = "" Then
With Cells(i, 6).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="R/M, Pkg, Label, Other, ' "
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End If
Next i

End Sub