ランダムに選択して色をつけるマクロを教えて下さい

質問

ランダムに選択して色をつけるマクロについて教えて下さい。

データはエクセルの勤務表のデータです。それぞれフロアが2階～4階まであります。
全体の範囲はC5～AF27までですが、3つに分けるとC5～AF27までが2階です。
C13～AF20までが3階です。C21～AF27までが4階となっています。
表を日にちごとに縦にみたとき、3フロアに分けた範囲ごとにみていくと、
基本的にはそれぞれ1回Eが出てきます。しかしどこか1フロアにはEが必ず
2回でてきます。そのEのどちらかに赤色を付けたいのですが、赤く色がつく職員さんに
関しては毎回まんべんなく偏りのないようにしたいのですが、その方法をカウントして少ない
人から優先的に赤色にする方法でできませんか？

回答No.4

merlionXX · ベストアンサー

ANo2の方の補足をみました。
ANo3も見ていただいてから書いたのですよね？

> 4階は7人ですので、Eが2回出て来ることはありません。

ならば検索範囲からC21:AF27を除外していいのですね？
それからEが3回以上でることもないのですよね？

それならSub test02の上から5行目
For n = 0 To 2　を
For n = 0 To 1 に変えてください。

> AM5～AM27それぞれの階の職員さんが

意味不明です。
AM5～AM27それぞれの階の職員さんとは？
範囲はC5：AF27では？

> 何回Eの色が付きの勤務を行ったのか、（a=a+1)？といった様な式？で回数をcountして

色の出現数を記録したAG列を消さずにおいておけばいいのですか？

それならSub test02の下から三行目
myRng(myRng.Count).ClearContents
を削除してください。

> （最初は全員0ですが、1がついたら次回は少ない人から選択されるような命令）でバランスよく組めませんか？

今でもEが２つあった場合、色が少ない人のセルに色をつけるようにしていますが、そうなっていませんか？
実行結果を添付します。

お礼

何度も意味不明の文章ですみませんでした。
画像ありがとうございました。頭の中で思い描いていた通りです。

countしてバランスよく勤務を割り振るようにできました。
ありがとうございました。

回答No.3

merlionXX

> 一部うまくいかない部分

おはようございます。ANo1-2 merlionXXです。
どのような現象があったのでしょう？
わたしはあなたの質問を以下のように理解してコードを書いています。

データ範囲はC5～AF27
その範囲をC5～AF12、C13～AF20、C21～AF27に３区分する。
各区分ごと、同一列の中に、"E"が最大2個存在する場合がある。
区分ごとに見て"E"が2個存在した場合、どちらかの"E"のセルに着色する。
ただし、区分ごとに着色するセルの行は各区分内で偏らないようにする。

わたしが作業のため付け加えた条件
着色するセルの行は偏らないようにするため、AG5～AG28を作業列に使用し、ここに同一行の着色したセルの数を入れた。（最後に消した）
偏らないようにするため、AG列の数値をみて少ないほうに着色した。
表外C28～AF28には"E"は存在しないものとする。

この前提がちがっていますか？
たとえば、最大2個存在とした各区分の同一列内に"E"が3つ以上あった場合は３つめ以降の"E"は無視されますが、そういうことですか？

お礼

はい。

その解釈で間違いありません。

回答No.2

merlionXX

ANo1　merlionXXです。
変数xの宣言が抜けていました。
あと、作業列の出現数が残ったままになっていましたので最後に消すようにしました。

Sub test02()
　　Dim rng As Range, myRng As Range, c As Range, tmp As Range, x As Range
　　Dim n As Long, i As Long
　　Set rng = Range("C5:AG12").Columns
　　For n = 0 To 2
　　　　Set myRng = rng.Offset(n * 8)
　　　　For i = 1 To myRng.Count
　　　　　　Set tmp = Nothing
　　　　　　Set x = Nothing
　　　　　　For Each c In myRng(i).Cells
　　　　　　　　Select Case c.Value
　　　　　　　　　　Case "E"
　　　　　　　　　　If tmp Is Nothing Then
　　　　　　　　　　　 Set tmp = c
　　　　　　　　　　Else
　　　　　　　　　　　　Set x = IIf(Range("AG" & tmp.Row).Value >= Range("AG" & c.Row).Value, c, tmp)
　　　　　　　　　　　　x.Interior.ColorIndex = 3
　　　　　　　　　　　　Range("AG" & x.Row).Value = Range("AG" & x.Row).Value + 1
　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　End Select
　　　　　　Next c
　　　　Next i
　　　　myRng(myRng.Count).ClearContents
　　Next n
End Sub

お礼

ありがとうがざいます。

ですが、どうしてもランダムに選びたいと思うのですが、一部うまくいかない部分がありました。

補足

返事をいただいてから、深く考えました。
offset(ｎ＊8)ということで各階を8人として考えていく方法でしたが、4階が7人ですので、少しずれてきてしまいました。

4階は7人ですので、Eが2回出て来ることはありません。

2Fの範囲はC5～AF12の（職員8人）
3Fの範囲はC12～AF20の（職員8人）
4Fの範囲はC21～AF27の（職員7人）

そこで、セルの下の方に　2FはC30に3FはC31に4FはC32に一日のEの
勤務を関数のcountifを使って数えました（それを各階AFまで）。結果は2FのC30が2。3FのC31が1。4FのC32が1とでました。このときにマクロで2とついたところに注目して2FのC5～AF12の中にEが2つあるということになりますので、そこからどちらか選んで赤色を付ける命令と、さらに、AM5～AM27それぞれの階の職員さんが何回Eの色が付きの勤務を行ったのか、（a=a+1)？といった様な式？で回数をcountして（最初は全員0ですが、1がついたら次回は少ない人から選択されるような命令）でバランスよく組めませんか？

回答No.1

merlionXX

> 3つに分けるとC5～AF27までが2階です。
C13～AF20までが3階なのですから、2階は「AF12まで」の書きまちがいですよね？
AG列を出現数記録の作業列として使わせてもらいました。
もしAG列が使用されているなら変えなくてはなりません。
コードを簡単にするため、表外の28行目には”E”に一致するセルはないものとしています。
なお、こういう一定の規則に基づいて行うのは「ランダム」とはいいません。

Sub test01()
　　Dim rng As Range, myRng As Range, c As Range, tmp As Range
　　Dim n As Long, i As Long
　　Set rng = Range("C5:AG12").Columns
　　For n = 0 To 2
　　　　Set myRng = rng.Offset(n * 8)
　　　　myRng(myRng.Count).ClearContents
　　　　For i = 1 To myRng.Count
　　　　　　Set tmp = Nothing
　　　　　　For Each c In myRng(i).Cells
　　　　　　　　Select Case c.Value
　　　　　　　　　　Case "E"
　　　　　　　　　　If tmp Is Nothing Then
　　　　　　　　　　　 Set tmp = c
　　　　　　　　　　Else
　　　　　　　　　　　　Set x = IIf(Range("AG" & tmp.Row).Value >= Range("AG" & c.Row).Value, c, tmp)
　　　　　　　　　　　　x.Interior.ColorIndex = 3
　　　　　　　　　　　　Range("AG" & x.Row).Value = Range("AG" & x.Row).Value + 1
　　　　　　　　　　End If
　　　　　　　　End Select
　　　　　　Next c
　　　　Next i
　　Next n
End Sub

お礼

範囲が一部間違っていました。