add/insert data validation cell to if column value to left is a number
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.
Code:
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)
Code:
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:
