Подскажите, как можно в VBA использовать переменные в диапазоне функции Range ?

@ykr · Регистрация: 11.01.2019

Студворк — интернет-сервис помощи студентам

Подскажите, как можно в VBA использовать переменные в диапазоне функции Range ?

Например, выражение 1 работает корректно.
1). Range(Cells(1, 10), Cells(1, 10)).Value = 300

Далее вместо Cell (1,10) хотел бы использовать Cell (I,K) ....

Пытаюсь по аналогии заменить Range("$A2:$D$61") на Range(Cells(2, 1), Cells(61, 4)) - вылетает ошибка 1004

Применяю совместно с LOOKUP. Выражение 2 работает корректно, выражение 3 с ошибкой 1004.

2). Rezult3 = WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)), Workbooks("bill.xlsx").Sheets("Phone").R ange("$A2:$D$61"), 4, 0)

3). Rezult3 = WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)), Workbooks("bill.xlsx").Sheets("Phone").R ange(Cells(2, 1), Cells(61, 4)), 4, 0)

При этом доступ к файлу и листу есть.
Workbooks.Open "D:\Test\Bill.xlsx"
ActiveSheet.Name = "Phone"

Может есть какие идеи?
Спасибо!

@Zeag · 21.06.2021, 13:18

Когда вы пишете Cells() без полной спецификации файла и листа - смотрите, какой лист будет использован. Я всегда пишу или полный адрес FP.Sheet(N).Cells(x, y), или с With и с точкой: .Cells(x, y) при ранее поставленном With FP.Sheet(N)

В вашем случае проверил бы так:

Visual Basic
1
Rezult3 = WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)), Workbooks("bill.xlsx").Sheets("Phone").Range(Workbooks("bill.xlsx").Sheets("Phone").Cells(2, 1), Workbooks("bill.xlsx").Sheets("Phone").Cells(61, 4)), 4, 0)

А к какому файлу и листу относится VLookup, отсюда не видно.

@ykr · 21.06.2021, 17:05 **[ТС]**

Спасибо, сейчас попробую...
вы правы с листами есть вопрос...

Изначально открывается файл "Test_Link.xlsx"
В нем активный лист с именем "Лист2"

И уже далее подтягивается файл "D:\test\bill.xlsx")
и лист "Phone".

Добавлено через 24 минуты
Zeag,

Вы имеете ввиду с помощью Set
Set ah = ActiveSheet
Set sh = Workbooks("bill.xlsx").Sheets("Phone")

как тогда полностью будет выглядеть запись LOOKUP, чтобы прописать в нем оба файла и листа на которых строится функция.
Спасибо!

@snipe · 21.06.2021, 17:14

ykr,
Давайте разбираться вместе
Range - по моему понятию диапазон
т.е. что что ограниченное размерами
соответственно в его описании эти границы обозначены
по секрету скажу что это диагональ из левого верхнего угла в правый нижний угол
в записи
range (cells(x,y),cells(x1,y1))...........
x,y,x1,y1 могут быть переменными
т.е если вы зададите заранее значения для этих переменных
то команда должна отработать

@Zeag · 21.06.2021, 18:08

snipe, x и y заданы у него. Но если Cells или Range идет без указания рабочей книги и/или листа, то они относятся к текущему листу. А это может быть совсем не тот, что предполагается автором.
ykr, я имею в виду запись вида:

Visual Basic
1
2
3
   With ThisWorkbook.Sheets(shCars)
'...
   End With

Set тут не нужен или не обязателен. В записи WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)) - я не знаю, в каком файле вы находитесь, к какому листу относится Cell(7,4).
Поиск я чаще делаю через Find, дело привычки.

Visual Basic
1
2
3
4
5
            Set rngF = F_1.Sheets(1).Columns(xColFind).Find(What:=sNom, LookAt:=xlWhole, SearchDirection:=xlByRows)  ' где ищем в файле 1 (WB)
            If Not rngF Is Nothing Then
               .Cells(k, 1) = F_1.Sheets(1).Cells(rngF.Row, "E")  ' Номенклатура
               .Cells(k, 2) = F_1.Sheets(1).Cells(rngF.Row, "F")  ' Артикул ИМТ
            End If

@ykr · 21.06.2021, 18:47 **[ТС]**

Zeag,

{В записи WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)) - я не знаю, в каком файле вы находитесь, к какому листу относится Cell(7,4). }

Изначально открывается файл "Test_Link.xlsx"
В нем активный лист с именем "Лист2"
К этому файлу и листу относится Cell(7,4).

И уже далее к VLookup подтягивается файл "D:\test\bill.xlsx")
и лист "Phone".

В принципе понятно, часть проверил, работает.
Возможно в коде действительно нужно точно указать, что берется из какой книги, листа ...

Спасибо, ваш способ чуть позже попробую реализовать...

@Zeag · 21.06.2021, 19:14

Тут надо видеть, как вы файл открываете. Идет ли работа в активном или по указателю.
Если файл внешний, то Set FP = Application.Workbooks.Open(Filename:=FN1 , UpdateLinks:=False, Local:=True) и дальше через FP.Sheet(нужный_лист) работаю.

@ykr · 22.06.2021, 09:47 **[ТС]**

Zeag, спасибо!
В идеале хочу работать с двумя внешними эксель файлами, в которых есть данные.
Но при этом управлять этими файлами также из файла эксель, в котром будет только меню и код VBA.

@Zeag · 22.06.2021, 10:40

Именно так я и работаю обычно. В макросе выбираю или запрашиваю имена файлов, открываю по Set и через эти указатели все делаю. Данные и код лучше разделять, давно известно.

@Hugo121 · 22.06.2021, 11:37

Как-то сложно всё в рассуждениях, а суть одна - при каждом применении cells нужно явно указывать родителя.
Удобнее это делать использую with и точку, но можно где-то выше по коду написать как уже упомянуто

Visual Basic
1
Set sh = Workbooks("bill.xlsx").Sheets("Phone")

и тогда далее использовать

Visual Basic
1
sh.Range(sh.Cells(2, 1), sh.Cells(61, 4))

@АЕ · 22.06.2021, 14:45

Сообщение от ykr

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

я бы пошел по другому пути. Просто проименовал диапазоны в этих книгах и к ним бы и обращался, не заморачиваясь на каком конкретно листе все это лежит.

@Hugo121 · 22.06.2021, 17:30

Set sh =... проще.

@ykr · 22.06.2021, 22:27 **[ТС]**

спасибо за помощь!
адаптирую код программы с помощью ваших советов, все фрагменты стали рабочими...
идем дальше.

@ykr · 24.06.2021, 12:55 **[ТС]**

Добрый день! В продолжении темы, появился вопрос с обработкой ошибки, когда VLookup не находит данные.
Не могу перехватить ошибку, если используется WorksheetFunction.VLookup
Не получается использовать:
On Error Resume Next
Rezult_Logic = IsError(WorksheetFunction.VLookup(Range( ah.Cells(L, H1), ah.Cells(L, H1)), sh.Range(sh.Cells(N1, P1), sh.Cells(N2, P2)), H, 0))

Rezult_Logic всегда False, даже на строках, где данные не найдены.

Если используется Application.VLookup - ошибка перехватывается.
Тогда строчка выглядит ка книже и все работает.
On Error Resume Next
Rezult3 = Application.VLookup(Range(ah.Cells(L, H1), ah.Cells(L, H1)), sh.Range(sh.Cells(N1, P1), sh.Cells(N2, P2)), H, 0)

Далее могу анализировать Rezult_Logic и на строках, где данные не найдены равен True.

Но хотелось бы использовать, если надо и функционал WorksheetFunction.VLookup и научиться перехватывать такие ошибки.
Кто поможет? Спасибо!

@Zeag · 24.06.2021, 13:58

Если вы все равно макрос пишете. не проще ли кодом искать? Range.Find применять. Там проверить, найдено ли, очень легко и ошибок (вылетов) не бывает.

@ykr · 24.06.2021, 16:26 **[ТС]**

Zeag, согласен, закончил фрагмент через Lookup, попробую через Range.Find
Но там видимо тоже подводные камни поджидают.

@Narimanych · 24.06.2021, 16:52

ykr,
Вы выложите конкретное задание . Может и find не понадобится...

@Zeag · 24.06.2021, 18:15

ykr, какие именно камни? Там все просто:

Visual Basic
1
2
3
4
5
6
7
' Поискать в файле F_2 на листе 1 в колонке col2 значение strS в режиме: если col1 = 2, то частичного совпадения, иначе полного, перемещаясь по строкам
Set rngF = F_2.Sheets(1).Columns(col2).Find(What:=strS, LookAt:=IIf(col1 = 2, xlPart, xlWhole), SearchDirection:=xlByRows)
If Not rngF Is Nothing Then   ' если Range не пуст, то поиск успешен
   MsgBox "Нашли!"
Else
   MsgBox "Не нашли :-("
End If

Самый цимес, что вылетов не будет. Проверяем rngF на Is Nothing и решаем, что дальше делать.

@Hugo121 · 25.06.2021, 18:39

Если данных нужно найти десяток - можно и find использовать.
Если тысячи - пора переходить на массивы и словари.

@Zeag · 25.06.2021, 19:05

Find и тысячи нормально обработает, за приемлемое время. А если хочется мгновенно - переходить на БД и SQL, ключевые поля делать.

Новые блоги и статьи Все статьи Все блоги /
Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .	Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .
Ломающие изменения в C#.NStar Alpha Etyuhibosecyu 20.11.2025 Уже можно не только тестировать, но и пользоваться C#. NStar - писать оконные приложения, содержащие надписи, кнопки, текстовые поля и даже изображения, например, моя игра "Три в ряд" написана на этом. . .	Мысли в слух kumehtar 18.11.2025 Кстати, совсем недавно имел разговор на тему медитаций с людьми. И обнаружил, что они вообще не понимают что такое медитация и зачем она нужна. Самые базовые вещи. Для них это - когда просто люди. . .	Создание Single Page Application на фреймах krapotkin 16.11.2025 Статья исключительно для начинающих. Подходы оригинальностью не блещут. В век Веб все очень привыкли к дизайну Single-Page-Application . Быстренько разберем подход "на фреймах". Мы делаем одну. . .	Фото: Daniel Greenwood kumehtar 13.11.2025	Расскажи мне о Мире, бродяга kumehtar 12.11.2025 — Расскажи мне о Мире, бродяга, Ты же видел моря и метели. Как сменялись короны и стяги, Как эпохи стрелою летели. - Этот мир — это крылья и горы, Снег и пламя, любовь и тревоги, И бескрайние. . .

@ykr 3 / 3 / 2 Регистрация: 11.01.2019 Сообщений: 125

	Excel Подскажите, как можно в VBA использовать переменные в диапазоне функции Range ? 21.06.2021, 13:05. Показов 3190. Ответов 19 Метки нет (Все метки) Подскажите, как можно в VBA использовать переменные в диапазоне функции Range ? Например, выражение 1 работает корректно. 1). Range(Cells(1, 10), Cells(1, 10)).Value = 300 Далее вместо Cell (1,10) хотел бы использовать Cell (I,K) .... Пытаюсь по аналогии заменить Range("$A2:$D$61") на Range(Cells(2, 1), Cells(61, 4)) - вылетает ошибка 1004 Применяю совместно с LOOKUP. Выражение 2 работает корректно, выражение 3 с ошибкой 1004. 2). Rezult3 = WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)), Workbooks("bill.xlsx").Sheets("Phone").R ange("$A2:$D$61"), 4, 0) 3). Rezult3 = WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)), Workbooks("bill.xlsx").Sheets("Phone").R ange(Cells(2, 1), Cells(61, 4)), 4, 0) При этом доступ к файлу и листу есть. Workbooks.Open "D:\Test\Bill.xlsx" ActiveSheet.Name = "Phone" Может есть какие идеи? Спасибо! 0

@ykr 3 / 3 / 2 Регистрация: 11.01.2019 Сообщений: 125
	21.06.2021, 17:05 [ТС]
	Спасибо, сейчас попробую... вы правы с листами есть вопрос... Изначально открывается файл "Test_Link.xlsx" В нем активный лист с именем "Лист2" И уже далее подтягивается файл "D:\test\bill.xlsx") и лист "Phone". Добавлено через 24 минуты Zeag, Вы имеете ввиду с помощью Set Set ah = ActiveSheet Set sh = Workbooks("bill.xlsx").Sheets("Phone") как тогда полностью будет выглядеть запись LOOKUP, чтобы прописать в нем оба файла и листа на которых строится функция. Спасибо! 0

@snipe 4088 / 1468 / 401 Регистрация: 07.08.2013 Сообщений: 3,667
	21.06.2021, 17:14
	ykr, Давайте разбираться вместе Range - по моему понятию диапазон т.е. что что ограниченное размерами соответственно в его описании эти границы обозначены по секрету скажу что это диагональ из левого верхнего угла в правый нижний угол в записи range (cells(x,y),cells(x1,y1))........... x,y,x1,y1 могут быть переменными т.е если вы зададите заранее значения для этих переменных то команда должна отработать 0

@ykr 3 / 3 / 2 Регистрация: 11.01.2019 Сообщений: 125
	21.06.2021, 18:47 [ТС]
	Zeag, {В записи WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)) - я не знаю, в каком файле вы находитесь, к какому листу относится Cell(7,4). } Изначально открывается файл "Test_Link.xlsx" В нем активный лист с именем "Лист2" К этому файлу и листу относится Cell(7,4). И уже далее к VLookup подтягивается файл "D:\test\bill.xlsx") и лист "Phone". В принципе понятно, часть проверил, работает. Возможно в коде действительно нужно точно указать, что берется из какой книги, листа ... Спасибо, ваш способ чуть позже попробую реализовать... 0

@Zeag 859 / 509 / 187 Регистрация: 09.03.2009 Сообщений: 1,720
	21.06.2021, 19:14
	Тут надо видеть, как вы файл открываете. Идет ли работа в активном или по указателю. Если файл внешний, то Set FP = Application.Workbooks.Open(Filename:=FN1 , UpdateLinks:=False, Local:=True) и дальше через FP.Sheet(нужный_лист) работаю. 0

@ykr 3 / 3 / 2 Регистрация: 11.01.2019 Сообщений: 125
	22.06.2021, 09:47 [ТС]
	Zeag, спасибо! В идеале хочу работать с двумя внешними эксель файлами, в которых есть данные. Но при этом управлять этими файлами также из файла эксель, в котром будет только меню и код VBA. 0

@Zeag 859 / 509 / 187 Регистрация: 09.03.2009 Сообщений: 1,720
	22.06.2021, 10:40
	Именно так я и работаю обычно. В макросе выбираю или запрашиваю имена файлов, открываю по Set и через эти указатели все делаю. Данные и код лучше разделять, давно известно. 0

@Hugo121 6997 / 2895 / 555 Регистрация: 19.10.2012 Сообщений: 8,803
	22.06.2021, 17:30
	Set sh =... проще. 0

@ykr 3 / 3 / 2 Регистрация: 11.01.2019 Сообщений: 125
	22.06.2021, 22:27 [ТС]
	спасибо за помощь! адаптирую код программы с помощью ваших советов, все фрагменты стали рабочими... идем дальше. 0

@ykr 3 / 3 / 2 Регистрация: 11.01.2019 Сообщений: 125
	24.06.2021, 12:55 [ТС]
	Добрый день! В продолжении темы, появился вопрос с обработкой ошибки, когда VLookup не находит данные. Не могу перехватить ошибку, если используется WorksheetFunction.VLookup Не получается использовать: On Error Resume Next Rezult_Logic = IsError(WorksheetFunction.VLookup(Range( ah.Cells(L, H1), ah.Cells(L, H1)), sh.Range(sh.Cells(N1, P1), sh.Cells(N2, P2)), H, 0)) Rezult_Logic всегда False, даже на строках, где данные не найдены. Если используется Application.VLookup - ошибка перехватывается. Тогда строчка выглядит ка книже и все работает. On Error Resume Next Rezult3 = Application.VLookup(Range(ah.Cells(L, H1), ah.Cells(L, H1)), sh.Range(sh.Cells(N1, P1), sh.Cells(N2, P2)), H, 0) Далее могу анализировать Rezult_Logic и на строках, где данные не найдены равен True. Но хотелось бы использовать, если надо и функционал WorksheetFunction.VLookup и научиться перехватывать такие ошибки. Кто поможет? Спасибо! 0

@Zeag 859 / 509 / 187 Регистрация: 09.03.2009 Сообщений: 1,720
	24.06.2021, 13:58
	Если вы все равно макрос пишете. не проще ли кодом искать? Range.Find применять. Там проверить, найдено ли, очень легко и ошибок (вылетов) не бывает. 0

@ykr 3 / 3 / 2 Регистрация: 11.01.2019 Сообщений: 125
	24.06.2021, 16:26 [ТС]
	Zeag, согласен, закончил фрагмент через Lookup, попробую через Range.Find Но там видимо тоже подводные камни поджидают. 0

@Narimanych 2751 / 1725 / 779 Регистрация: 23.03.2015 Сообщений: 5,449
	24.06.2021, 16:52
	ykr, Вы выложите конкретное задание . Может и find не понадобится... 0

@Hugo121 6997 / 2895 / 555 Регистрация: 19.10.2012 Сообщений: 8,803
	25.06.2021, 18:39
	Если данных нужно найти десяток - можно и find использовать. Если тысячи - пора переходить на массивы и словари. 0

@Zeag 859 / 509 / 187 Регистрация: 09.03.2009 Сообщений: 1,720
	25.06.2021, 19:05
	Find и тысячи нормально обработает, за приемлемое время. А если хочется мгновенно - переходить на БД и SQL, ключевые поля делать. 0