Подскажите, как можно в 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, ключевые поля делать.

Новые блоги и статьи Все статьи Все блоги /
Blazor и контроллер сервопривода IoT Meadow Maple Wired 11.07.2025 Я решил разобраться, как можно соединить современные веб-технологии с миром "железа". Интересная комбинация получилась из Blazor в качестве веб-интерфейса и микроконтроллера Meadow с его веб-сервером. . .	Генерация OpenQASM из кода Q# EggHead 10.07.2025 Летом 2024-го я начал эксперименты с библиотекой Q# Bridge, и знаете что? Она оказалась просто находкой для тех, кто работает на стыке разных квантовых экосистем. Основная фишка этой библиотеки -. . .	Изучаем новый шаблон ИИ-чата .NET AI Chat Web App stackOverflow 10.07.2025 В . NET появилось интересное обновление - новый шаблон ИИ-чата под названием . NET AI Chat Web App. Когда я впервые наткнулся на анонс этого шаблона, то сразу понял, что Microsoft наконец-то. . .	Результаты исследования от команды ARP (июль 2025 г.) Programma_Boinc 10.07.2025 Результаты исследования от команды ARP (июль 2025 г. ) Африканский проект по дождям (ARP) World Community Grid снова запущен! Мы рады поделиться обновленной информацией о нашем прогрессе с осени. . .	Angular vs Svelte - что лучше? Reangularity 09.07.2025 Сегодня рынок разделился на несколько четких категорий: тяжеловесы корпоративного уровня (Angular), гибкие универсалы (React), прогрессивные решения (Vue) и новая волна компилируемых фреймворков. . .
Code First и Database First в Entity Framework UnmanagedCoder 09.07.2025 Entity Framework дает нам свободу выбора, предлагая как Code First, так и Database First подходы. Но эта свобода порождает вечный вопрос — какой подход выбрать? Entity Framework — это. . .	Как использовать Bluetooth-модуль HC-05 с Arduino Wired 08.07.2025 Bluetooth - это технология, созданная чтобы заменить кабельные соединения. Обычно ее используют для связи небольших устройств: мобильных телефонов, ноутбуков, наушников и т. д. Работает она на частоте. . .	Руководство по структурам данных Python AI_Generated 08.07.2025 Я отчетливо помню свои первые серьезные проекты на Python - я писал код, он работал, заказчики были относительно довольны. Но однажды мой наставник, взглянув на мою реализацию поиска по огромному. . .	Тестирование энергоэффективности и скорости вычислений видеокарт в BOINC проектах Programma_Boinc 08.07.2025 Тестирование энергоэффективности и скорости вычислений видеокарт в BOINC проектах Опубликовано: 07. 07. 2025 Рубрика: Uncategorized Автор: AlexA Статья размещается на сайте с разрешения. . .	Раскрываем внутренние механики Android с помощью контекста и манифеста mobDevWorks 07.07.2025 Каждый Android-разработчик сталкивается с Context и манифестом буквально в первый день работы. Но много ли мы задумываемся о том, что скрывается за этими обыденными элементами? Я, честно говоря,. . .

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

	Excel Подскажите, как можно в VBA использовать переменные в диапазоне функции Range ? 21.06.2021, 13:05. Показов 2873. Ответов 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 Сообщений: 121
	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 4087 / 1467 / 401 Регистрация: 07.08.2013 Сообщений: 3,665
	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 Сообщений: 121
	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 / 508 / 187 Регистрация: 09.03.2009 Сообщений: 1,713
	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 Сообщений: 121
	22.06.2021, 09:47 [ТС]
	Zeag, спасибо! В идеале хочу работать с двумя внешними эксель файлами, в которых есть данные. Но при этом управлять этими файлами также из файла эксель, в котром будет только меню и код VBA. 0

@Zeag 859 / 508 / 187 Регистрация: 09.03.2009 Сообщений: 1,713
	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 Сообщений: 121
	22.06.2021, 22:27 [ТС]
	спасибо за помощь! адаптирую код программы с помощью ваших советов, все фрагменты стали рабочими... идем дальше. 0

@ykr 3 / 3 / 2 Регистрация: 11.01.2019 Сообщений: 121
	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 / 508 / 187 Регистрация: 09.03.2009 Сообщений: 1,713
	24.06.2021, 13:58
	Если вы все равно макрос пишете. не проще ли кодом искать? Range.Find применять. Там проверить, найдено ли, очень легко и ошибок (вылетов) не бывает. 0

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

@Narimanych 2751 / 1725 / 779 Регистрация: 23.03.2015 Сообщений: 5,447
	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 / 508 / 187 Регистрация: 09.03.2009 Сообщений: 1,713
	25.06.2021, 19:05
	Find и тысячи нормально обработает, за приемлемое время. А если хочется мгновенно - переходить на БД и SQL, ключевые поля делать. 0