ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / И снова vblookup...
22 сообщений из 22, страница 1 из 1
И снова vblookup...
    #34609134
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
Разложил всё по полочкам, т.к. уже два дня с этим вопросом вожусь. Надеюсь, что это реальная затея. Помогите советом, Please. Может быть надо поменять тип переменных?

Есть папка: C:\Таблицы\
В ней очень много документов вида:
C 01.01.2007 по 18.01.2007
C 01.01.2007 по 21.03.2007.xls
C 01.01.2007 по 03.03.2007.xls
C 01.01.2007 по 15.05.2007.xls
C 01.04.2007 по 08.06.2007.xls
C 01.04.2007 по 21.04.2007.xls
C 01.05.2007 по 29.05.2007.xls
C 01.05.2007 по 01.06.2007.xls
C 01.03.2007 по 13.06.2007.xls
C 01.03.2007 по 18.06.2007.xls
................
В каждом из этих документов меня интересует массив "C4:X450"
Искомое значение находится в первом столбце массива.
Надо:
написать функцию типа VBLOOKUP с тремя переменными:
1. искомое значение (должно совпадать со значением в первом столбце массива)
2. дата1 (первая дата в названии документа)
3. дата2 (вторая дата в названии тогоже документа)
Код:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Function func(ByVal значение As String, ByVal дата1 As String, ByVal дата2 As String)
   Dim sdf As Range
   Dim sdf1 As String
   Dim sdf2 As String
   Dim sdf3 As String
   Dim sdf4 As String
   sdf1 = "'C:\Таблицы\["
   sdf2 = "C "
   sdf3 = " по "
   sdf4 = ".xls]Лист1'!C4:X450"
   sdf = sdf1 + sdf2 + дата1 + sdf3 + дата2 + sdf4
   func = WorksheetFunction.VLookup(значение, sdf,  2 , False)
End Function

Выдаёт, что ошибка в значении! Что не так???
...
Рейтинг: 0 / 0
20.06.2007, 22:04:26
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34609287
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
Давно хотел тебе ответит. но всё всремени не хватало нормально покумекать.

Варианта на мой взгляд тут 2:

1) Это записыват формулу в ячейку Эксель. тогда считает. А потом уже с ячейкой что хотим.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
Sub func2(ByVal значение As Variant, ByVal дата1 As String, ByVal дата2 As String)
   Dim sdf As String
   Dim sdf1 As String
   Dim sdf2 As String
   Dim sdf3 As String
   Dim sdf4 As String
   sdf1 = "'C:\Таблицы\["
   sdf2 = "C "
   sdf3 = " по "
   sdf4 = ".xls]Лист1'!C4:X450"
   sdf = sdf1 + sdf2 + дата1 + sdf3 + дата2 + sdf4
   
   Debug.Print "=VLookup(" & значение & "," & sdf & ", 2, False)"
   Range("A1").Formula = "=VLookup(" & значение & "," & sdf & ", 2, False)"
   
End Sub

Запускаем так

Код: plaintext
1.
2.
Sub sad()
    func2 """Февраль""", "01.03.2007", "18.06.2007"
End Sub

2) Такая функция через ADO, сам в этом толком не понимаю, но вот так работает

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
Function func(ByVal значение As Variant, ByVal дата1 As String, ByVal дата2 As String)
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
    
   Set cnn = New ADODB.Connection
   Set rst = New ADODB.Recordset
   Dim myArray As Variant
    
   Dim sdf As String
   Dim sdf1 As String
   Dim sdf2 As String
   Dim sdf3 As String
   Dim sdf4 As String
   Dim sdf5 As String
   Dim n ' номер столбца в диапазоне
   n =  2 
   
   sdf1 = "C:\Таблицы\"
   sdf2 = "C "
   sdf3 = " по "
   sdf4 = ".xls"
   sdf5 = "[Лист1$C4:X450]"
   
   sdf = sdf1 + sdf2 + дата1 + sdf3 + дата2 + sdf4
    
    
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & sdf & ";" & _
      "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
    
    rst.Open "SELECT * FROM " & sdf5 & "WHERE F1 =" & значение, cnn
    
    myArray = rst.GetRows

    func = myArray(n -  1 ,  0 )
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
   
End Function

Запускаем так

Код: plaintext
1.
2.
Sub sad()
    Debug.Print func("'Февраль'", "01.03.2007", "18.06.2007")
End Sub
...
Рейтинг: 0 / 0
21.06.2007, 01:23:53
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34609291
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
З.ы.: Сам грешным делом думал, что это делаетя полегче. Может оно и правда легко а я загнался.
...
Рейтинг: 0 / 0
21.06.2007, 01:27:23
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34610246
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
to Deggasad:
Первый вариант выдаёт значение без ссылок на три переменные, поэтому я не могу перемещать формулу или меняя значения переменных на листе получать другое значение формулы. (А ради этого, собственно и вся затея:) )

Второй вариант пока не могу проверить, потому что админ поставил урезаный Office :(
...
Рейтинг: 0 / 0
21.06.2007, 12:08:22
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34610303
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
И ещё по первому варианту: после открытия связи не обновляются:(
...
Рейтинг: 0 / 0
21.06.2007, 12:20:31
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34610719
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
redskyto Deggasad:
Первый вариант выдаёт значение без ссылок на три переменные, поэтому я не могу перемещать формулу или меняя значения переменных на листе получать другое значение формулы. (А ради этого, собственно и вся затея:) )

Ну думай сам оба варианта работают:
Первый проще но надо в цикле запускать. Зато потом в ячейках нормальные формулы
Второй как раз то что ты хотел, как мне кажется! Но сложнее!
...
Рейтинг: 0 / 0
21.06.2007, 13:36:04
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34610839
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
to Deggasad.
Значит недопонял я ещё как этот цикл вставить. Я только учусь. В таком случае БОЛЬШОЕ спасибо!!!
...
Рейтинг: 0 / 0
21.06.2007, 13:58:18
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34615754
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
to Deggasad.
К первому варианту. Если ты имел ввиду так:
Код: plaintext
1.
2.
Function func3(ByVal значение As Variant, ByVal дата1 As String, ByVal дата2 As String)
func2 значение, дата1, дата2
End Function
то ссылки есть, но выдаётся "ошибка в значении". Числовой формат ячеек с "дата1", "дата2" - дата; ячейки со "значением" - общий. При этом, я так понимаю, в формуле значение появляется без кавычек "значение".
...
Рейтинг: 0 / 0
24.06.2007, 12:54:19
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34615823
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
redskyto Deggasad.
К первому варианту. Если ты имел ввиду так:
Код: plaintext
1.
2.
Function func3(ByVal значение As Variant, ByVal дата1 As String, ByVal дата2 As String)
func2 значение, дата1, дата2
End Function
то ссылки есть, но выдаётся "ошибка в значении". Числовой формат ячеек с "дата1", "дата2" - дата; ячейки со "значением" - общий. При этом, я так понимаю, в формуле значение появляется без кавычек "значение".

Вот посмотри
...
Рейтинг: 0 / 0
24.06.2007, 14:55:04
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34615903
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
Спасибо, но думаю второй варант будет удобнее. Потому что у меня в Таблице разбросано около 2000 ячеек, в которых должен произойти перерассчёт (при этом по первому варианту для перерассчёта будет запрос: указать файл. 2000 раз ) и на РАЗНОМ удалении от них находится 35 дат.
...
Рейтинг: 0 / 0
24.06.2007, 16:49:17
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34616021
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
redskyСпасибо, но думаю второй варант будет удобнее. Потому что у меня в Таблице разбросано около 2000 ячеек, в которых должен произойти перерассчёт (при этом по первому варианту для перерассчёта будет запрос: указать файл. 2000 раз ) и на РАЗНОМ удалении от них находится 35 дат.

Думаю всё таки первый вариант лучше.
1) После однократного выполнения в ячейке у вас обычная формула Эксель
2) Изменение формул макросом можно запускать только когда меняется диапазон с датами.
3) Файл указывать не нужно Если правильно записан путь в строковых переменных. Файл спрашивается только если путь неверный!
4) То что даты не слева так можно же и подругому их брать главное указать какая там закономерность

Но если не хочется заморачиваться. наверное можно и вторым воспользоваться. Хоть он и корявый на мой взгляд! Я просто не знаю как по другому!
...
Рейтинг: 0 / 0
24.06.2007, 19:50:50
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34616226
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
Deggasad3) Файл указывать не нужно Если правильно записан путь в строковых переменных. Файл спрашивается только если путь неверный!У меня файл спрашивался даже если путь верный.
А в следующем исполнении, работает не спрашивая :
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
Sub Расчет5()
 Dim sdf$
 Dim значение$
 Dim fds As String
 Dim sdfs As String
  
  For Each icel In Selection
    значение = icel.Offset(, - 3 ).Address( 0 ,  0 )
    sdf = "'C:\Таблицы\[С 01.01.2007 по 01.04.2007.xls]Лист1'!$C$4:$X$450"
    icel.Formula = "=VLookup(" & значение & "," & sdf & ", 2, False)"
 
fds = Format(icel.Offset(, - 2 ).Value, "DD.MM.YYYY")
    icel.Replace What:="01.01.2007", Replacement:=fds
  
sdfs = Format(icel.Offset(, - 1 ).Value, "DD.MM.YYYY")
    icel.Replace What:="01.04.2007", Replacement:=sdfs
   Next icel
End Sub
Интересно, такой вариант с использованием функции Replace можно загнать в формулу?
Теперь я хочу: Deggasad2) Изменение формул макросом можно запускать только когда меняется диапазон с датами. Если будет время, то подскажите, как это сделать?
...
Рейтинг: 0 / 0
25.06.2007, 01:08:08
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34616335
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
Да уж прикол! Я же знал что должно работать! У тебя в ниже приведённом макросе
В строке sdf2 = "С " , буква С , стоит английская, а не русская. Это не я поставил, я скопировал с твоего макроса, оттуда и пошла ошибка видимо. Переключись на русскую раскладку и поставь русскую С должно работать!



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
Sub Расчет()
 Dim sdf$, sdf1$, sdf2$, sdf3$, sdf4$
 Dim значение$, дата1$, дата2$
  
  sdf1 = "'Y:\Таблицы\["
  sdf2 = "С "
  sdf3 = " по "
  sdf4 = ".xls]Лист1'!C4:X450"

  For Each icel In Selection
    значение = icel.Offset(, - 3 ).Address( 0 ,  0 )
    дата1 = Format(icel.Offset(, - 2 ).Value, "DD.MM.YYYY")
    дата2 = Format(icel.Offset(, - 1 ).Value, "DD.MM.YYYY")
    sdf = sdf1 + sdf2 + дата1 + sdf3 + дата2 + sdf4
    icel.Formula = "=VLookup(" & значение & "," & sdf & ", 2, False)"
  Next icel
  
End Sub
...
Рейтинг: 0 / 0
25.06.2007, 08:27:21
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34616356
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
redsky Deggasad2) Изменение формул макросом можно запускать только когда меняется диапазон с датами. Если будет время, то подскажите, как это сделать?

Смотри пример!
...
Рейтинг: 0 / 0
25.06.2007, 08:41:09
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34616379
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
Чё то я там напутал. Лучше этот пример!
...
Рейтинг: 0 / 0
25.06.2007, 08:58:38
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34617871
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
DeggasadЛучше этот пример!Прикрасно работает! СПАСИБО за уделённое время.
Второй вариант у меня выдаёт значение только когда запускается макросом.
Если использовать функцию func на странице, то появляется ошибка значения. Это связанно с форматом введённых значений в ячейках или как?
...
Рейтинг: 0 / 0
25.06.2007, 15:55:49
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34618115
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
redsky DeggasadЛучше этот пример!Прикрасно работает! СПАСИБО за уделённое время.
Второй вариант у меня выдаёт значение только когда запускается макросом.
Если использовать функцию func на странице, то появляется ошибка значения. Это связанно с форматом введённых значений в ячейках или как?

А если строку
Код: plaintext
  rst.Open "SELECT * FROM " & sdf5 & "WHERE F1 =" & значение, cnn

Заменить на строку
Код: plaintext
  rst.Open "SELECT * FROM " & sdf5 & "WHERE F1 =" & "'" & значение & "'", cnn

Работает ????
...
Рейтинг: 0 / 0
25.06.2007, 16:45:26
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34618127
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
Если строку поменял, то значение для поиска без кавычек пиши в ячейках
А до замены строки чтобы работало нужно было в кавычках двойных значение писать "..."
...
Рейтинг: 0 / 0
25.06.2007, 16:48:26
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34618258
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
to Degassad:
Громадное спасибо!

УРААААААААААААААААААААААААААА!

Сказка, СУППЕР, Отлично, прекрасно, то, что надо.
Это то, чего я и хотел в идеале.

Спасибо, что выдержали мою настойчивость. Уж больно это удобная для меня функция.
...
Рейтинг: 0 / 0
25.06.2007, 17:24:58
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34618665
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
А можно, чтобы в случае отсутствия значения в источнике, функция (из второго варианта) выдавала не #ЗНАЧ, а 0. И это было прописано в коде а не в ячейке в виде:

=ЕСЛИ(ЕОШИБКА(func(A2;B2;C2))=ИСТИНА;;func(A2;B2;C2))

???

Как мёд, так и ложку :-)
...
Рейтинг: 0 / 0
25.06.2007, 20:18:17
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34618827
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
redsky
Как мёд, так и ложку :-)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
Function func(ByVal значение As Variant, ByVal дата1 As String, ByVal дата2 As String)
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Set cnn = New ADODB.Connection
   Set rst = New ADODB.Recordset
   Dim myArray As Variant
    
   Dim sdf$, sdf1$, sdf2$, sdf3$, sdf4$, sdf5$
   Dim n ' номер столбца в диапазоне
    n =  2 
   
   sdf1 = "C:\Таблицы\": sdf2 = "C ": sdf3 = " по ":  sdf4 = ".xls": sdf5 = "[Лист1$C4:X450]"
   
   sdf = sdf1 + sdf2 + дата1 + sdf3 + дата2 + sdf4
      
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & sdf & ";" & _
      "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
    
    rst.Open "SELECT * FROM " & sdf5 & "WHERE F1 =" & "'" & значение & "'", cnn
    
    On Error Resume Next
      myArray = rst.GetRows
      func = myArray(n -  1 ,  0 )
    On Error GoTo  0 
     If func = "" Then func =  0 
    
    rst.Close: cnn.Close:  myArray = "":  Set rst = Nothing:  Set cnn = Nothing
End Function

Поробуй так!
Я знаю, что можно лучше. Просто я почти ничего сам в этом не понимаю, только начал разбираться! Поэтому Если кто поправит буду благодарен! Но вроде и так работает, только медленно-о-о-о-о-о-о!
...
Рейтинг: 0 / 0
25.06.2007, 23:55:35
| Ответить | Цитировать | Написать  
И снова vblookup...
    #34618852
redsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
redsky
Участник
Thanks a lot!
ЛЯПОТАААААААА!
...
Рейтинг: 0 / 0
26.06.2007, 00:26:36
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / И снова vblookup...
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&msg=34610246&tid=2182708]:
 0ms
get settings:
 11ms
get forum list:
 19ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 35ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 74ms
get tp. blocked users:
 2ms
others: 245ms
total:  405ms
созд. / загр.: 405ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]