We help IT Professionals succeed at work.

Intersect, and refer to column by name

86 Views
Last Modified: 2018-11-11
  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

Comment
Watch Question

Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

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
CERTIFIED EXPERT

Commented:
What is the code meant to do?

Author

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 LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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).

Author

Commented:
The column is named Variable; hence
tb.ListColumns("Variable").DataBodyRange

Open in new window

.
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions