Всем привет  
 
Сижу уже 2й день над формулой для оранжевой таблицы в аттаче.  
В зеленой таблице правильные ответы.  
 
Нужно выбрать все значения в таблице Odds такие, у которых название = "левый столбец"&"значения из таблицы Events меньше процента в верхней строке"  
 
Т.е.    
для пересечения b и 25% нужно взять среднее по ba и bb (99% и 50%)  
для пересечения c и 100% нужно взять среднее по ca cb cc cd (97% 93% 50% 11%)  
 
Если кто-то знает как сделать в несколько шагов через промежуточную таблицу или как-то что-то добавить к таблицам Odds и Events, то это всё можно сделать.  
 
Пожалуйста добавляйте в аттач файл с формулой, потом что я плохо понимаю формулы на русском, а у большинства на этом форуме как я понимаю русский эксель :)  
 
Реальная таблица 200х200, и комбинаций в odds больше 25000, поэтому руками я это не посчитаю :(  
 
Спасибо

Привет всем,  
 
Возникла следующая проблема:  
На листе есть контролы CheckBox с панели Control Toolbox, у них есть LinkedCell, который соответственно меняется на TRUE/FALSE. В зависимости от TRUE/FALSE пересчитывается ещё с десяток формул от каждого контрола. Надо как-то вызывать код VBA после такого пересчета.  
 
Private Sub Worksheet_Calculate() не подходит, т.к. он выполняется столько раз, сколько ячеек с формулами на листе.  
Код заключается в применении фильтра на столбец, состоящий из true/false, чтобы скрыть ненужную информацию. 500 раз применить фильтр выглядит криво и впринципе нелогично.  
 
Ранее решал эту проблему с помощью Validation полей и соответственно эвента Worksheet_change. Оно работало один раз как надо, но пользователям захотелось ставить галочки вместо выбора да/нет из списка Validation.  
 
Подскажите как можно как-то обойти эти ограничения.  
 
Код ниже:  
Private Sub Worksheet_Calculate()  
   On Error Resume Next  
   ActiveSheet.Unprotect "password"  
   Application.ScreenUpdating = False  
       ShowAllData  
       Range("T1").AutoFilter field:=1, Criteria1:=True  
   ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True  
   Application.ScreenUpdating = True  
End Sub

Приветствую,    
 
Возникла необходимость создать "минималистический" эксель, чтобы он выглядел как отдельно написанная программа почти.  
Начал с скрывания коммандбаров (их естественно надо восстанавливать перед закрытием книги)  
Код:  
 
Option Explicit  
Dim cBars() As CommandBar  
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)  
   Application.ScreenUpdating = False  
   'Restore command bars  
   On Error Resume Next  
   Dim i As Integer  
   For i = 0 To UBound(cBars)  
       cBars(i).Visible = True  
   Next  
   On Error GoTo 0  
   'Protect book  
   ActiveWorkbook.Protect "123", Structure:=True, Windows:=False  
   Application.ScreenUpdating = True  
End Sub  
 
Private Sub Workbook_Open()  
   Application.ScreenUpdating = False  
   'Unprotect book  
   ActiveWorkbook.Unprotect "123"  
   'Get visible command bars, save reference to them and hide them  
   Dim cBar As CommandBar, i As Integer  
   i = 0  
   For Each cBar In Application.CommandBars  
       If cBar.Visible Then  
           On Error GoTo nextfor  
           cBar.Visible = False  
           On Error GoTo 0  
           ReDim Preserve cBars(0 To i)  
           Set cBars(UBound(cBars)) = cBar  
           i = i + 1  
       End If  
nextfor:  
   Next  
   'Activate working sheet  
   Worksheets("Quest").Activate  
   Application.ScreenUpdating = True  
End Sub  
 
Но сохранение в оперативке мне кажется как-то не очень надежным чтоли (возможно при краше экселя или системы инфа о изначальных параметрах пользователя пропадет и потом фиг знает как восстановить её).  
Хранение на листе кажется ненадежным, потому что придется как-то сравнивать по cBar.Name с cstr(worksheets("Hidden").range("A1").value) потом, что мне кажется не очень хорошо. И вообще медленней будет.  
 
Кто что скажет по этому поводу? :)

Столкнулся с задачей масс-форматирования схожих страниц и заметил, что код занимает весьма много времени.  
 
Конкретней, мне надо было изменить на 11 листах ширину строк с 22 по 34 на определенные значения, удалить несколько листов или определенные строки/столбцы на 11 листах.  
 
Ну и собственно был код в 2-х вариантах (с массивом листов и массивом названий листов):  
dim wsName(1 to 11) as String  
wsName(1)="Sheet1"  
...  
wsName(11)="Sheet11"  
 
for j = 1 to 11  
ThisWorkBook.Worksheets(wsName(j)).Rows("24").RowHeight=10  
ThisWorkBook.Worksheets(wsName(j)).Rows("25").RowHeight=15  
...  
ThisWorkBook.Worksheets(wsName(j)).Rows("32").RowHeight=20  
Next  
 
Ну и аналогично для массива листов, было    
ws(j).Rows("24").RowHeight=10 в цикле  
 
Работало это всё медленно, нашел в хелпе обращение к массиву листов Worksheets(Array("Sheet1", "Sheet2", "Sheet3"), и нашел что оно работает с применением строкового массива типа (Worksheets(wsName))  
 
Но, что было неудобно:  
Для того, чтобы например изменить высоту строк, приходилось писать такой код:  
ThisWorkBook.WorkSheets(wsName).Select  
Rows("22").RowHeight=10  
 
То есть выделение листа, и выделение строки на активном листе (ну фиг с ним с ScreenUpdating = false пользователь этого не видит), но мне кажется, что это как-то криво и должна быть возможность сделать это без Select'а.  
А ещё в коде перед этим открывается другой эксель файл и чтобы заработал Select, нужно было писать ThisWorkBook.Activate.  
Мне оч. сильно не нравятся методы Select/Activate, поэтому собственно и создал эту тему :)

Всем доброго утра, задачка следующая:  
Есть строка param, значение может быть "Value1", "Value15", "Value156", "Parameter1", "Parameter25" и т.п.  
Пытаюсь придумать как-нить на VBA или можно с worksheetfunction как забрать цифры справа в одну строчку. Через IFы задавать (if like "Value*", if like "Parameter*" then right(str, len-5), right(str,len-9)) получается как-то громоздко :(

Вопрос по сабжу, не знаю как это называется в русском офисе, находится в Insert->Picture->Organization Chart.  
 
Там есть такая возможность Autoformat на тулбаре, относящемся к нему. Там можно выбрать скины для чарта. Знает ли кто-нибудь можно ли туда добавить свой и если да, то в каком формате рисовать или где почитать про это.

Есть лист с 2-мя наборами option button.  
Соответственно:  
GroupName = Group1  
OptionButton1,OptionButton2,OptionButton3  
GroupName = Group2  
OptionButton4,OptionButton5,OptionButton6  
 
Можно ли как-нибудь сделать формулу в которой будет типа  
If(OptionButton1.value=true,"TRUE","FALSE")  
?

Есть ли способ рассчитать формулу массива в переменную в коде VBA?  
Например есть формула:  
=COUNT(IF(ISNUMBER(C5:C100),C5:C100))  
Считает количество цифр в c5:c100 (в столбце есть цифры, есть "-", и есть пересекающие объединенные ячейки).  
В экселе можно посчитать, ответ у меня 41 например.  
Вот как это можно сделать в экселе  
dim a%  
a= ?????

Можно ли изменить имя файла, не сохраняя его, но так чтобы если нажать кнопку save или file->save он сохранил под этим именем?  
 
Пример:  
1) пользователь открывает файл file1 весом 40МБ  
2) жмет там кнопочку, которая делает много действий по пересчету, удалению, форматированию и т.п. и смотрит на результат  
 
Вот после этого этапа нужно чтобы файл переименовался как-нить типа в filefinal, но чтобы остался старый файл + этот файл не нужен пока пользователь не нажмет save. То есть создать как бы копию в оперативке, а не на жестком диске (как делает thisworkbook.saveas)

Можно ли программно удалить код и формы из книги?  
Причем желательно что-то типа самоуничтожения %) То есть код выполняется (в моем случае меняет формулы на значения и удаляет вспомогательные листы) и после этого удаляются все формы и код из книги.

см. пример  
 
Нужно посчитать процентиль по массиву ячеек на 2-х листах в 2-3-4 столбцах.  
 
Сейчас пока смог придумать формулу на 1 столбец:  
=PERCENTILE(IF(A5:A27="c",OFFSET(A5:A27,0,3),""),0.5)  
соответственно через ctrl-shift-enter.  
Но нужно как-то задать диапазон:  
LIST1!A5:A27 & LIST1!F5:F27 & LIST2!A5:A27 & LIST2!F5:F27  
и соответственно оффсет от тогоже.  
 
Далее желательно вообще как-то задать диапазон в виде  
(LIST1 & LIST2 & LIST3 & LIST4 & LIST5)!Column(1)Row(5):Column(1)Row(27) & Column(1+5)Row(5):Column(1+5)Row(27) & Column(1+5+5)Row(5):Column(1+5+5)Row(27)  
 
Вообщем на 10 листов и на 10 столбцов отстающих друг от друга (в свое время оффсеты для ИФа стоят от этих столбцов везде на 3 колонки вперед.  
 
Ищу гуру формул массивов :)

Как можно определить какой тип листа является activesheet?  
В книге есть листы и чарты, есть немодальная форма, надо по нажатии кнопок определять чарт ли активный лист.  
 
Посмотрел свойства ActiveSheet для чарта и листа, они разные но нет свойства типа Type, по какому свойству можно на 100% определить, что открытый лист - чарт, а не лист с данными?

В прикрепленном файле надо посчитать для каждого столбца B-F только те компании, у которых есть хоть одна запись в этом столбце.  
 
Компаний 40 всего. По столбцу B ответ 40, по C максимум 39 (точно не знаю, но напротив 68-й компании например нету ни одной цифры в столбце C, поэтому её считать не надо.

Есть ли какой-нибудь универсальный OnChange/Select/Activate/Deactivate эвент для формы, возвращающий хотя бы имя контроля, который активировали, поменяли и т.п.?  
 
У меня есть форма на которую добавляется i групп контролей (2-20 строк по 6 контролей в каждой) во время инициализации, соответственно добавить на них на все TextBox1,2-20_change не получается, да и в принципе там довольно универсальный общий код можно написать на их обработку.

Задача такая:  
Есть список  
Arkhangelsk Архангельск  
Astrakhan Астрахань  
Barnaul Барнаул  
Belgorod Белгород  
 
Англ. название в 1-м столбце, русское во 2-м.  
 
Есть книги в которых содержатся разные листы + возможно листы с английским названием города (из списка).  
Нужно выбрать те листы, у которых название - город из списка на английском и сделать запись в общем файле на листе Total в виде.  
 
Имя_файла & Англ. название & "/" & Рус. название.  
--------  
Данные с листа  
--------  
 
Сейчас это реализовано, но имхо как-то криво:  
цикл по файлам (через FSO)  
dim wsht as variant  
dim rng, iCell as range  
dim flag as boolean  
set rng = ThisWorkBook.Sheets("citylist").Range("A1:A30")  
for each wsht in workbooks(InBook)  
flag=false  
 
for each iCell in rng  
 
if wsht.name = icell.value then  
flag=true  
exit for  
end if  
 
next  
 
if flag then    
'''' код копирования с листа в тотал  
end if  
next  
 
енд  
 
Если кто знает как-то попроще поделитесь плз :)

Можно ли как-нибудь получить строки без пробелов, "-", "?", цифр и прочих символов. То есть только буквы.  
 
Например есть список:  
НО отдела АААА123  
НО отдела АААА234  
НО отдела АААА-345  
ЧП, НО отдела ББББ-1  
 
Надо привести к виду:  
НОотделаАААА  
НОотделаАААА  
НОотделаАААА  
ЧПНОотделаБББ  
 
Задача нужна потому что есть исходный список со всякими цифрами и тире и переправленный конечный список без запятых и цифр. Нужно их сопоставить тупо по буквам.

Возможно ли сделать пользовательский прогресс бар для различных функций как на скрине по ссылке?  
http://s45.radikal.ru/i107/0809/e9/118ee33eb533.jpg

Часто вижу что с переменными используют символы $ # &, но не очень понимаю, что они значат.  
 
Примеры:  
http://msoffice.nm.ru/faq/macros/formulas.htm#faq370  
http://msoffice.nm.ru/faq/macros/formulas.htm#faq434  
http://msoffice.nm.ru/faq/macros/miscellaneous.htm#faq251  
 
iFormulas$, iMaskFormula$, iAddress$, iProcent#, iCount& и т.д.  
Что значат эти символы и как их использовать?

Задача в общем:  
Есть 2 таблицы  
1) 3 колонки  
а) имя  
б) значение Х  
в) значение Y  
 
По этой таблице нужно построить точечный график, чтобы при наведении на точку можно было узнать имя из этой строки (любым способом). Точки соответственно с координатами х, у.  
Точек около 1000-2000.  
 
2) 5 колонок  
а) имя  
б) минХ  
в) максХ  
г) минУ  
д) максУ  
 
По этой нужно построить квадраты с координатами (минХ, минУ) (минХ, максУ) (максХ, максУ) (максХ, минУ). Квадрат соответственно должен быть виден (с линиями граней), а не просто 4 точки.  
 
В примере показываю как я делаю это сейчас. Основная проблема - макс. кол-во серий 255, а я создаю новую серию на каждую точку (если делать серию точек, то не видно имени каждой отдельной точки). Если кто может поделиться идеями буду признателен :)

Вообщем тестил разные пропертис/методы Application'а и в итоге получается так, что когда закрываю сам эксель иногда вместо него закрывается lotus notes или какая-нибудь закладка проводника. Все книги получается закрывать через file->close без проблем, но когда закрываю непосредственно эксель он с 1-й попытки выключает какую-нибудь другую программу, со 2-й уже обычно себя.  
 
Звучит бредово, но как-то надо исправить :))

В файле есть несколько листов типа "ABC 1-25", "ABC 26-50", "ABC 51-75" и т.д. Заканчиваются чем-то типа "ABC 300-316".  
Листы ессно одинаковые по структуре и периодически по ним надо делать одинаковые процедуры. Пока обхожусь весьма кривым вариантом:  
внутри каждой процедуры объявляю  
dim lst(10) as string  
lst(1) = "ABC 1-25"  
lst(2) = "ABC 26-50"  
....  
lst(10) = "ABC 300-316"  
dim lstname as string  
 
for j=1 to 10  
   lstname=lst(j)  
   with ThisWorkbook.Worksheets(lst).  
    процедура по листу  
   end with  
next  
 
Не нравится тем, что во-первых не получается прописать этот массив глобально на книгу, во-вторых кажется что можно как-то по-другому и лучше, но не знаю как.  
 
Кто как поступает в таких ситуациях поделитесь плз :)

Вобщем когда жму в тулбоксе на additional controls VBA чего то думает 1 секунду и ничего не происходит. У кого-нибудь есть мысли почему такое может быть? :)  
 
з.ы.: ms excel 2003 SP3 english

Подскажите пожалуйста как можно ускорить процесс.  
Задача такая: таблица побита на квадраты 19х9 на 11 листах. На каждом листе 90*28 квадратов.  
Нужно посчитать в 2-м столбце каждого квадрата количество заполненных ячеек 1-го столбца.    
В 5-м столбце с учетом условия в 3-м посчитать кол-во заполненных ячеек 4-го столбца.  
В 6-м и 8-м надо рассчитать все 19 строк типа 1+4, или 4/(4+1)  
Вообщем-то по GetTickCount любые рассчеты выполняются одинаково по приблизительно 50мс, 1 табличка 3.5 сек. Соответственно 90*28*11 * 3.5 будет очень долго. (1617 минут).  
 
Код примерно:  
for j= 1 to 11  
list = ListName(j)  
for col = 5 to 140 step 9  
for i = 1 to 1880 step 21  
 With Worksheets(list).Cells(i, col)  
      'так ввожу формулу "=COUNTIF(D11:D30, ">0")"  
      .FormulaR1C1 = "=COUNTIF(R" & i & "C" & col - 1 & ":R" & i + 19 & "C" & col - 1 & "," & Chr(34) & ">0" & Chr(34) & ")"  
      'так я заменяю формулу на конкретное значение  
      .value=.value  
      'далее адресую с помощью .offset(x,y) на нужные ячейки внутри "подтаблицы", в 1 цикле for k=1 to 19 прохожу по строкам 6-го 8-го столбца  
 end with  
 
next  
next  
next  
 
Если убрать замену формулы на значение будет долго грузиться потом сам файл, поэтому поставили задачу рассчитывать формулы по кнопке. Не особо знаком с проблемами производительности, поэтому решил сделать так. Подскажите если кто знает как ускорить процесс.

Помогите плз, нужно присвоить приблизительно 100 ячейкам, идущим подряд в столбце гиперссылки на другой лист в этой же книге.  
Примерно    
a1 -> list2!b2  
a2 -> list2!d2  
a3 -> list2!f2  
 
Текст в самих a1,a2,a3... берется vlookup-ом по соседней ячейки с 3-го листа и не должны меняться из-за присвоения гиперссылки.