Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


13332 / 13646 ツリー ←次へ | 前へ→

【5870】特定の列の最大値Cellに色を塗りたい takumama 03/6/4(水) 23:00 質問
【5871】Re:特定の列の最大値Cellに色を塗りたい ichinose 03/6/5(木) 2:19 回答
【5873】Re:特定の列の最大値Cellに色を塗りたい takumama 03/6/5(木) 10:20 質問
【5884】Re:特定の列の最大値Cellに色を塗りたい ichinose 03/6/6(金) 0:41 回答
【5885】Re:特定の列の最大値Cellに色を塗りたい takumama 03/6/6(金) 10:25 お礼
【5891】Re:特定の列の最大値Cellに色を塗りたい takumama 03/6/6(金) 17:15 質問
【5901】Re:特定の列の最大値Cellに色を塗りたい ichinose 03/6/7(土) 10:51 回答
【5905】Re:特定の列の最大値Cellに色を塗りたい takumama 03/6/7(土) 14:27 お礼
【5913】Re:特定の列の最大値Cellに色を塗りたい takumama 03/6/7(土) 22:41 お礼

【5870】特定の列の最大値Cellに色を塗りたい
質問  takumama  - 03/6/4(水) 23:00 -

引用なし
パスワード
   こんばんわ。
はじめまして_(_^_)_
特定のセル位置を指定した上で、最大値のセルに色をつけたいのですが。。。
最大値の取得はできるのですが、セル位置を取得する方法がわかりません。。
どなたかご教授いただけませんでしょうか。。
よろしくお願いいたします。

Sub 最大値()
  Dim test
  Dim x
  'ActiveCell.SpecialCells(xlCellTypeLastCell).Select
  'endr = ActiveCell.Row
  'Range("c1").Formula = "=max(a1:a" & endr & ")"
  Set myrang = Range("a1,a6")
  test = Application.WorksheetFunction.Max(myrang)
  
  'Range(Cells(1, 1), Cells(1, 1)).Interior.ColorIndex = 6
  MsgBox test

【5871】Re:特定の列の最大値Cellに色を塗りたい
回答  ichinose  - 03/6/5(木) 2:19 -

引用なし
パスワード
   ▼takumama さん:
こんばんは。
>はじめまして_(_^_)_
>特定のセル位置を指定した上で、最大値のセルに色をつけたいのですが。。。
>最大値の取得はできるのですが、セル位置を取得する方法がわかりません。。
>どなたかご教授いただけませんでしょうか。。
>よろしくお願いいたします。
'===============================================
Sub 最大値のセルに色()
  get_max_rng(Range("a1:a6")).Interior.ColorIndex = 6
End Sub
'=====================================================================
Function get_max_rng(rng As Range) As Range
  Dim sv_val(), ans()
  With rng
   sv_val() = .Value
   ans() = Application.Evaluate("=IF(MAX(" & .Address & ")=" & .Address & ",1,"""")")
   .Value = ans()
   Set get_max_rng = .SpecialCells(xlCellTypeConstants)
   .Value = sv_val()
   End With
End Function

最大値の入ったセルの取得ができますが、
色を付けるのが目的なら、条件付書式を使用する方法が一般的のような気がします。

【5873】Re:特定の列の最大値Cellに色を塗りたい
質問  takumama  - 03/6/5(木) 10:20 -

引用なし
パスワード
   ichinose さん
こんにちわ。ありがとうございます。(o^^o)
できました(∩_∩)
ところですみません・・・
なんでFunctionで定義しているのか、また
>Application.Evaluate("=IF(MAX(" & .Address & ")=" & .Address & ",1,"""")")
の記述の意味がよくわからないのですが。。。(~_~;

また、インプットボックスでセルの開始位置と終了位置を任意で指定できるようにしたいのですが。。。

↓ こちらに関しては、条件式はなにに対して使用することが一般的なのですか??
>最大値の入ったセルの取得ができますが、
>色を付けるのが目的なら、条件付書式を使用する方法が一般的のような気がします。

ExcelVBAに関してまったくの初心者のためすみません_(._.)_

【5884】Re:特定の列の最大値Cellに色を塗りたい
回答  ichinose  - 03/6/6(金) 0:41 -

引用なし
パスワード
   こんばんは。
>>Application.Evaluate("=IF(MAX(" & .Address & ")=" & .Address & ",1,"""")")
>の記述の意味がよくわからないのですが。。。(~_~;

Evaluateは、()の式を評価しています。中では、配列数式を使用しています(配列数式に
関しては、Helpを参照してください)。

.Addressには、例題ですと、引数に指定されたセルのアドレス("A1:A6")が入ります。

式の意味は、例のセル範囲を引用すると、
「セルA1:A6の範囲で、最大値と等しければ、1、そうでなければ ""を対応する配列に格納する」ということになり、1と""で構成された配列を返します。


>なんでFunctionで定義しているのか
この「なんで」の疑問のポイントがわかりませんが、

・「なんで」sub xxxxx(・・・・)というプロシジャーではないのか?
 という意味でしょうか?

 引数がひとつ節約できるからです。
 Functionで定義すると、定義したファンクション名をデータ変数として扱えますので、
 記述が簡略化できるのが理由です。

・「なんで」Functionをわざわざ定義して処理を分けたのか?
 という意味でしょうか?
 
 「指定されたセル範囲の中から、最大値を含むセルを取得する」という機能を持つ
 Functionを作成しておくと、他のプロシジャーからでも同じ処理をさせたい事象が発生したときの事を考えて少しでも汎用的にしたかったためです。


>また、インプットボックスでセルの開始位置と終了位置を任意で指定できるようにしたいのですが。。。
前回のコードの修正も含めて作ってみました。
'========================================================
Sub 最大値のセルに色()
  Dim rng As Range
  On Error Resume Next
  Set rng = Application.InputBox("最大値の検査対象セル範囲を指定してチョ", , Selection.Address, , , , , Type:=8)
  If Err.Number = 0 Then
    If rng.Rows.Count = 65536 Then Set rng = rng.Resize(rng.Rows.Count - 1)
'列全体指定されるとエラーになるので-1して置きました
    rng.Interior.ColorIndex = xlNone
    get_max_rng(rng).Interior.ColorIndex = 6
    End If
  On Error GoTo 0
End Sub
'=========================================================
Function get_max_rng(rng As Range) As Range
  Dim sv_val
  Set get_max_rng = Nothing
  With rng
   sv_val = .Value
   .Value = Application.Evaluate("=IF(MAX(" & .Address & ")=" & .Address & ",1,"""")")
   If .Cells.Count > 1 Then
     On Error Resume Next
     Set get_max_rng = .Cells.SpecialCells(xlCellTypeConstants)
     On Error GoTo 0
   Else
     If .Value = 1 Then Set get_max_rng = rng
     End If
   .Value = sv_val
   End With
End Function

Inputboxでセル範囲を指定できます


>↓ こちらに関しては、条件式はなにに対して使用することが一般的なのですか??
>>最大値の入ったセルの取得ができますが、
>>色を付けるのが目的なら、条件付書式を使用する方法が一般的のような気がします。
Excelの一般機能に「条件付書式」というのがあります。

検査セル範囲を選択し、「書式」----「条件付書式」で条件設定のダイアログが表示されます。
ここで、条件指定したり、書式を設定する方法です。詳細は、Helpを参照してください。
これを使って、VBAのコードを書くと、
'=================================================
Sub 条件付書式設定例()
  Call set_condition(range("a1:a6"), 6)
End Sub
'=================================================================
Sub set_condition(rng As Range, clidx As Long)
  With rng
   .FormatConditions.Delete
   .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=MAX(" & rng.Address & ")"
   .FormatConditions(1).Interior.ColorIndex = clidx
   End With
End Sub

というようになります。

それに、マクロ実行後、最大値が変わるような値をセルに入力すると、色も変わってくれます(ユーザーに自由に検査セル範囲を選ばせるなら、最初の方がいいかもしれません)。

確認してみて下さい。

【5885】Re:特定の列の最大値Cellに色を塗りたい
お礼  takumama  - 03/6/6(金) 10:25 -

引用なし
パスワード
   ichinose さん

こんにちわ(^O^)
本当に親切なご対応ありがとうございます。
もろもろの不明な点にまでご回答いただきとても勉強になります(__)

>>Inputboxでセル範囲を指定できます
こちらもありがとうございます。
さっそくためしてみました。。。
なぜか、InputBoxで範囲の指定ができるのですが、色がぬられず・・・
デバックしたら
Set rng = Application.InputBox("最大値の検査対象セル範囲を指定してチョ", , Selection.Address, , , , , Type:=8)
でrngがNothingとのことで値が取得できていないようでした。
自分でも勉強して調べてみます・・・

また、条件付書式設定に関してもありがとうございます。
本当は、この条件式設定を制御できれば。と当初からおもっていました。
やはり、前回のマクロ結果(色彩がついたまま)では使い勝手があまりよくないなぁ。。とかんじていたので。。

<最大値のセルに色>のInputBoxが成功すれば、これを応用して利用することもできますよね??
がんばってみたいとおもいます。
ありがとうございます!

・・・取り急ぎ、前回のマクロで範囲を指定して加工したものを実務で使用させていただきました(__)

【5891】Re:特定の列の最大値Cellに色を塗りたい
質問  takumama  - 03/6/6(金) 17:15 -

引用なし
パスワード
   ichinose さん
こんにちわ(^O^)

>rngがNothingとのことで値が取得できていないようでした。
こちらに関していろいろとやってみました。
問題なく動作しました!ありがとうございます!また、お騒がせいたしました

こんどは・・・
条件式書式に複数条件(同選択cellに対して最大値と最小値の違う色で色彩する)をできるように挑戦中です「(^^; )
単純にSub set_conditionをmax用とmin用とで2つ作り、
    Call set_condition_max(Range("b7:b17"), 15, Range("b7:b17"), 8)
    Call set_condition_min(Range("b7:b17"), 15, Range("b7:b17"), 6)
とでいけるかとおもったのですが,うまくいきません。
どうやら、後で書いたものが上書き処理されている感じです。。。

また、Sub set_conditionをひとつにして変数名を変更してmax用とmin用でわけてみたのですが、やはり結果は後のものがかぶってしまっている感じです。。

.FormatConditions.Delete がいけないのかと思い、二度目の処理ではコメントにして実行してみたのですがやはりうまくいきません(+_+)

何度も甘えてご質問ばかり申し訳ありませんが、よろしければご教授くださいませ。。。

【5901】Re:特定の列の最大値Cellに色を塗りたい
回答  ichinose  - 03/6/7(土) 10:51 -

引用なし
パスワード
   ▼takumama さん:
こんにちは

>条件式書式に複数条件(同選択cellに対して最大値と最小値の違う色で色彩する)をできるように挑戦中です「(^^; )
>単純にSub set_conditionをmax用とmin用とで2つ作り、
>    Call set_condition_max(Range("b7:b17"), 15, Range("b7:b17"), 8)
>    Call set_condition_min(Range("b7:b17"), 15, Range("b7:b17"), 6)
>とでいけるかとおもったのですが,うまくいきません。
>どうやら、後で書いたものが上書き処理されている感じです。。。
>
>また、Sub set_conditionをひとつにして変数名を変更してmax用とmin用でわけてみたのですが、やはり結果は後のものがかぶってしまっている感じです。。
>
>.FormatConditions.Delete がいけないのかと思い、二度目の処理ではコメントにして実行してみたのですがやはりうまくいきません(+_+)
>
>何度も甘えてご質問ばかり申し訳ありませんが、よろしければご教授くださいませ。。。
これね、マクロの記録で条件付書式の操作を行ってみると簡単にコード例を作ってくれるのでそれを修正すると良いと思いますよ。
私は、コードの内容より、インターフェースに悩んでしまいました。
とりあえず、コピーしときますが、

'==============================================================
Sub セルに色()
  Dim rng As Range
  On Error Resume Next
  Cells.FormatConditions.Delete
  Set rng = Application.InputBox("最大値・最小値の検査対象セル範囲を指定してチョ", , Selection.Address, , , , , Type:=8)
  If Err.Number = 0 Then
   
    Call set_condition(rng, Array(8, 6), 2)
    End If
  On Error GoTo 0
End Sub


'======================================================================
Sub set_condition(rng As Range, clidx, Optional c_type As Long = 0)
' 機能   : 指定されたセル範囲で最大値、最小値のセルに指定された色を設定する
' INPUT: rng - 設定するセル範囲
'        clidx- カラーインデックス 配列形式で指定する
'           最大値 1 最小値 5 のとき array(1,5)
'            ひとつのカラーインデックスのみの指定の場合も配列にする事(array(5)のように)
'       c_type 設定のタイプ 0-最大値のみ(規定値)
'                  1-最小値のみ
'                  2-最大値・最小値の両方
'               そのた -設定削除(偶然)
  
  Dim obj_idx As Long
  obj_idx = 1
  With rng
   .FormatConditions.Delete
   If c_type = 0 Or c_type = 2 Then
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
     Formula1:="=MAX(" & .Address & ")"
    .FormatConditions(obj_idx).Interior.ColorIndex = clidx(LBound(clidx))
    obj_idx = obj_idx + 1
    End If
   If c_type = 1 Or c_type = 2 Then
     .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
      Formula1:="=min(" & .Address & ")"
     .FormatConditions(obj_idx).Interior.ColorIndex = clidx(UBound(clidx))
     obj_idx = obj_idx + 1
     End If
   End With
End Sub

確認してみて下さい

【5905】Re:特定の列の最大値Cellに色を塗りたい
お礼  takumama  - 03/6/7(土) 14:27 -

引用なし
パスワード
   ichinose さん:
こんにちわ!

>これね、マクロの記録で条件付書式の操作を行ってみると簡単にコード例を作ってくれるのでそれを修正すると良いと思いますよ。
(-_-; そぉでした。。。
あちらのコードを理解することと、編集して使用することに没頭してしまい、基本的なことを忘れていました。。。すみません・・

>私は、コードの内容より、インターフェースに悩んでしまいました。
そうですね。
とりあえず、いただいたものをInputBoxで指示ができるようにしたいとおもいます。(削除・最大値&最小値・最大のみ・最小のみ)
できたらUpします!
本当にお世話になりました。ありがとうございました_(_^_)_

今後、マクロの記録をわすれずに有効に使用していきます^^;
(・・・いつも相談されるときにはマクロの記録を使いなさい!というくせして、自分になると忘れるなんて(>_<)本当に面目ないです)

【5913】Re:特定の列の最大値Cellに色を塗りたい
お礼  takumama  - 03/6/7(土) 22:41 -

引用なし
パスワード
   こんばんわ。
とりあえず、InputBoxで入力して処理を選択するように修正してみました。
改良の余地はいっぱいあるとおもいますが、ありがとうございました!
===================================================================
Sub main()
Dim rng As Range

  msg = InputBox("処理内容を選択してね!最大値のみ:0/最小値のみ:1/最大値・最小値:2/条件削除:3")
  Select Case msg
  Case 0
    colormax = InputBox("最大値の色を番号で指定してね!")
  Case 1
    colormin = InputBox("最小値の色を番号で指定してね!")
  Case 2
    colormax = InputBox("最大値の色を番号で指定してね!")
    colormin = InputBox("最小値の色を番号で指定してね!")
  End Select
  
  Cells.FormatConditions.Delete
  If msg = 3 Then
    Set rng = Application.InputBox("条件を削除するセル範囲を指定してチョ", , Selection.Address, , , , , Type:=8)
  
  Else
    Set rng = Application.InputBox("最大値・最小値の検査対象セル範囲を指定してチョ", , Selection.Address, , , , , Type:=8)
  End If
  If Err.Number = 0 Then
   Select Case msg
   Case 0
     Call 最大値に色(rng, Array(colormax), 0)
   Case 1
     Call 最小値に色(rng, Array(colormax, colormin), 1)
   Case 2
     Call set_condition(rng, Array(8, 6), 2)
   Case 3
     Call 条件削除
   End Select
   
  End If
  On Error GoTo 0
End Sub
Sub 条件削除()
  
  Selection.FormatConditions.Delete
End Sub
Sub 最小値に色(rng As Range, clidx, Optional c_type As Long = 0)
  
  Dim obj_idx As Long
  obj_idx = 1
  With rng
   .FormatConditions.Delete
   If c_type = 1 Or c_type = 2 Then
     .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
      Formula1:="=min(" & .Address & ")"
     .FormatConditions(obj_idx).Interior.ColorIndex = clidx(UBound(clidx))
     obj_idx = obj_idx + 1
   End If
  End With
End Sub

Sub 最大値に色(rng As Range, clidx, Optional c_type As Long = 0)

  Dim obj_idx As Long
  obj_idx = 1
  With rng
   .FormatConditions.Delete
   If c_type = 0 Or c_type = 2 Then
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
     Formula1:="=MAX(" & .Address & ")"
    .FormatConditions(obj_idx).Interior.ColorIndex = clidx(LBound(clidx))
    obj_idx = obj_idx + 1
   End If
  End With
  End Sub

'======================================================================
Sub set_condition(rng As Range, clidx, Optional c_type As Long = 0)
' 機能   : 指定されたセル範囲で最大値、最小値のセルに指定された色を設定する
' INPUT: rng - 設定するセル範囲
'        clidx- カラーインデックス 配列形式で指定する
'            最大値 1 最小値 5 のとき array(1,5)
'           ひとつのカラーインデックスのみの指定の場合も配列にする事(array(5)のように)
'       c_type 設定のタイプ 0-最大値のみ(規定値)
'                   1-最小値のみ
'                   2-最大値・最小値の両方
'                  そのた-設定削除(偶然)
 
  Dim obj_idx As Long
  obj_idx = 1
  With rng
   .FormatConditions.Delete
   If c_type = 0 Or c_type = 2 Then
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
     Formula1:="=MAX(" & .Address & ")"
    .FormatConditions(obj_idx).Interior.ColorIndex = clidx(LBound(clidx))
    obj_idx = obj_idx + 1
   End If
   If c_type = 1 Or c_type = 2 Then
     .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
      Formula1:="=min(" & .Address & ")"
     .FormatConditions(obj_idx).Interior.ColorIndex = clidx(UBound(clidx))
     obj_idx = obj_idx + 1
   End If
  End With
End Sub

13332 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free