0

I am looking for some help with a VBA solution for preserving hyperlinks when using VLOOKUP on Excel (2010).

I have a load of data on Sheet 1 for internal use only, and a cut-down version of this on Sheet 2. Instead of recreating Sheet 2 everytime, I am looking to have a working version which updates everytime Sheet1 is updated. Thus, I have used VLOOKUP on Sheet 2 so that only the desired info is returned on sheet 2.

However, the problem was that sheet 1 contained in many cells Hyperlinks to external websites, and this would not pull through to Sheet2 using VLOOKUP. With some help, however, using the following VBA solution the hyperlinks now pull through:

Function GetHyperLink(r As Range) As String
If r.Hyperlinks.Count Then
GetHyperLink = r.Hyperlinks(1).Address
End If
End Function

And I am using the following formula in the relevant cell(s) in Sheet2:

=HYPERLINK(GetHyperLink(INDEX('Sheet 1'!$B$1:$B$10001,MATCH(A4,'Sheet 1'!$A$1:$A$10001,0))),(VLOOKUP(A4,'Sheet 1'!$A$1:$B$10001,2,FALSE)))

However, the problem is with formatting: every cell on Sheet2 is formatted blue and underlined, even although some of them do not contain a hyperlink!

Is someone able to help with a VBA solution/formula to fix this last piece of the puzzle?

Many thanks, in anticipation.

Improve this question
2
  • I think this has little or nothing to do with your VBA code. Excel sometimes likes to over-guess that things are hyperlinks once you've entered one hyperlink... Does it have to be a VBA fix?
    – Madball73
    May 29, 2014 at 15:41
  • No, doesn't have to be VBA fix! If you have any other idea's I would be grateful.
    – Emily2
    May 29, 2014 at 15:42

1 Answer 1

Reset to default
1

Excel appears to randomly apply hyperlink formatting once you have a few on a page. You can change this behaviour, to make it never do this automaticaly (so you'd always have to use HYPERLINK), change this setting in Proofing->AutoCorrect->AutoFormat as you Type.

As for fixing the current sheet, you may have Clear->Clear Formats and reformat the page.

enter image description here

Improve this answer
2
  • Hi Madball, Thanks for the option. However, this turns all of the cells black and not underlined - I was hoping that the cells which do have a hyperlink would still be blue and underlined? How would I do thins?
    – Emily2
    May 29, 2014 at 16:29
  • Go into any of the formulas, and F2+Enter to have it redo the highlighting (or just click in the formula and press enter). Then copy-paste this cell to all the cells that have this formula.
    – Madball73
    May 29, 2014 at 16:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .