VBA if value is not in a named range add it.

[bhsoundman]

I've got a worksheet that uses a data validation cell dropdown to add data (names) to a cell. If a name is typed into the cell and it does not exist in the named range, my current macro offers to add it to the named range. This macro is triggerd when the cell value is changed so it checks automatically. My current script works, but it's painfully slow. In fact if that workbook is even open and I'm working in an unrelated workbook, that gets slowed down too. I'm sure there's a more efficient way to write this so it's a faster process. I've looked through numerous posts about this subject, but only found parts of the answer. Currently this whole process works via (3) macros & my current script is a mess (I apologize). Any help is greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng As Range

Dim r As Long

Sheets("Avaliable Crew").Range("N1").Value = ""

Sheets("Avaliable Crew").Range("N2").Value = ""

r = Target.Row

On Error Resume Next

Set Rng = Range("B9:bo55").SpecialCells(xlCellTypeBlanks)

On Error GoTo 0

If Not Rng Is Nothing Then

Rng.Interior.Color = RGB(255, 255, 163)

End If


If Not Intersect(Target, Range("C9:BP55")) Is Nothing Then

If Target.CountLarge = 1 Then

If Target.Value <> "" Then Range("A3").Value = Target.Value

Range("a1").Value = Range("B" & r).Value

Sheets("Avaliable Crew").Range("N1").Value = Target.Value

Sheets("Avaliable Crew").Range("N2").Value = Range("B" & r).Value

End If

End If

in_available_crew

End Sub

Sub in_available_crew()

Dim CrewName As String

Dim C As Range

CrewName = Sheets("Avaliable Crew").Range("N1").Value

Sheets("Avaliable Crew").Calculate

With ThisWorkbook.Names("Available_Crew").RefersToRange

Set C = .Find(CrewName, LookIn:=xlValues)

If C Is Nothing Then

answer = MsgBox("Crew member " + Sheets("Avaliable Crew").Range("N1").Value + " not found. Want to add them to this category", vbQuestion + vbYesNo + vbDefaultButton2, "ADD CREW?")

If answer = vbNo Then

Exit Sub

Else

Add_To_Crew

End If

End If

End With

End Sub

Sub Add_To_Crew()

Application.ScreenUpdating = False

Dim CrewColumn As String

Dim CrewMember As String

Dim rCell As Range

CrewMember = Sheets("Avaliable Crew").Range("N1").Value

CrewColumn = Sheets("Avaliable Crew").Range("N4").Value

Debug.Print (CrewMember)

Sheets("Avaliable Crew").Select

ActiveSheet.Calculate

Sheets("Avaliable Crew").Range("N1").Copy

Sheets("Avaliable Crew").Range(CrewColumn & "1").Select

Debug.Print (CrewColumn)

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

For Each rCell In Range("A1:I1") 'Change range to suit

rCell.EntireColumn.sort Key1:=rCell(2, 1), _

Order1:=xlAscending, Header:=xlYes

Next rCell



Sheets("Crew").Select

Application.ScreenUpdating = True



End Sub

 

[kevin9999]

I see you’ve had over 30 views of your post, but no suggestions so far. Personally, I’m finding it difficult to follow the flow of your code, as well as understanding how some parts (formatting, sorting) are related to your post title. I would like to help you, but can I suggest we break this down to the core issue addressed in your title - if value is not in a named range add it – then do the add on extras once we get that bit right?

You said you wanted this to happen automatically, so you’ve correctly used a Worksheet_Change event module.

What is the name of the sheet where you want the change event to be captured? (“Crew?) What is the cell/range of cells in that sheet where you want to be monitored for change?

You then want the code to ask if the user wants to add the new value?

What is the name of the sheet that contains the named range you want the new value added to? (“Available Crew”?)

What is the name of the range you want the new value added to? (“Available_Crew”?)

Once we get the basics right, we can look at adding the other stuff.

 

[bhsoundman]

I see you’ve had over 30 views of your post, but no suggestions so far. Personally, I’m finding it difficult to follow the flow of your code, as well as understanding how some parts (formatting, sorting) are related to your post title. I would like to help you, but can I suggest we break this down to the core issue addressed in your title - if value is not in a named range add it – then do the add on extras once we get that bit right? You're correct

You said you wanted this to happen automatically, so you’ve correctly used a Worksheet_Change event module. Yes, I've got the event part working correctly I believe.

What is the name of the sheet where you want the change event to be captured? (“Crew?) What is the cell/range of cells in that sheet where you want to be monitored for change? Correct. the worksheet is "Crew" & the monitored range is "B9:BO55"

You then want the code to ask if the user wants to add the new value? yes

What is the name of the sheet that contains the named range you want the new value added to? (“Available Crew”?) yes

What is the name of the range you want the new value added to? (“Available_Crew”?) There are multiple (audio, video, lighting etc etc)

Once we get the basics right, we can look at adding the other stuff.

Thanks so much for the reply @kevin9999 ! I appreciate you looking at this. I've actually started over on this workflow & have been working on it today. I may have a much faster version, but I'm still working on it. I'll post the finished version . Because you took the time to ask questions, I've answered them in the above text.

 

[kevin9999]

@bhsoundman thank you for clarifying those points. I look forward to seeing your improved code when you post it.

Am I correct in assuming the ranges "audio", "video" etc. are 'contained' within the larger named range "Available_Crew"? And that the new value is added to a particular column based on the value in cell N4 of the "Available Crew" sheet? If so, where does the N4 value come from?