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

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

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

Подскажите, как можно в 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").Range("$A2:$D$61"), 4, 0)

3). Rezult3 = WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)), Workbooks("bill.xlsx").Sheets("Phone").Range(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

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

            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

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

@ykr 1 / 1 / 1 Регистрация: 11.01.2019 Сообщений: 79
		1
	Excel Подскажите, как можно в VBA использовать переменные в диапазоне функции Range ? 21.06.2021, 13:05. Показов 2346. Ответов 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").Range("$A2:$D$61"), 4, 0) 3). Rezult3 = WorksheetFunction.VLookup(Range(Cells(7, 4), Cells(7, 4)), Workbooks("bill.xlsx").Sheets("Phone").Range(Cells(2, 1), Cells(61, 4)), 4, 0) При этом доступ к файлу и листу есть. Workbooks.Open "D:\Test\Bill.xlsx" ActiveSheet.Name = "Phone" Может есть какие идеи? Спасибо! 0

@ykr 1 / 1 / 1 Регистрация: 11.01.2019 Сообщений: 79
	21.06.2021, 17:05 [ТС]	3
	Спасибо, сейчас попробую... вы правы с листами есть вопрос... Изначально открывается файл "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 4076 / 1456 / 401 Регистрация: 07.08.2013 Сообщений: 3,629
	21.06.2021, 17:14	4
	ykr, Давайте разбираться вместе Range - по моему понятию диапазон т.е. что что ограниченное размерами соответственно в его описании эти границы обозначены по секрету скажу что это диагональ из левого верхнего угла в правый нижний угол в записи range (cells(x,y),cells(x1,y1))........... x,y,x1,y1 могут быть переменными т.е если вы зададите заранее значения для этих переменных то команда должна отработать 0

@ykr 1 / 1 / 1 Регистрация: 11.01.2019 Сообщений: 79
	21.06.2021, 18:47 [ТС]	6
	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 834 / 484 / 186 Регистрация: 09.03.2009 Сообщений: 1,664
	21.06.2021, 19:14	7
	Тут надо видеть, как вы файл открываете. Идет ли работа в активном или по указателю. Если файл внешний, то Set FP = Application.Workbooks.Open(Filename:=FN1, UpdateLinks:=False, Local:=True) и дальше через FP.Sheet(нужный_лист) работаю. 0

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

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

@АЕ ᴁ® 3811 / 2147 / 452 Регистрация: 13.12.2016 Сообщений: 7,359 Записей в блоге: 5
	22.06.2021, 14:45	11
	Сообщение от ykr В идеале хочу работать с двумя внешними эксель файлами, в которых есть данные. Но при этом управлять этими файлами также из файла эксель, в котром будет только меню и код VBA. я бы пошел по другому пути. Просто проименовал диапазоны в этих книгах и к ним бы и обращался, не заморачиваясь на каком конкретно листе все это лежит. 0

@Hugo121 6930 / 2838 / 545 Регистрация: 19.10.2012 Сообщений: 8,670
	22.06.2021, 17:30	12
	Set sh =... проще. 0

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

@ykr 1 / 1 / 1 Регистрация: 11.01.2019 Сообщений: 79
	24.06.2021, 12:55 [ТС]	14
	Добрый день! В продолжении темы, появился вопрос с обработкой ошибки, когда 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 834 / 484 / 186 Регистрация: 09.03.2009 Сообщений: 1,664
	24.06.2021, 13:58	15
	Если вы все равно макрос пишете. не проще ли кодом искать? Range.Find применять. Там проверить, найдено ли, очень легко и ошибок (вылетов) не бывает. 0

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

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

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

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