Referring to Named Range in If InStr?

[jmpatrick]

Happy Friday!

I'm trying to use a Named Range as x but I'm stuck. Here's the code I have that's giving me a type mismatch error:

Sub MarkConfirmed()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    X = Worksheets("Holidays").Range("HolidaysAll").Value

    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, ".") Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, X) Then GoTo SkipConfirm
   
    ActiveSheet.Unprotect

    Dim c As Range
    For Each c In Selection
    If c.Value <> "" Then c.Value = c.Value & "."
    Next
   
    'Protect Calendar
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

SkipConfirm:

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
End Sub

If I use a cell instead of a Named Range it works:

X = Worksheets("Holidays").Range("B1").Value

 

[DanteAmor]

Range("HolidaysAll")

Ensures that the named range only has cell B1, if it has more than one cell then it will send the error.

 

[jmpatrick]

Ensures that the named range only has cell B1, if it has more than one cell then it will send the error.

That's what I'm seeing. The named range HolidayAll has 10 rows. Not sure how to fix this.

 

[jmpatrick]

I could do something like this but it would be ugly and it have to be modified every time I needed to add another holiday:

    a = Worksheets("Holidays").Range("B1").Value
    b = Worksheets("Holidays").Range("B2").Value
    c = Worksheets("Holidays").Range("B3").Value
    d = Worksheets("Holidays").Range("B4").Value
    e = Worksheets("Holidays").Range("B5").Value
    f = Worksheets("Holidays").Range("B6").Value
    g = Worksheets("Holidays").Range("B7").Value
    h = Worksheets("Holidays").Range("B8").Value
    i = Worksheets("Holidays").Range("B9").Value
    j = Worksheets("Holidays").Range("B10").Value

    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, ".") Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, a) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, b) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, c) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, d) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, e) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, f) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, g) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, h) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, i) Then GoTo SkipConfirm
    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, j) Then GoTo SkipConfirm

 

[Joe4]

Maybe loop through your named range, and store the values in an array.
Then you should be able to check to see if the value you are checking appears anywhere in the array.

There are a few ways to do that. Here are some examples:

Check if Value is in Array using VBA - wellsr.com

Check if a value is in an array with this VBA function. Use it to look for a string in a string array, an integer in an integer array, and more.

wellsr.com

Check if a value exists in an array VBA

To check if a value exists in an array, we can loop through its elements. However there is another solution! You can use the INDEX () function, native to Excel and in the case of two-dimensional arrays use a combination of the INDEX/MATCH function. H...

ccm.net

 

[Akuini]

Try using loop:

    Dim x As Variant

    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, ".") Then GoTo SkipConfirm
    For Each x In Worksheets("Holidays").Range("HolidaysAll").Value
        If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, x) Then GoTo SkipConfirm
    Next

 

[jmpatrick]

Try using loop:

    Dim x As Variant

    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, ".") Then GoTo SkipConfirm
    For Each x In Worksheets("Holidays").Range("HolidaysAll").Value
        If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, x) Then GoTo SkipConfirm
    Next

This is exactly what I was trying to do. Thanks!

 

[Joe4]

Try using loop:

    Dim x As Variant

    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, ".") Then GoTo SkipConfirm
    For Each x In Worksheets("Holidays").Range("HolidaysAll").Value
        If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, x) Then GoTo SkipConfirm
    Next

Ah yes, you can loop through the values directly without bringing them into an array (wish I would have thought of that!).

 

[Akuini]

This is exactly what I was trying to do. Thanks!

You're welcome, glad to help & thanks for the feedback.