Решение VBA для VLOOKUP с гиперссылками

Question

Я ищу помощь с решением VBA для сохранения гиперссылок при использовании VLOOKUP в Excel (2010).

У меня есть данные на Листе 1 только для внутреннего использования, и сокращенная версия этого на Листе 2. Вместо того, чтобы воссоздавать Sheet 2 каждый раз, я ищу рабочую версию, которая обновляется при каждом обновлении Sheet1. Таким образом, я использовал VLOOKUP на листе 2, чтобы на листе 2 возвращалась только нужная информация.

Однако проблема заключалась в том, что лист 1 содержал во многих ячейках гиперссылки на внешние веб-сайты, и это не помогло бы перейти на Sheet2 с помощью VLOOKUP. Однако с некоторой помощью, используя следующее решение VBA, гиперссылки теперь проходят:

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

И я использую следующую формулу в соответствующих ячейках в 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)))

Однако проблема заключается в форматировании: каждая ячейка на Sheet2 отформатирована синим цветом и подчеркнута, хотя некоторые из них не содержат гиперссылки!

Может ли кто-нибудь помочь с решением / формулой VBA, чтобы исправить эту последнюю часть головоломки?

Большое спасибо, в ожидании.

Answer 1

Похоже, что Excel случайно применяет форматирование гиперссылок, если у вас есть несколько на странице. Вы можете изменить это поведение, чтобы оно никогда не делало это автоматически (поэтому вам всегда нужно использовать HYPERLINK), измените этот параметр в Proofing-> AutoCorrect-> AutoFormat при вводе текста.

Что касается исправления текущего листа, у вас может быть Очистить-> Очистить форматы и переформатировать страницу.