Изменение ячейки в цикле по отфильтрованному списку.

Дата: Пятница, 13.02.2015, 18:06 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 20% ±

Excel 2007

Добрый форумчанины, помогите пожалуйста. Вопрос жизни и смерти , практически в буквальном смысле.....
Есть вот такой код
[vba]

Код

pos = ActiveSheet.AutoFilter.Range.Offset(1, 0).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, ActiveSheet.AutoFilter.Range.Columns.Count).Columns(10).SpecialCells(xlCellTypeVisible).Cells(1)

      For Each x In ActiveSheet.AutoFilter.Range.Offset(1, 0).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, ActiveSheet.AutoFilter.Range.Columns.Count).Columns(10).SpecialCells(xlCellTypeVisible)
           If pos = x And Count = 0 Then
             Cells(x, 10).Interior.ColorIndex = 6
              Count = 1
           Else
              If pos = x And Count <> 0 Then Cells(x, 10).Interior.ColorIndex = 3


       End If
      Next

[/vba]

Суть задачи для которой этот код написан. Есть отфильтрованный список, в нём есть столбец со значениями, например 2,2,3,4,2,1,5,6,4,4.
Задача в том чтобы оставить только одну уникальную запись по данному опираясь на данный столбец, то есть чтобы получилось 2,3,4,1,5,6.
По моему придуманному алгоритму, все уникальные ячейки с уникальным значением пометить жёлтым, а остальные красным цветом, вот собственно разъяснение кода и суть проблемы"

В первом цикле, идёт перебор значений по одному столбцу в отфильтрованом списке, не считая первой строки, шапки.
Дальше условия на совпадение тоесть если первый элеменn в этом столбце равен самому себе, то эта ячейка закрашивается жёлтым и счётчик увеличивается на 1.
Если оно выполняется то то ячейка должна закраситься жёлтым цветом - вот именно в этом месте,
в этой строчке [vba]

Код

Cells(x, 10).Interior.ColorIndex = 6

[/vba] у меня проблема(((
Как правильно записать в данном случае, а то у меня получается либо он первый элемент который вообще во всём списке вначале идёт, либо вообще всю страницу окрашивает((
[moder]Файл нужен.
И Правила форума почитайте

Код

Cells(x, 10).Interior.ColorIndex = 6

СообщениеДобрый форумчанины, помогите пожалуйста. Вопрос жизни и смерти , практически в буквальном смысле.....
Есть вот такой код
[vba]

Код

Cells(x, 10).Interior.ColorIndex = 6

Автор - Alisa
Дата добавления - 13.02.2015 в 18:06

SLAVICK

Дата: Пятница, 13.02.2015, 18:28 | Сообщение № 2

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата Alisa, 13.02.2015 в 18:06, в сообщении № 1

оставить только одну уникальную запись по данному опираясь на данный столбец

Где оставить?
визуально отобразить а другие строки скрыть, или удалить, или вообще вывести в отдельное место список?
Без файла с примером согласно правил форума - не разобраться

Иногда все проще чем кажется с первого взгляда.

Дата: Пятница, 13.02.2015, 18:28 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 189

Репутация: 78 ±

Замечаний: 0% ±

excel 2010

Я бы на словарях такое делала
[vba]

Код

Sub test()
pos = ActiveSheet.AutoFilter.Range.Offset(1, 0).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, ActiveSheet.AutoFilter.Range.Columns.Count).Columns(10).SpecialCells(xlCellTypeVisible).Cells(1)
    Set dic = CreateObject("Scripting.Dictionary")
     For Each X In ActiveSheet.AutoFilter.Range.Offset(1, 0).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, ActiveSheet.AutoFilter.Range.Columns.Count).Columns(10).SpecialCells(xlCellTypeVisible)
     If dic.exists(X.Value) Then
         X.Interior.ColorIndex = 3
     Else
         X.Interior.ColorIndex = 6
         dic.Item(X.Value) = 0&
     End If
     Next
End Sub

[/vba]
Может я не так поняла ваше задание, но не поняла почему вы X ставите в Cells(x, 10)

К сообщению приложен файл: file1.xls (43.0 Kb)

Лучше день потерять, потом за пять минут долететь!

Дата: Суббота, 14.02.2015, 12:24 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 20% ±

Excel 2007

Огромное спасибо) это то что надо, эх научиться бы понимать что тут так же как вы уметее(
Может сможете вкратце объяснить по строчкам, что как и почему происходит?
И если знаете, то какую нито ссылку или учебник, где это подробно и удобно описано, как делать. А то я вот полторы книжки прочитала, и ничего подобного не нашла(

И ещё один не большой вопрос, вот в вашем коде красным окрашивает только одну ячейку, а как сделать чтобы окрашивался диапазон на строке?

Сообщение отредактировал Serge_007 - Суббота, 14.02.2015, 13:58

Дата: Суббота, 14.02.2015, 13:35 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 189

Репутация: 78 ±

Замечаний: 0% ±

excel 2010

По словарям есть в этой шпаргалке (лучше не придумаешь)
Комментарии по коду:
[vba]

Код

Sub test()
'здесь объявление словаря
Set dic = CreateObject("Scripting.Dictionary")
'здесь выбирается область автофильтра текущего листа ActiveSheet.AutoFilter.Range
'Offset(1, 0) - исключаем заголовок из этой области
'Resize задаем высоту и ширину области, высота "Rows.Count - 1" потому что исключили заголовок, ширина такая же сколько колонок Columns.Count
'.Columns(10) - в этой области выбираем только 10 колонку
'.SpecialCells(xlCellTypeVisible) - только видимые ячейки, то что скрыто автофильтром не берем.
Set rng1 = ActiveSheet.AutoFilter.Range.Offset(1, 0).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, ActiveSheet.AutoFilter.Range.Columns.Count).Columns(10).SpecialCells(xlCellTypeVisible)
'перебор ячеек из области (10 колонка автофильтра, только видимые)
     For Each X In rng1
     'rr строка в которой находится перебираемая ячейка, понадобится для того что бы определить, какую строку окрашивать
     rr = X.Row
     'если значение уже было записано в словарь то
     If dic.exists(X.Value) Then
     'эта ветка условия - если было записано - красим в красный
         ActiveSheet.AutoFilter.Range.Rows(rr).Interior.ColorIndex = 3
     Else
     ' эта ветка если не было ещё записано в словарь - красим в желтый
         ActiveSheet.AutoFilter.Range.Rows(rr).Interior.ColorIndex = 6
     ' и записываем в словарь значение ячейки, что бы когда в следующей ячейке встретится это значение
     ' оно прошло по ветке условий что значение существует=>окраска в красный цвет
         dic.Item(X.Value) = 0&
     End If
     Next
End Sub

[/vba]
Насчет книжки не знаю, я осваивала азы по "Харрис Мэтью Освой самостоятельно программирование для Microsoft Excel 2000 за 21 день"
По ссылкам здесь обширный FAQ http://msoffice.nm.ru/faq/macros.htm
А так, форум лучшее средство познания)

По словарям есть в этой шпаргалке (лучше не придумаешь)
Комментарии по коду:
[vba]

Код

К сообщению приложен файл: file1-1-.xls (48.5 Kb)

Лучше день потерять, потом за пять минут долететь!

СообщениеПо словарям есть в этой шпаргалке (лучше не придумаешь)
Комментарии по коду:
[vba]

Код

Автор - Leanna
Дата добавления - 14.02.2015 в 13:35

Дата: Суббота, 14.02.2015, 13:53 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 20% ±

Excel 2007

Цитата Leanna, 14.02.2015 в 13:35, в сообщении № 5

Насчет книжки не знаю, я осваивала азы по "Харрис Мэтью Освой самостоятельно программирование для Microsoft Excel 2000 за 21 день"
По ссылкам здесь обширный FAQ http://msoffice.nm.ru/faq/macros.htm
А так, форум лучшее средство познания)

Огромное спасибо за ответ, и за название книжки, сейчас скачаю и будет читать)
Можно только один последний вопрос.
Вот в этой строке
[vba]

Код

ActiveSheet.AutoFilter.Range.Rows(rr).Interior.ColorIndex = 3

[/vba]
Мы всю строку окрашиваем в красный цвет, а как правильно записать, чтобы не вся строка окрашивалась, а например со 2-го по 13 столбец?

Дата: Суббота, 14.02.2015, 14:04 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 189

Репутация: 78 ±

Замечаний: 0% ±

excel 2010

Замените ту строку на ту что ниже.
Так даже правильнее, тот пример не будет работать если автофильтр не в первой строке, т.к. строка Range.Rows(rr) указывается относительно области автофильтра, а rr = X.Row указывается строка относительно листа. (Поздно обнаружила я ошибку)
[vba]

Код

Range(Cells(rr, 2), Cells(rr, 13)).Interior.ColorIndex = 3

[/vba]

Лучше день потерять, потом за пять минут долететь!

Дата: Суббота, 14.02.2015, 18:46 | Сообщение № 8

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 20% ±

Excel 2007

Цитата Leanna, 14.02.2015 в 14:04, в сообщении № 7

Большое спасибо всё работает как надо)

Вот только не подскажите почему, если макрос просто выполнить то всё хорошо, а если через отладчик проверять его выполнение пошагово, то он работает не правильно*(
[moder]Не надо цитировать всё сообщение целиком. Вы уже получили за это замечание и опять нарушаете Правила форума[/moder]

Дата: Суббота, 14.02.2015, 18:53 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 189

Репутация: 78 ±

Замечаний: 0% ±

excel 2010

Вы на файле примере смотрите (во вложении)?
На какой строке неправильно через отладчик? (Проверила, у меня всё ок)

К сообщению приложен файл: file1-2-.xls (47.0 Kb)

Лучше день потерять, потом за пять минут долететь!

RAN

Дата: Суббота, 14.02.2015, 19:07 | Сообщение № 10

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Leanna,
[vba]

Код

x.EntireRow.Cells(2).Resize(, 13).Interior.ColorIndex = 3

[/vba]

Alisa / SkyWay унисекс?

Быть или не быть, вот в чем загвоздка!

Дата: Суббота, 14.02.2015, 19:10 | Сообщение № 11

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 20% ±

Excel 2007

Цитата Leanna, 14.02.2015 в 18:53, в сообщении № 9

(Проверила, у меня всё ок)

Вы правы, проблема не в коде, а из-за отладчика, но я всё равно как то не понимаю)
Там снизу у меня есть окно Wathes, насколько я понимаю оно показывает значение переменных.
Вот если туда сделать add watch и добавить dic.Item(X.Value), то при пошаговом выполнение, макрос будет работать не правильно...
Странно, я думала, что просматриваемые значения, которые добавляешь в Watches никак не могут повлиять на код.

Кстати а вот в этой строчке
[vba]

Код

dic.Item(X.Value) = 0&

[/vba]
амперсент это какой то ключевой символ или опечатка?)
И ещё по поводу этой строчки, можно по русски объяснить, как это работает? а то вот читаю теорию, а как в действительности работает, что то не соображу, почему присваиваем ноль, и что это за ноль, и куда он записывается(*

Сообщение отредактировал Alisa - Воскресенье, 15.02.2015, 18:56