Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / И снова vblookup... / 22 сообщений из 22, страница 1 из 1
20.06.2007, 22:04:26
    #34609134
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
Разложил всё по полочкам, т.к. уже два дня с этим вопросом вожусь. Надеюсь, что это реальная затея. Помогите советом, 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
| Ответить | Цитировать | Написать  
21.06.2007, 01:23:53
    #34609287
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
Давно хотел тебе ответит. но всё всремени не хватало нормально покумекать.

Варианта на мой взгляд тут 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:27:23
    #34609291
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
З.ы.: Сам грешным делом думал, что это делаетя полегче. Может оно и правда легко а я загнался.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.06.2007, 12:08:22
    #34610246
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
to Deggasad:
Первый вариант выдаёт значение без ссылок на три переменные, поэтому я не могу перемещать формулу или меняя значения переменных на листе получать другое значение формулы. (А ради этого, собственно и вся затея:) )

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

Ну думай сам оба варианта работают:
Первый проще но надо в цикле запускать. Зато потом в ячейках нормальные формулы
Второй как раз то что ты хотел, как мне кажется! Но сложнее!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.06.2007, 13:58:18
    #34610839
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
to Deggasad.
Значит недопонял я ещё как этот цикл вставить. Я только учусь. В таком случае БОЛЬШОЕ спасибо!!!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.06.2007, 12:54:19
    #34615754
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
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, 14:55:04
    #34615823
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
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, 16:49:17
    #34615903
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
Спасибо, но думаю второй варант будет удобнее. Потому что у меня в Таблице разбросано около 2000 ячеек, в которых должен произойти перерассчёт (при этом по первому варианту для перерассчёта будет запрос: указать файл. 2000 раз ) и на РАЗНОМ удалении от них находится 35 дат.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.06.2007, 19:50:50
    #34616021
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
redskyСпасибо, но думаю второй варант будет удобнее. Потому что у меня в Таблице разбросано около 2000 ячеек, в которых должен произойти перерассчёт (при этом по первому варианту для перерассчёта будет запрос: указать файл. 2000 раз ) и на РАЗНОМ удалении от них находится 35 дат.

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

Но если не хочется заморачиваться. наверное можно и вторым воспользоваться. Хоть он и корявый на мой взгляд! Я просто не знаю как по другому!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2007, 01:08:08
    #34616226
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
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, 08:27:21
    #34616335
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
Да уж прикол! Я же знал что должно работать! У тебя в ниже приведённом макросе
В строке 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:41:09
    #34616356
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
redsky Deggasad2) Изменение формул макросом можно запускать только когда меняется диапазон с датами. Если будет время, то подскажите, как это сделать?

Смотри пример!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2007, 08:58:38
    #34616379
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
Чё то я там напутал. Лучше этот пример!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2007, 15:55:49
    #34617871
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
DeggasadЛучше этот пример!Прикрасно работает! СПАСИБО за уделённое время.
Второй вариант у меня выдаёт значение только когда запускается макросом.
Если использовать функцию func на странице, то появляется ошибка значения. Это связанно с форматом введённых значений в ячейках или как?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2007, 16:45:26
    #34618115
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
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:48:26
    #34618127
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
Если строку поменял, то значение для поиска без кавычек пиши в ячейках
А до замены строки чтобы работало нужно было в кавычках двойных значение писать "..."
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2007, 17:24:58
    #34618258
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
to Degassad:
Громадное спасибо!

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

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

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

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

???

Как мёд, так и ложку :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2007, 23:55:35
    #34618827
Deggasad
Deggasad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
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
| Ответить | Цитировать | Написать  
26.06.2007, 00:26:36
    #34618852
redsky
redsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова vblookup...
Thanks a lot!
ЛЯПОТАААААААА!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / И снова vblookup... / 22 сообщений из 22, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 298ms
Закрыть
start [/forum/topic.php?fid=61&mobile=1&tid=2182708]:
 0ms
get settings:
 5ms
get forum list:
 8ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 33ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 29ms
get tp. blocked users:
 1ms
others: 211ms
total:  298ms
x
x
Закрыть


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