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

 

[jmpatrick]

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.

Hope this is better. I get an error when using the tool to post a Sheet.

This sheet is Calendar. The range SubLotColumn currently covers C5:C543:

This sheet is Holidays. The range HolidaysAll currently covers B1:B11

 

[Joe4]

OK, are the values in column B on your Calendar pages entered as valid dates, and then have a Custom date format applied to them?
Or are they entered as Text?

This part of your code is also problematic:

    If cl.Value = x Then GoTo SkipHyperlink

as the way x is listed here, it is looking for the variable x, but you have not defined a variable x anywhere in your code.

If you want to check the value to see if it was equal to the literal string "x", you would need to enclose it in double-quotes like:

    If cl.Value = "x" Then GoTo SkipHyperlink

I think that you may have some other logic issues in your code, but I cannot discern exactly what it is you are trying to do.
Based on the sample data you posted, can you just explain in plain English exactly what you are trying to do?

 

[jmpatrick]

Based on the sample data you posted, can you just explain in plain English exactly what you are trying to do?

If a value in the named range SubLotColumn matches a value in the named range HolidaysAll, then skip that line. If it does not find a match then run this code:

ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:=cl.Address, ScreenTip:="Click To Open Job Edit Window"

Here's the complete 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 x As Variant

    For Each x In Worksheets("Holidays").Range("HolidaysAll").Value
      
    Dim cl As Range
  
    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))
        If cl.Value <> "" Then
        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"
        End If
      
SkipHyperlink:

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

 

[Joe4]

If a value in the named range SubLotColumn matches a value in the named range HolidaysAll, then skip that line. If it does not find a match then run this code:

How can they possibly match, when the values in the SubLotColumn are the codes in column C (i.e. 7579-38) and the values in HolidaysAll are names (i.e. "New Year's Day")?
Those will never match -- you are comparing "apples-to-oranges" here.

 

[jmpatrick]

How can they possibly match, when the values in the SubLotColumn are the codes in column C (i.e. 7579-38) and the values in HolidaysAll are names (i.e. "New Year's Day")?
Those will never match -- you are comparing "apples-to-oranges" here.

The SubLotColumn can contain values from HolidaysAll.

 

[jmpatrick]

How can they possibly match, when the values in the SubLotColumn are the codes in column C (i.e. 7579-38) and the values in HolidaysAll are names (i.e. "New Year's Day")?
Those will never match -- you are comparing "apples-to-oranges" here.

I'm now getting a Run-time error 92, For Loop not initialized.

Sub AddJobEditHyperlinks()

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

    Next c
    
    Call ProtectCalendar

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

 

[Joe4]

The SubLotColumn can contain values from HolidaysAll.

View attachment 93888

Ah, OK. You did not show that in your orignal sample, so it looked like there were no matches!

Just so that we are absolutely clear as to what your goal is, can you explain, in plain English EXACTLY what you want to happen in each scenario (match vs. non-match)?

Note that I am going to be at a work conference all day long, so may not get a chance to respond until tonight - unless there are some breaks during the day where I have a chance to log in.

 

[jmpatrick]

Ah, OK. You did not show that in your orignal sample, so it looked like there were no matches!

Just so that we are absolutely clear as to what your goal is, can you explain, in plain English EXACTLY what you want to happen in each scenario (match vs. non-match)?

Note that I am going to be at a work conference all day long, so may not get a chance to respond until tonight - unless there are some breaks during the day where I have a chance to log in.

My script creates hyperlinks in every row in column "C" that is in the named range SubLotColumn. The hyperlinks simply point to the same cell.

Any cell in SubLotColumn that matches the values found in the named range HolidaysAll need to be skipped (cell doesn't get a hyperlink).

Any cell in SubLotColumn that does not match the values found in the named range HolidaysAll will get a hyperlink.

 

[Joe4]

Try this:

Sub AddJobEditHyperlinks()

    Dim cell As Range

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Sheets("Calendar").Activate
    ActiveSheet.Unprotect
    Sheets("Calendar").Range("SubLotColumn").Hyperlinks.Delete

'   Loop through each cell in SubLotColumn
    For Each cell In Sheets("Calendar").Range("SubLotColumn")
'       Check to see if cell is not blank
        If cell.Value <> "" Then
'           Check to see if no value found in HolidaysAll
            If Application.WorksheetFunction.CountIf(Sheets("Holidays").Range("HolidaysAll"), cell.Value) = 0 Then
'               Add Hyperlink
                ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=cell.Address, ScreenTip:="Click To Open Job Edit Window"
            End If
        End If
    Next cell
    
    Call ProtectCalendar

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

 

[jmpatrick]

Try this:

Sub AddJobEditHyperlinks()

    Dim cell As Range

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Sheets("Calendar").Activate
    ActiveSheet.Unprotect
    Sheets("Calendar").Range("SubLotColumn").Hyperlinks.Delete

'   Loop through each cell in SubLotColumn
    For Each cell In Sheets("Calendar").Range("SubLotColumn")
'       Check to see if cell is not blank
        If cell.Value <> "" Then
'           Check to see if no value found in HolidaysAll
            If Application.WorksheetFunction.CountIf(Sheets("Holidays").Range("HolidaysAll"), cell.Value) = 0 Then
'               Add Hyperlink
                ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=cell.Address, ScreenTip:="Click To Open Job Edit Window"
            End If
        End If
    Next cell
   
    Call ProtectCalendar

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

Works perfectly! Lots to learn from here. Thanks for your help.