Vlookup с динамическим значением lookup_value с использованием vba?

Я новичок в использовании значения динамического поиска, я попытался последовать совету из раздела Как выполнить vlookup в Excel с динамическим значением lookup_value с помощью VBA? для динамического получения значения из другой книги.

Этот ниже код вызывает ошибку #Name?.

Обратитесь к вам за помощью в устранении ошибки.

Отрывок из кода:

Dim ws As Worksheet
Dim dynamic_lookup_value As Range
Dim LocationRef As Range

Set ws = ActiveWorkbook.Worksheets("XYZ")

Set dynamic_lookup_value = ws.Cells(2, 1)

ActiveWorkbook.Names.Add Name:="dynamic_lookup_value", RefersTo:=Worksheets("XYZ").Cells(2, 1)

Set LocationRef = ws.Cells(2, ActiveColumn)

ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(" & dynamic_lookup_value.Address(0, 0) & ",'[ABC.xlsx]XYZ'!C1:C26,MATCH(""Location Description"",'[ABC.xlsx]XYZ'!R1C1:R1C26,0),)," & LocationRef.Address(0, 0) & ")"

После выполнения формула выглядит как

=IFERROR(VLOOKUP('A2','[HC report.xlsx]HC Report'!$A:$Z,MATCH("Location Description",'[HC report.xlsx]HC Report'!$A$1:$Z$1,0),),'G2')

Обратите внимание, что поисковое значение отображается как «A2» (с отдельными кодами). Пытался объявить dynamic_lookup_value, используя следующий фрагмент кода, но тщетно.

ActiveWorkbook.Names.Add Name:="dynamic_lookup_value", RefersTo:=Worksheets("XYZ").Cells(2, 1)

Если я вручную удалю код Singe до и после A2 и G2, то формула будет работать нормально.

Просим экспертов посоветовать решение этого вопроса.

Большое спасибо, Прабху

Одиночные кавычки обусловлены тем, что вы не указываете нотацию RC при использовании свойства Address.

SJR 11.04.2018 14:05

Большое спасибо за ваш ответ. Мне удалось решить проблему по вашему совету.

Prabhu 03.05.2018 15:56
0
2
383
2

Ответы 2

Новый ответ: После того, как я смог воспроизвести вашу ошибку, я думаю, что у меня есть решение: заменить ваш ActiveCell.FormulaR1C1 на ActiveCell.Formula. Это решило это в моем простом воссоздании проблемы (с использованием немедленного окна)

Это работает (устанавливает ссылку на ячейку A1 в ячейке A2 на основе адреса ячейки A1: Cells(2,2).Formula = "="&Cells(1,1).Address(0,0)

Хотя это не работает (дает ту же проблему с 'A1' в одинарных кавычках, что и у вас): Cells(2,2).FormulaR1C1 = "="&Cells(1,1).Address(0,0)

Большое спасибо за ваш ответ Prebsus. Пытался добавить в формулу косвенную, все та же проблема.

Prabhu 12.04.2018 08:15

Теперь мне удалось воспроизвести вашу проблему - см. Мой обновленный ответ выше

Prebsus 12.04.2018 17:01

Большое спасибо за ваш ответ Prebsus. Я попытался заменить Activecell.formulaR1C1 по-прежнему той же проблемой. Я пытаюсь заполнить формулу до определенного количества строк ниже. Оцените ваши предложения по решению проблемы.

Prabhu 13.04.2018 11:57

Я обнаружил проблему с моим макросом, как и посоветовал заменить Activecell.FormulaR1C1 на Activecell.Formula, а также заменил ссылку R1C1 формулой с абсолютной ссылкой на ячейку, после чего макрос выполнил идеально. Новый код ниже:

Prabhu 13.04.2018 16:34

ColumnNumber1 = AssociateIDColumnNo ColumnNumber2 = LocationDescColumnNo 'Преобразовать в столбец Letter ColumnLetter1 = Split (Cells (1, ColumnNumber1) .Address, "$") (1) ColumnLetter2 = Split (Cells (1, ColumnNumber2) .Address, " ) ActiveCell.Formula = _ "= ЕСЛИОШИБКА (ВПР (" & ColumnLetter1 & "2, '[XYZ.xlsx] ABC Report'! $ A $ 1: $ Z $ 500000, MATCH (" "Описание местоположения" ", '[XYZ. xlsx] ABC '! $ A $ 1: $ Z $ 1,0),), "& ColumnLetter2 &" 2) "

Prabhu 13.04.2018 16:34

Я думаю, что в VBA может быть некоторая путаница между именованными диапазонами и переменными диапазона. Однако одной из проблем в вашей формуле является несовместимое приложение или нотация RC, поэтому вам нужно

ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(" & dynamic_lookup_value.Address(0, 0, xlR1C1) & ",XYZ!C1:C26,MATCH(""Location Description"",XYZ!R1C1:R1C26,0),)," & LocationRef.Address(0, 0, xlR1C1) & ")"

Большое спасибо за ответ. По вашему предложению мне удалось решить проблему.

Prabhu 03.05.2018 15:58

Другие вопросы по теме