Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
VBA - формулы рабочего листа
 
#1
31.05.2010 17:18:27
Добрый день!    
Подскажите, пожалуйста:  
Есть функция:  
=СУММПРОИЗВ(--НЕ(ЕОШИБКА(ПОИСК("пред";'Лист 3'!$A$6:$A$100)))*(A12='Лист 3'!$B$6:$B$100))  
Пытаюсь то же реализовать что-то похожее через ф-цию рабочего листа в VBA. Пока получилось:  
With Application.WorksheetFunction  
tmp = .SumProduct(--.Not(.IsError(.Search("пред", "'Лист 3'!A6:A100"))) * (Cells(ActiveCell(Row), 1).Value = Range("'Лист 3'!B6:B100").Value))  
End With  
Сейчас ругается на Search. Но судя по всему еще много чего по-всплывает.  
Подскажите, можно ли реализовать что-то подобное через функцию рабочего листа?
 
 
 
#2
31.05.2010 17:26:31
Здравствуйте.  
Как минимум в Search необходимо правильно указать диапазон:  
Search("пред", ['Лист 3'!A6:A100])
или    
Search("пред", Sheets("Лист 3").Range("A6:A100"))  
 
Тоже самое и со второй частью формулы:  
Range("'Лист 3'!B6:B100").Value  
Но тут еще и .Value лишнее  
[Лист 3'!B6:B100]
или  
Sheets("Лист 3").Range("B6:B100")  
 
И вот это у Вас неверно записано:  
Cells(ActiveCell(Row), 1).Value  
надо  
Cells(ActiveCell.Row, 1).Value  
 
И еще насколько мне известно функции массива из-под VBA должны заключаться в квадратные скобки  
tmp = [.SumProduct(--.Not(.IsError(.Search("пред", [Лист 3'!B6:B100]))) * (Cells(ActiveCell.Row, 1).Value = [Лист 3'!B6:B100]))]
 
Но это актуально лишь для версии Excel не старше 2003. В 2007 такая формула уже вызовет ошибку.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#3
31.05.2010 17:42:44
Может так:  
 
TMP = Evaluate("=SUMPRODUCT(NOT(ISERROR(SEARCH(""пред"",'Лист 3'!A6:A100)))*(" & Cells(ActiveCell.Row, 1).Value & "='Лист 3'!B6:B100)))")  
 
Не тестировал.
KL
 
 
 
#4
31.05.2010 17:45:44
Кирилл, кстати у меня в 2007 ни одна формула массива ни одним методом не вычисляется(запись на лист не в счет). В то время как в 2003 эти же методы идут на ура. Я и Evaluate припахивал, и скобки квадратные в разных вариациях - по фиг.  
 
Не знаете, случаем, где собака порылась? :-)  
Или это как с методом FileSearch - решили, что лишнее?  
 
P.S. Можно на ты?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#5
31.05.2010 17:56:35
Всем спасибо за ответы.  
Для обоих вариантов получается результат:  
= Error 2015  
Excel 2003  
The_Prist, подскажите, наверное лучше в данном случае все-таки циклом считать(в массиве накапливать результат), а не мастерить такие ф-лы?
 
 
 
#6
31.05.2010 18:03:15
В общем-то достаточно быстро может получиться и циклом. Только необходимо сначала занести данные с листа в массив, а затем в массиве уже цикл по значениям. Это для скорости. Если скорость не важна - можно и прям на листе сверять, без массива...
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#7
31.05.2010 19:53:24
{quote}{login=Grin23}{date=31.05.2010 05:56}{thema=}{post}Всем спасибо за ответы.  
Для обоих вариантов получается результат:  
= Error 2015  
Excel 2003...{/post}{/quote}  
 
А так?  
 
tmp = Evaluate("=SUMPRODUCT(ISNUMBER(SEARCH(""пред"",'Лист 3'!$A$6:$A$100))*(""" & Cells(ActiveCell.Row, 1) & """='Лист 3'!$B$6:$B$100))")
KL
 
 
 
#8
31.05.2010 20:24:08
{quote}{login=The_Prist}{date=31.05.2010 05:45}{thema=}{post}Кирилл, кстати у меня в 2007 ни одна формула массива ни одним методом не вычисляется(запись на лист не в счет). В то время как в 2003 эти же методы идут на ура. Я и Evaluate припахивал, и скобки квадратные в разных вариациях - по фиг.  
 
Не знаете, случаем, где собака порылась? :-)  
Или это как с методом FileSearch - решили, что лишнее?  
 
P.S. Можно на ты?{/post}{/quote}  
На ты - без проблем :-)  
 
У меня вычисляются как в 2007 так и в 2010. В решении, которое я привел ранее, не было учтено то, что во втором условии подставлялось текстовое значение, а оно должно заключаться в кавычки. В принципе массив должны выдавать почти все (если не все) формулы массива через Evaluate("SUMPRODUCT(...)"), [SUMPRODUCT(...)], правда есть функции, которым нужно помочь вернуть массив с помощью TRANSPOSE().
А вот с якобы "формулами" с использованием Application.SumProduct(...) или Application.WorksheetFunction.SumProduct(...) проблема не в последних, а в том, что сравнение или математические операции между массивами при помощи базовых операторов в VBA невозможны (напр.: =, *, / и т.п.) :-)  
 
Надеюсь я не запутал еще больше :-)
KL
 
 
 
#9
31.05.2010 21:00:37
{quote}{login=KL}{date=31.05.2010 08:24}{thema=Re: }{post}Надеюсь я не запутал еще больше :-){/post}{/quote}Не, не запутал. Наоборот.  
Спасибо за разъяснения.  
 
Сейчас попробовал - с Evaluate все работает. Даже не знаю, помутнение что ли какое было - точно помню, что в 2003 работало, а точно такое же выражение в 2007 - фиг...
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
Страницы: 1
Читают тему
Наверх