Defining a Range based on a Named Range from another Sheet?

[jmpatrick]

Happy Friday!

I'm trying to define run code only if the value does not match one of the values in a Named Range on another sheet. Sadly, it's not working.

Here's what I have:

Sub AddJobEditHyperlinks()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim cl As Range
    Dim Hol As Range
    
    Set Hol = Worksheets("Holidays").Range("HolidaysAll")
    
    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))
        
        If cl.Value <> Hol Then
            ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:=cl.Address, ScreenTip:="Click To Open Job Edit Window"
        End If
        
    Next cl
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[Joe4]

If "Hol" is a multi-cell range, then you will need to loop through that too, just like you are looping through your "cl" range.
So you would need "a loop inside another loop".

Or, you may be able to store the holidays in an array and use some array logic to find if the date in a particular cell is found in that array.

 

[jmpatrick]

If "Hol" is a multi-cell range, then you will need to loop through that too, just like you are looping through your "cl" range.
So you would need "a loop inside another loop".

Or, you may be able to store the holidays in an array and use some array logic to find if the date in a particular cell is found in that array.

Thanks for the reply. I was able to use a similar concept from last week. Still doesn't work but I think it's closer:

Sub AddJobEditHyperlinks()

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

    If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, x) Then GoTo SkipHyperlink
   
    'ActiveSheet.Unprotect

    Dim cl As Range
    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))
        If cl.Value <> "" Then
            ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:=cl.Address, ScreenTip:="Click To Open Job Edit Window"
        End If
    Next cl
   
    '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

SkipHyperlink:

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

 

[jmpatrick]

If "Hol" is a multi-cell range, then you will need to loop through that too, just like you are looping through your "cl" range.
So you would need "a loop inside another loop".

Or, you may be able to store the holidays in an array and use some array logic to find if the date in a particular cell is found in that array.

Here's where I'm at now. This does not throw an error, but it's not skipping rows whose value in "C" is found in the Named Range:

Sub AddJobEditHyperlinks()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Dim x As Variant

    Dim cl As Range
    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))
   
    For Each x In Worksheets("Holidays").Range("HolidaysAll").Value
   
        If InStr(Worksheets("Calendar").Range("C" & ActiveCell.Row).Value, x) Then GoTo SkipHyperlink
           ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:=cl.Address, ScreenTip:="Click To Open Job Edit Window"
    Next
   
SkipHyperlink:
       
    Next cl

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

 

[Joe4]

I am kind of confused by this line here:

    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))

What exactly are you trying to do here?
What/how is "SubLotColumn" defined?

 

[jmpatrick]

I am kind of confused by this line here:

    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))

What exactly are you trying to do here?
What/how is "SubLotColumn" defined?

That's the Named Range of the cells being looped through.

I made a couple changes. No errors, but it doesn't skip x values.

Sub AddJobEditHyperlinks()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Sheets("Calendar").Activate
    ActiveSheet.Unprotect
    Application.Goto Reference:="SubLotColumn"
    Selection.Hyperlinks.Delete
    
    Dim cl As Range
    
    Dim varName As Variant
    Dim rng As Variant
    
    varName = "x"
    rng = Worksheets("Holidays").Range("HolidaysAll").Value
    
    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))
        If cl.Value = x Then GoTo SkipHyperlink
            ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:=cl.Address, ScreenTip:="Click To Open Job Edit Window"
        
SkipHyperlink:

    Next cl
    
    Call ProtectCalendar
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[Joe4]

Can you post samples of the two data ranges involved, so I can see exactly what your data looks like, and try to recreate the scenario on my side?
Please also let me know the exact addresses of your Named Ranges.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jmpatrick]

Can you post samples of the two data ranges involved, so I can see exactly what your data looks like, and try to recreate the scenario on my side?
Please also let me know the exact addresses of your Named Ranges.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Using your fine add-in, here is my main sheet. The named range SubLotColumn is in col. C of this page.

			MODELS have BLACK background
	Date	Sub # / Lot #	Subdivision
	11/14/22 - Monday	7579-38	CGM - Cottages At Gregory Meadows
	11/15/22 - Tuesday	7579-55	CGM - Cottages At Gregory Meadows
	11/15/22 - Tuesday	7817-1	BAL - Ballantyne
	11/15/22 - Tuesday	7872-10	BAW - Brookfield At Waldon Village
	11/15/22 - Tuesday	7872-37	BAW - Brookfield At Waldon Village
	11/16/22 - Wednesday	7746-43	EMP - Emerald Park
	11/16/22 - Wednesday	7746-44	EMP - Emerald Park
	11/17/22 - Thursday	7578M-18	TMP - Townes At Merrill Park
	11/17/22 - Thursday	7746-42	EMP - Emerald Park
	11/18/22 - Friday	7335-24	FS - Fieldstone
	11/18/22 - Friday	7335-31	FS - Fieldstone
	11/18/22 - Friday	7335-8	FS - Fieldstone

...and here is the sheet with the data (from the named range HolidaysAll

1/1/2023	New Years Day
5/29/2023	Memorial Day
7/4/2023	Independence Day
	Saturday
	Sunday
9/4/2023	Labor Day
11/23/2023	Thanksgiving
12/24/2023	Christmas Eve
12/25/2023	Christmas Day
12/31/2023	New Years Eve
	Saturday
	Sunday
	Office Closed

Here is my current code:

Sub AddJobEditHyperlinks()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Sheets("Calendar").Activate
    ActiveSheet.Unprotect
    Sheets("Calendar").Range("SubLotColumn").Hyperlinks.Delete
    
    Dim cl As Range
    
    Dim varName As Variant
    Dim rng As Variant
    
    varName = "x"
    rng = Worksheets("Holidays").Range("HolidaysAll").Value
    
    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))
        If cl.Value = x Then GoTo SkipHyperlink
            ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:=cl.Address, ScreenTip:="Click To Open Job Edit Window"
        
SkipHyperlink:

    Next cl
    
    Call ProtectCalendar
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[jmpatrick]

Made a couple more changes. No errors but still doesn't skip the x values.

Sub AddJobEditHyperlinks()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Sheets("Calendar").Activate
   
    Dim cl As Range
    
    Dim varName As Variant
    Dim rng As Variant
    
    varName = "x"
    rng = Worksheets("Holidays").Range("HolidaysAll").Value
    
    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))
    
    If cl.Value = x Then GoTo SkipHyperlink
        If cl.Value <> "" Then
            ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:=cl.Address, ScreenTip:="Click To Open Job Edit Window"
        End If
        
SkipHyperlink:
        
    Next cl
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[Joe4]

I think you provided screen shots from the XL2BB add-in, as if you posted images using the tool correctly, it typically shows the column and row headers, which would be very helpful in seeing where exactly all this data resides on your worksheets.