Microsoft Office Forums - View Single Post - [Solved] Add to list using new entry in cell

trevorc · 08-07-2018, 04:44 PM

Here is my solution...
using a to hold value next row of list, xx is the new value for the list. First check to see if it's in the list, if not add it, update a for next time, then go back to selection zz. Crude but it works

Code:

Sub Macro4()
a = Mid(Range("C1"), 2)
xx = ActiveCell.Value
zz = ActiveCell.Address
With Sheets("Automation Data").Range("B20:B100")
     Set Rng = .Find(What:=xx, _
               After:=.Cells(.Cells.Count), _
               LookIn:=xlValues, _
               LookAt:=xlWhole, _
               SearchOrder:=xlByRows, _
               SearchDirection:=xlNext, _
               MatchCase:=False)
End With
If Not Rng Is Nothing Then
   MsgBox "Already in List"
   Exit Sub
Else
ttt = MsgBox("Not in List, Add now?", vbYesNo)
   If ttt = 6 Then
      Sheets("Automation Data").Range("B" & a) = xx
      Range("C4:C5000").Select
      With Selection.Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='Automation Data'!$B$20:$B$" & a
           .IgnoreBlank = True
           .InCellDropdown = True
           .ShowInput = False
           .ShowError = False
       End With
     Sheets("Customer").Range("C1") = "C" & a + 1
   End If
End If
Range(zz).Select
End Sub