Подскажите плз. где подглядеть макрос по поиску из закраске дубликатов. Вроде постоянно обсуждается, но в основном с использованием УФ и формул, а надо что-то простое и именно макросом.
Да нет! Массивом оно конечно лучше. Правда придется создавать "базу дубликатов" большую. Мне нужно искать дубликаты месяцев за 20 лет, т.е. 240 ячеек. И деть ее куда-то надо, ведь этот макрос работает если "база" и диапазон проверки на одном листе. А на лист, что-либо лишнее крайне не хочется заносить, во как! Да и "цветомузыка" эта мне не нужна, но это мелочи, отключил.
Нее! Мне один цвет нужен, я его и сделал. Вообще задача несколько в другом, закраска, это так, "рюшечки"! Нужно проверять, на соответствии календарному, ко-во дней в месяцах из которых состоит столбец таблицы. При условии, что любой месяц может быть "разбит" на периоды с произвольным ко-вом дней. Т.е. считать общее ко-во дней в периодах и проверять соответствует ли оно календарному. Вот я и думаю, как это реализовать с минимумом жертв. См. Пример
Sub bad_periods()
Dim i&, rBase As Range, a, dct As Object
'Set rBase = Application.InputBox("Диапазон из 2 столбцов:", Default:=[a1].CurrentRegion.Address, Type:=8)
Set rBase = Range("A1:B19")
a = rBase.Value
Set dct = CreateObject("scripting.dictionary")
For i = 1 To UBound(a, 1)
dct.Item(a(i, 1)) = dct.Item(a(i, 1)) + a(i, 2)
Next
For i = 1 To UBound(a, 1)
If Application.WorksheetFunction.EoMonth(a(i, 1), 0) + 1 - a(i, 1) < dct(a(i, 1)) Then
MsgBox "MsgBox в строке " & i
rBase(i, 1).Resize(1, 2).Interior.Color = RGB(255, 0, 0)
Else
rBase(i, 1).Resize(1, 2).Interior.Color = RGB(0, 255, 0)
End If
Next
End Sub
формула для условного форматирования: =СУММЕСЛИ($A$1:$A$19;$A1;$B$1:$B$19)>КОНМЕСЯЦА($A1;0)+1-$A1 для подсветки всех неправильно забитых периодов ее же можно для условия проверки данных - для предотвращения ввода (только не ">", а "<="
ну вот так работает, и предыдущий у меня работал в 2010.
Код
Sub bad_periods()
Dim i&, rBase As Range, a, dct As Object
'Set rBase = Application.InputBox("Диапазон из 2 столбцов:", Default:=[a1].CurrentRegion.Address, Type:=8)
Set rBase = Range("A1:B19")
a = rBase.Value
Set dct = CreateObject("scripting.dictionary")
For i = 1 To UBound(a, 1)
dct.Item(a(i, 1)) = dct.Item(a(i, 1)) + a(i, 2)
Next
Dim s$, t As Boolean, mdays As Double
s = "Ошибки в строках: "
For i = 1 To UBound(a, 1)
' On Error Resume Next
' mdays = Application.WorksheetFunction.EoMonth(a(i, 1), 0) + 1 - a(i, 1)
' If Err <> 0 Then
mdays = DateSerial(Year(a(i, 1)), Month(a(i, 1)) + 1, 1) - a(i, 1)
' End If
If mdays < dct(a(i, 1)) Then
s = s & i & ", "
t = True
rBase(i, 1).Resize(1, 2).Interior.Color = RGB(255, 0, 0)
Else
rBase(i, 1).Resize(1, 2).Interior.Color = xlNone
End If
Next
If t Then MsgBox (Left(s, Len(s) - 2)) Else MsgBox "Всё в порядке"
End Sub
а что должно быть если меньше? заливки быть не должно, условное форматирование снимается. Ну или если нужна другая закраска, то тогда поставьте еще одну (с другим знаком) формулу на другой цвет
Sub bad_periods()
Dim i&, rBase As Range, a, dct As Object
Set rBase = Range("A1:B19")
a = rBase.Value
Set dct = CreateObject("scripting.dictionary")
For i = 1 To UBound(a, 1)
dct.Item(a(i, 1)) = dct.Item(a(i, 1)) + a(i, 2)Next
Dim s$, t As Boolean, mdays As Double
s = "Ошибки в строках: "
For i = 1 To UBound(a, 1)
mdays = DateSerial(Year(a(i, 1)), Month(a(i, 1)) + 1, 1) - a(i, 1)
If mdays < dct(a(i, 1)) Or mdays > dct(a(i, 1)) Then
s = s & i & ", "
t = True
rBase(i, 1).Resize(1, 2).Interior.ColorIndex = 40
Else
rBase(i, 1).Resize(1, 2).Interior.ColorIndex = -4142
End If
If a(i, 1) = 0 Then
rBase(i, 1).Resize(1, 2).Interior.ColorIndex = -4142
End If
Next
Код
If t Then MsgBox (Left(s, Len(s) - 2)) Else MsgBox "Все правильно!"
Код
End Sub
В итого, как-то так получается. Еще бы придумать, чтобы вместо номеров строк, содержащих ошибки, выводился список месяцев?! А так, почти ляпота!