Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Проверка на наличие положительных значений в определенных по условию диапазоне ячеек
 
#1
25.12.2012 22:13:06
С вашего позволения я начну заново свой пересказ задачи, ибо перечитав его дома я понял что понять что-то действительно трудно.

есть небольшая табличка со студентами: наименование (имена студентов) и несколько столбцов со значениями.

И у меня уже есть макрос который проверяет значение каждой ячейки в диапазоне C:G по каждой строчке, и если хоть одна окажется со значением <=0 (либо пуст.ячейка = 0), ячейка 1ого столбца закрашивается в красный цвет:

Sub RedColor()
Dim lLastRow As Long

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row

Dim s As Range
For Each s In ActiveSheet.Range("C2:G" & lLastRow)
If IsNumeric(s) Or IsEmpty(s) Then
If s <= 0 Then Cells(s.Row, 1).Interior.Color = vbRed
End If
Next

End Sub


Трудность в том, что его нужно научить мыслить немного шире:

для каждого студента, есть одноименная вкладка со списком значений - это некая часть шапки таблицы из 1ого листа, по которым только и нужно проверять значения в ячейках напротив студента.
Т.е. важно, чтобы именно по этим колонкам (шапки которых прописаны в одноименных со студентами вкладках (студент1, студент2 и тп) проверялось заполненность положительными значениями. На значения остальных колонок (не прописанных во вкладке с именем студента) - не обращать внимание

в приложении файл. на последней вкладки то, что должно получиться по результатам работы макроса
caustic
 
 
 
#2
25.12.2012 22:54:06
Макрос на лист1
Option Explicit

Sub RedColor()
Dim lLastRow As Long
Dim iStudent As String
Dim iVremDen As String
Dim iFound As Range

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row

Dim s As Range

For Each s In ActiveSheet.Range("C2:G" & lLastRow)
If IsNumeric(s) Or IsEmpty(s) Then
   If s <= 0 Then
   iStudent = Cells(s.Row, 1)
   iVremDen = Cells(1, s.Column)
   Set iFound = Sheets(iStudent).Columns(1).Find(iVremDen, , xlFormulas, xlWhole)
   If Not iFound Is Nothing Then
   Cells(s.Row, 1).Interior.Color = vbRed
   End If
   End If
End If
Next
 
 
 
#3
25.12.2012 23:17:23
Kuzmich,
сейчас голова уже не варит вникать в тонкости вашего макроса и разбирать его на составные, но запустив, я увидел то, что ждал увидеть - закраску нужных ячеек :)  :)

спасибо вам огромное. честно говоря, думал макрос будет трехэтажным...
caustic
 
 
 
#4
22.01.2013 17:42:39
Доброго времени суток товарищи!
подскажите пожалуйста, как видоизменить тело макроса (постом выше) так, чтобы закрашивались (н-р в зеленый) те ячейки при отрицательных или пустых значениях, шапка колонок которых прописаны во вкладках со студентами (студент1, студент2 и тп). Соответственно значения ячеек, которые не прописаны списком во вкладке с именами студентов - игнорировалось

пример "как должно быть" прилагаю


с уважением!
caustic
 
 
 
#5
22.01.2013 18:07:49
Добавьте одну строчку в код

If Not iFound Is Nothing Then
Cells(s.Row, 1).Interior.Color = vbRed
s.Interior.ColorIndex = 4
End If
 
 
 
#6
25.01.2013 15:37:36
ребят, и в первую очередь Kuzmich спасибо за помощь, а еще такой вопрос

как скорректировать макрос так, чтобы он закрашивал имена студентов не при первом найденном s <= 0, а при следующем.
т.е. наткнулся на s <= 0...пропустил.. а вот при повторном уже закрашивал имя студента.
другими словами, допускается наличие одной ячейки со значением <= 0, если больше одной  - закрашиваем имя студента.

с уважением
caustic
 
 
 
#7
26.01.2013 13:21:26
caustic
Посмотрите пример во вложении
 
 
 
#8
28.01.2013 11:28:12
Kuzmich,
простите меня, я видимо немного некорректно объяснил.

что делает ваш макрос:
он ищет первое значение <= 0, пропускает его, при втором - закрашивает красным.
Пример: если вы для 1студента (2строка в файле) выставите положительное значение в колонке "Завтрак", очистите ячейку в колонке "Ланч", то выполнив макрос, он все равно подсветит этого студента красным, что не верно, т.к. положительные значения для колонок "Завтрак" и "Обед" у него выставлены, а по условию - допускается одна ячека с пустым либо отрицательным значением


как нужно: чтобы макрос работал (проверял) только те ячейки, шапки колонок которых прописаны в одноименных (по имени студента) вкладках. И соответственно по ним допускал одну ячейку с отрицательным или пустым значением, а при нахождении второй такой - уже закрашивал в красный имя студента
caustic
 
 
 
#9
28.01.2013 13:14:07
На листе1 1студент встречается три раза,
ваше условие должно выполнятся для этих трех строк?
Я правильно понимаю?
Посмотрите вариант
 
 
 
#10
28.01.2013 13:43:23
Да, для каждого студента (а не только для этих трех строк) должна идти проверка по своему списку из одноименных вкладок, собственно говоря, что и делает сейчас ваш вновь высланный макрос. Я его еще хорошенько обкатаю и дам знать если что пойдет не по условию :)

спасибо,Kuzmich вам!
caustic
 
 
 
#11
29.01.2013 11:05:36
все-таки что-то я не доглядел.
Kuzmich, на вашем примере, он исправно работает так как надо, т.е. игнорирует первое попавшееся значение ячеек <= 0 в колонках, шапки которых прописаны для каждого студента в одноименной вкладке.
Перенес макрос на свой пример (см приложение): теперь он не игнорирует первое попавшееся значение ячейки <= 0 (с уловиями что я описал выше, т.е .про шапки колонок и одноименные вкладки студентов)

и еще из моего примера, 13 и 14 казалось бы идентичные строчки по 3stud, но первую (13) макрос красит красным, а следующую - нет.

где я ошибся?

ps. сама шапка таблицы и список названий колонок шапки во вкладках с именами студентов могут быть разбросаны в любой последовательности.
caustic
 
 
 
#12
29.01.2013 11:33:26
caustic
Надо исправить строчку так:
For Each s In ActiveSheet.Range(Cells(iFoundStudent.Row, 4), Cells(iFoundStudent.Row, 11))

Вы решили красить порядковый номер, а не ячейку со студентом?
 
 
 
#13
29.01.2013 11:33:44
когда адаптировал файл примера для сайта, не исправил ошибку в этой строчке
For Each s In ActiveSheet.Range(Cells(iFoundStudent.Row, 4), Cells(iFoundStudent.Row, 11))
caustic
 
 
 
#14
29.01.2013 11:37:38
вы меня опередили )
но еслибы эта ошибка отвечала на вопрос почему не игнорируются в моем примере первые значения ячейки <= 0


и еще, если это упростит макрос, то оринтироваться можно на то, что студентов больше 3х не будет. имена фиксированы.
Изменено: caustic - 29.01.2013 12:10:07
caustic
 
 
 
#15
29.01.2013 12:27:33
Игнорируются первые значения ячейки <= 0 или пусто
 
 
 
#16
29.01.2013 14:41:12
Kuzmich,
макрос сейчас работает вот по какому принципу:
он считает, что каждое имя студента (1 студент, 2 студент) - оно уникально и встретив его однажды в основной таблице - выполняет то что вы говорите:

Цитата
Kuzmich пишет:
Игнорируются первые значения
ячейки <= 0 или пусто

Но далее встретив это имя еще раз, уже идет проверка на заполненность всех ячеек (по шапкам из вкладки с одноименным именем), без какого-либо игнорирования..

в приложении тот же файл от вас, с несколькими новыми строчками. для примера взял значение "2 студент"
и при выполнении макроса они все закрашиваются красным, хотя 10, 11 и 15 строчки не должны закрашиваться
caustic
 
 
 
#17
29.01.2013 14:51:35
В последнем примере вы используете неисправленную версию макроса.
Исправленная в посте от 28 января
 
 
 
#18
29.01.2013 14:59:27
прикрепляю версию от вас за 28 число.

та же самая проблема.
caustic
 
 
 
#19
29.01.2013 15:14:38
Для первого студента пропущена ячейка G2, остальные окрашены.
Для второго студента пропущена ячейка G7, остальные окрашены.
Для третьего студента пропущена ячейка C5, остальные окрашены.
Где ошибка?
 
 
 
#20
29.01.2013 15:21:06
ошибка от 9ой строчки и ниже - все варианты закрашены красным цветом,
хотя 9, 10 и 14 строчки не должны быть закрашены, т.к. по условию, для 2ого студента проверяются 2 колонки, а по этим строчкам, одна из двух ячеек содержит положительное число, поэтому красится не должна.
caustic
 
 
 
#21
29.01.2013 15:27:59
Так у вас условие должно выполнятся для каждой строки?
Я то брал весь массив значений для i-студента
 
 
 
#22
29.01.2013 15:33:33
Kuzmich, да.

ваш макрос работает сейчас только по уникальным значениям.
т.е. встретив к примеру значение "1 студент", он правильно проверит его на все условия, описанные выше, проигнорит первую попавшуюся пустую ячейку или ячейку с отрицательным значением (если есть). А при последующей встречи со значением "1 студент" он уже просто проверяет на заполненность положительными числами все обозначенные ячейки колонок, но игнорить первую попавшуюся пустую ячейку или отриц число - уже не станет. и поэтому закрасит ее
caustic
 
 
 
#23
29.01.2013 15:36:46
Вы выложите этот пример с листом "как должно быть"
 
 
 
#24
29.01.2013 15:40:52
off
Простите, но ЭТОТ ДИАлог, имхо, просится в личку и... за наличку... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#25
29.01.2013 15:53:03
пример в приложении.

P.S. кроме значений "1 студент", "2 студент", "3 студент" - других "студентов" (н-р "4 студент" и тд) в таблице не будет.
caustic
 
 
 
#26
29.01.2013 16:07:40
Option Explicit

Sub RedColor()
Dim lLastRow As Long
Dim i As Long
Dim n As Integer
Dim kStudent As String
Dim iVremDen As String
Dim iFound As Range
Dim MyRange As Range
Dim s As Range

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lLastRow
kStudent = Cells(i, 1)
n = 0
For Each s In ActiveSheet.Range(Cells(i, 3), Cells(i, 7))
If IsNumeric(s) Or IsEmpty(s) Then
If s <= 0 Then
iVremDen = Cells(1, s.Column)
Set iFound = Sheets(kStudent).Columns(1).Find(iVremDen, , xlFormulas, xlWhole)
If Not iFound Is Nothing Then
n = n + 1
If n <> 1 Then 'пропускаем первое отрицательное значение или пустое
Cells(s.Row, 1).Interior.Color = vbRed
s.Interior.ColorIndex = 4
End If
End If
End If
End If
Next
Next
End Sub
 
 
 
#27
29.01.2013 16:29:07
Kuzmich,
вот вроде работает! спасибо большое, буду тестить на своих примерах) :)
caustic
 
 
 
#28
30.01.2013 17:55:21
Добрый день.
на последнем шаге прошу помощи по этому же макросу.

в кратце суть расскажу: по каждому студенту есть одноименная вкладка из которой проверяются значения в колонках, имена которых прописаны на этих листах.

окей. сделано

после этого хочу прогнать на эту проверку колонку C, а именно:
во вкладке "spisok" выписаны так же списком некоторые значения из колонки C, по которым нужно проверить на положительные числа колонки, имена которых прописаны на вкладке "dni"

с горем пополам, но сделано, благодаря форуму.

Теперь хочу объединить эти 2 макроса в один.. и тут какие-то фокусы.
если закоментить первую часть макроса (перед комментарием 'теперь исключения' в модуле)
макрос выполняет вторую часть на отлично.
если прогнать полностью макрос, он будто игнорит вторую его часть, и работает только с вкладками 1stud, 2stud, 3stud - т.е. проверяет на положительные числа колонки, имена которые выписаны в них.
подскажите где его нужно скорректировать, чтобы они работали должным образом


пример прилагаю, внутри макрос


с уважением
caustic
 
 
 
#29
30.01.2013 18:49:54
Во второй части макроса, что вы определяете строкой

iRow = Cells(lLastRow, 3).End(xlToLeft).Row
 
 
 
#30
30.01.2013 19:38:41
последнюю заполненную строчку в столбце C
далее эта переменная задается как последний шаг для цикла For i = 12 To iRow
т.е. работаем с 12 строчки до последней заполненной
Изменено: caustic - 30.01.2013 19:41:28
caustic
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх