Intersect, and refer to column by name

  1. With the intersect, how can I prevent the code from being activated if there is no Site? (The commented code affects the whole worksheet.)
  2. How can I refer to Selection.Offset(0, -1) by the column's name, Site?

Dim sSite As String
If Not Intersect(Target, tb.ListColumns("Variable").DataBodyRange) Is Nothing Then
' If Intersect(Target, tb.ListColumns("Variable").DataBodyRange) <> "" And Selection.Offset(0, -1) <> "" Then
    Cancel = True
    sSite = WorksheetFunction.Substitute(WorksheetFunction.Index(Range("Sites[[Base]]") _
    , WorksheetFunction.Match(Selection.Offset(0, -1), Range("Sites[[Site]]"), 0)), "*", "" & ActiveCell.Value & "")
    ThisWorkbook.FollowHyperlink (sSite)
End If

Open in new window

lhuttonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
Simply test the sSite variable: 
if(sSite <> vbNullString) Then
    ThisWorkbook.FollowHyperlink sSite
End If

Open in new window


PS:
Do you really need the Selection and ActiveCell objects ?
Be warned that they are major source of troubles.
lhuttonAuthor Commented:
  1. Only the Site column may be empty; never sSite.
  2. My second question asks how can I avoid the Selection. For the ActiveCell, how else should I refer to it?
NorieAnalyst Assistant Commented:
What is the code meant to do?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
lhuttonAuthor Commented:
The Variable column contains either a whole or part of a hyperlink; the adjacent Site column (if not empty for the active row) refers to another table for the other part of the hyperlink.

  1. The intersect means the code can only be activated if the Variable column is selected. I also want to prevent the code from being activated if no Site is specified for the active row (because it is a whole hyperlink).
  2. Instead of the Selection I want to refer to the Site column by name in the way that I have used names for the ranges. This will save me having to update the code when the table structure changes.
Fabrice LambertConsultingCommented:
A Variable column ???
How are we supposed to understand that ? A column that can be anywhere ?

I still don't understand !!
Upload a sample workbook, and explain from the beginning (not just the part you attempt to solve).
lhuttonAuthor Commented:
The column is named Variable; hence 
tb.ListColumns("Variable").DataBodyRange

Open in new window

.
Fabrice LambertConsultingCommented:
Upload a sample workbook, and explain from the beginning (not just the part you attempt to solve).
For now, the little chunk of code you provided isn't enough to offer a solution.
There are other things involved, but you didn't told us about it.
lhuttonAuthor Commented:
An alternative to 
Selection.Offset(0, -1)

Open in new window

is 
Cells(ActiveCell.Row, Range("TableName[[ColumnName]]").Column).Value

Open in new window

Source

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.