» Excel VBA (часть 3)

Alex_Piggy
Большое спасибо!

AndVGri
Спасибо Вам за подсказку. Вставил Ваш код, но всё равно все ячейки очищаются... Я снова записал все действия через макро, в окне "Найти и заменить" нажал "ctrl+A" поистил нужные ячейки. Я так понимаю, макрорекордер не записывает действие "ctrl+A" после поиска, поэтому всё и очищается. Помогите прописать, чтобы очищались только те ячейки, шрифт в которых белый.

Leojse

Попробуйте так:

Код: Sub test()
Dim r As Range

For Each r In Worksheets(1).UsedRange
If r.Font.Color = vbWhite Then
r.Value = ""
End If
Next
End Sub

andrewkard1980, спасибо Вам за ответ!
Порылся еще в инете и слепил свой код:

Код: Sub Macros()
For Each cell In Selection
If cell.Font.Color = 16777215 Then cell.Value = ""
Next
End Sub

Здравствуйте, есть задачка по раскраске ячеек в Ecxel 2013 в зависимости от условий, вот содержание задачи:задана следующая формула в ячейке K2: =ЕСЛИ(J2=0;СЕГОДНЯ();A2+45)
Как задать два цвета для ячейки К2 в случае, если J2=0, раскрасить в зелёный цвет дату СЕГОДНЯ(), иначе в красный цвет, но за 7 дней до наступления даты А2+45 ?
В Условном форматировании не нашёл как такой метод реализовать, а в макросах не силён. Буду благодарен за помощь.

Доброе время, serg3001
Странно. Как раз через условное форматирование (или я неправильно понял задачу?). Если сегодня K2, то зеленый, если неделя до K2, то - без форматирования, если до K2 больше недели, то красный.
Условие 1
Значение Равно "=Сегодня()"
Формат - Вид - Заливка ячейки - Зеленый
Условие 2
Значение Больше "=Сегодня()+7"
Формат - Вид - Заливка ячейки - Красный
PS. Кстати, вопрос - А если K2 уже было? Может лучше условие1
Значение МеньшеИлиРавно "=Сегодня()"
Или условие 2
Значение Вне "=Сегодня()" "=Сегодня()+7"
PPS. Формулы - без кавычек.

Alex_Piggy, приветствую, с зелёным цветом тоже вчера решил через УФ, немного переформулирую задачу, там есть ещё ячейка J2, если у J2 значение=0, то зелёный для К2, если сегодня(уже решили), 2- красный, ставится за минусом 7 дней от даты в ячейке K2, которая считается по формуле А2+45, при этом значение J2>0, 3- без цвета, в остальных случаях. Файл с примером приложил для наглядности.
СкачатьКнига1.xlsx

serg3001
Для ячейки K3. Остальные - через "Заполнить вниз"

Код:
Условие 1
Значение Равно "=СЕГОДНЯ()*(J3=0)"
Цвет Зеленый
Условие 2
Значение Между "=СЕГОДНЯ()*(J3>0)" "=(СЕГОДНЯ()+7)*(J3>0)"
Цвет красный.

Alex_Piggy, спасибо, всё правильно сделали.

Добрый день. Интересно, сравнение строк из столбца в 20 000 строк по одной в 120 000 строк при максимальном ускорении можно ускорить более чем 2,5 часа. При условии что в строках со столбцом 20 т. симоволов максимум до 30 симовлов, в столбце с 120 т. может быть и до 300 символов в строке. Цель итераций - приведение к одному виду. Все возможные методы уже включены. Все в массивах, все мигания, расчеты отключены. Может через БД как то? Читал про словари, но по мне так это едино с массивом, просто другая форма хранения. Загрузка с листа идет по а(1 то 120000,1то1), потом перебор.

Доброе время, andrewkard1980
Прошу прощения, из Ваших слов непонятно - Вам нужно найти/проверить соответствие строк целиком или подстрок? И приведение к одному виду - это к какому из какого? Если сравнение строк целиком, то через Dict(vString).Exists будет много быстрее.
Вы можете сделать/выложить тестовый файл или сам макрос?

Цитата:

Читал про словари, но по мне так это едино с массивом, просто другая форма хранения.

Серьёзно?
Поиск в словаре Log(N), прямой просмотр в массиве N/2- что быстрее?
Приблизительно 337453 операций против 1200000000 в вашем случае.
Конечно, если вы используете бинарный поиск в упорядоченном массиве, тогда результаты будут сопоставимы со словарём, и даже может быть быстрее, но судя по времени - используется прямой просмотр с соответствующим результатом
Цитата:

более чем 2,5 часа

Alex_Piggy
Глубо говоря столбец с не правильным названием и рядом с правильными, потом по тексту ищем неправильное и меняем на правильное из соседнего столбца. К-во строк по замене 20 т. К-во сторок которые нудно просмотреть - 120 т.
Попробую
Цитата:

Dict(vString).Exists

AndVGri

Цитата:

Серьёзно?

Видимо не до конца
Т.е. Поиск в словаре Log(N) будет явно быстрее? Попробую переписать код.

Цитата:

прямой просмотр с соответствующим результатом

именно так

Цитата:

Поиск в словаре Log(N) будет явно быстрее?

Быстрее, не сомневайтесь. Для одной из задач нужно было проверить число вхождений 500 000 строк в 1 000 000 строк (и тот и другой набор по 10 символов), находящихся в текстовом файле. Чтение/разбивка на строки/заполнение Dictionary и проверка вхождений заняло около 120 секунд.
Успехов.

andrewkard1980
Да, и не знаю как реализовано, но если в строке, в которой производятся замены есть несколько слов, имеет смысл выделять слова регекспом и уже для них проводить поиск/замену. [more]
Код:
vDATA = "alpha besta gamma delta"
aUnCorr = Array ("alpa","besta","gama","dela")
aCorr = Array ("alpha","beta","gamma","delta")

Set Dict = CreateObject("Scripting.Dictionary")
For i=0 To UBound(aCorr)-1
Dict.Add aUnCorr(i), aCorr(i)
Next

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global=True
.IgnoreCase=False
.Multiline=False
.Pattern="[^ \s]+(?=[ \s]|$)"
End With

For Each Matches in RegEx.Execute(vDATA)
If Dict.Exists(Matches.Value) Then
vDATA = Replace (vDATA,Matches.Value,Dict.Item(Matches.Value),1,1)
End If
Next
MsgBox vDATA

AndVGri
Спасибо, не даром что то меня тревожило
Alex_Piggy
Первый Ваш вариант очень похож на необходимый, спасибо.

Допустимы ли идентификаторы переменных и процедур на русском языке в VBA? Что-то никак не получается вызвать такую функцию из внешнего модуля (c расширением .xlam)

karakurt2
Если версия Office и региональные настройки Windows например английские - то однозначно нет (т.е. не возможно). - сталкивался - так там любая буква в коде (переменная или что ещё) на русском не читалась ядром языка ни в какую.
Если вышеупомянутое всё с русским (и/или) - то незнаю (никогда небыло любых комбинаций системы с русским).
Может и не помогло - но надеюсь круг поиска сузило.

Получилось обойтись без переименования, просто я размещал код в элементе ThisBook, а не в элементе Module1.

помогите пож-та облегчить ввод данных в экселе.
Есть эксель- матрица с данными исследования. пусть называется диссертация.xls
но постоянно по ряду методик тестируются люди и данные заносятся в эксель. Т.о. можно сделать несколько ещё экселей, Например в папке испытуемого Вася Пупкин

тест1.xls
тест2.xls
тест3.xls
4
5

5 методик
и так далее этих Пупкиных много

можно ли из ячеек этих новых экселей автоматический вставлять данные в соотв.ячейки общего файла.
Например, каждый из этих экселей автоматически обрабатывает данные и уже готовые баллы находятся для теста1 в ячейках А1,B2... и так далее, а готовые баллы теста 2 лежат в ячейках c3,d4 . и так с каждым экселем.
Можно ли написать макрос который автоматически вставляет данные из указанных экселей в общую таблицу
Но начиная с пустого места
т.е. 100 первые ячеек заполнено, а он уже будет вставлять в 101-ую ячейку, а потом нажать кнопку, и данные перенесутся из 2 экселя в 102-ую ячейку.

Т.е. по сути эту фразу

" из экселя C:\1\вася пупкин\тест1.хls из ячеек A1, B2... значения поставь в диссертация.xls в ячейки a101,b101... как поставишь , берешь из этой же папки тест2 и из его ячеек c3,d4 значения поставь в ячейки c101,d101 файла диссертация.хлс....
А когда закончишь с Папкой Пупкин переходи в папку Петров и также с 1 по 5 тест также вставь данные только в ячейки от А102" надо переписать на машинный язык.

дальше в шаблон я сам пропишу нужные ячейки.

Можете выручить, пожалуйста.

Fsp050
Если я все правильно понял - открываете общий файл, открываете файл с каким-то пупкиным. В общем файле в необходимой ячейке пишете символ "=", после чего переходите в файл с пупкиным и тыкаете мышкой в нужную ячейку с данными. Возвращаетесь в общий файл, жмете "ентер". Смотрите получившуюся формулу, копируете/подправляете по мере необходимости.

Если же это необходимо сделать имеено средствами VBA, тогда Cell.Formula в помощь.

а где посмотреть эту cell формулу?)

Fsp050
Я же описал, как ее увидеть/сконструировать в первый раз.

Fsp050 13:54 02-05-2014
Цитата:

а где посмотреть эту cell формулу?)

Вот формула

Код: ='C:\1\вася пупкин\[тест1.хls]Sheet1'!A1

Цитата:

но тест1.xls должен быть открыт

Не обязательно. Тест1 должен быть открыт только если формулу именно что конструировать. Для ручного ввода это необязательно. И потом для дальнейшей работы этого не надо; наш основной файл только будет переспрашивать, надо ли обновить данные из внешних источников.

Громостко немного, но респект , друзья.
А если потом этот тест1 будет удалён? его значения с главного экселя не пропадут? Можно их закреплять вне зависимости от того есть ли исходный фаил или нет.

Уважаемые товарищи программисты))
Посодействуйте, плиз, с решением задачки по написанию макроса для иксель.
Макрос должен выполнять следующие действия:
1) проверить значение ячейки Result_List!Bx (x-порядковый номер ячейки) на соответствие значению диапазона Data_List!A1:A500.
2) Если такое совпадение нашлось, то в соседнюю ячейку Result_List!Ax записать значение из соседней ячейки Data_List!Bn (n-соседняя ячейка для той, где нашлось совпадение)

Мне это нужно для отчета на работе.
Буду очень благодарен за помощь.

Доброе время, maa78s
Можно и макросом. Но лень.
Попробуйте функцию ВПР (VLOOKUP). Что-то вроде Result_List!A1 =
=ВПР(Result_List!B1;Data_List!$A$1:$B$500;2)
И заполнить вниз для остальных Result_List!B

Fsp050
При открытии книги ексель спрашивает, обновлять ли данные с внешних источников. Если отказаться, то данные останутся даже после удаления файла.

Но это, имхо, уже перебор. При таких требованиях, кмк, надо переходить на аксес.

Alex_Piggy, спасибо. Подход формулами я уже использовал как воркараунд.
Но проблема в том, что при каждой загрузке данных, например с сервера, происходит очистка всех полей Result_List и заполнение новыми данными, поэтому все формулы с ячеек столбца A также очищаются и приходится заново копипастить формулу.
Поэтому я и хотел сделать это макросами