エクセルのマクロ

質問

A列にアルファベット、B列からE列まで数字が入力されている表があります。
A1から順に（A1→A2→A3→・・・）、A列の文字を調べてそれが"A"であった場合のみ、同じ行のB列からE列の最大値をF列に、最小値をG列にコピーする（A列の文字が"A"以外の場合は何もせず下の行を調べる）、ということを繰り返し、A列が空白になった時その作業を中止する、というマクロを作りたいのです。

もちろん、一つの表だけならA列で並べ替えをして関数を使えば簡単にできますが、表がたくさんあるので自動化したいと思います。
マクロに関してはまったくの初心者なので、お知恵をお借りできればうれしく思います。

回答No.3

zap35 · ベストアンサー

F1に
=IF(A1="A",MAX(E:E),"")
G1に
=IF(A1="A",MIN(E:E),"")
を入力して、下方向にコピーすれば「A列で並べ替えを」する必要もありませんね。関数のよいところはシートにデータを入力すると直ちに値が変わるところです。マクロだとマクロ実行まで表示が変わりませんから。

もしマクロがよければ、アクティブシートに対して処理を行う場合の
マクロは以下になります。このマクロをAlt+F11でVBE画面を開き、左上のVBAProjectのブック名右クリック→挿入→標準モジュールで開く画面に貼り付けてください。ワークシート画面に戻って、ALT＋F8でマクロ一覧を開き、マクロ名を選択して「実行」をクリックします。

Sub Macro1()
Dim idx, lastR As Long
Dim sh As Worksheet
　Set sh = ActiveSheet
'　For Each sh In Worksheets
　　With sh
　　　lastR = .Range("A65536").End(xlUp).Row
　　　Range("F1:G" & lastR).ClearContents
　　　For idx = 1 To lastR
　　　　If .Cells(idx, "A") = "A" Then
　　　　　.Cells(idx, "F").Value = WorksheetFunction.Max(.Columns(5))
　　　　　.Cells(idx, "G").Value = WorksheetFunction.Min(.Columns(5))
　　　　End If
　　　Next idx
　　End With
'　Next sh
End Sub

もし全てのシートに対してこの処理を行ってよいなら先頭に「'」がついている行（２行あります）の「'」を削除してください。そうすれば前シートに対して処理します。

なお＃０１さんの[A1]の書き方は古いバージョンの書き方で、OFFICE2007ではエラーになりました。一応留意してください
（とうとうEvaluateが使えなくなりましたね）

お礼

ご回答ありがとうございます。
できればもう一つ教えていただきたいのですが、
.Cells(idx, "F").Value = WorksheetFunction.Max(.Columns(5))
の「Columns(5)」というのはどういう意味なのでしょうか。

回答No.10

Wendy02

こんにちは
質問者様には、脱線させて申し訳ありません。（削除させられても良いです）

zap35さん、Wendy02です。私は、まだ付いて行けるようですね。

私は、MSが、また、ユーザーの裏切りをしたかと思ったのです。（^^;日本の報道はほとんどアテに出来ないので、今は、海外メディアの広報に頼らなくてはならないのですが、いつも見ていないので、何かあると、結構、不安にさせられるのです。それに、次回のMSのアプリ改編には、私は、付いていける自信がないのです。

「MSは、ユーザーに対して裏切る」
いずれは来ると、肝に銘じておかなくてはならないように思っています。

(海外の記事を読むと、MSがユーザーを守ろうとしているけれども、MSのアプリのモニターのユーザー意見等が、これはやめてしまえとか強行に言うので、その折衷案を取るのだそうです。そうとは思えない部分はあるけれども。ただ、これは、SF小説ではなくて、本当のMSの世界戦略であり、コンピュータによる支配なんですね。そして、おそらくは、強行に反対するのは、当のアメリカのユーザーになると思います。）

#9のコード

>Sub Macro4() '2003→Rangeメソッドは失敗しました '_Global'オブジェクト
>'2007→指定した名前のアイテムが見つかりませんでした
>Range([4]) = 4
>End Sub

[4] という書き方はありませんが、仮に、[A4] としても、この意味は、
Range オブジェクトをダイレクトに指していますので、

Range([A4]) = 4　は、
Range(Range("A4")) = 4 という意味になり、Rangeオブジェクトの中に、単独のRangeオブジェクを引数にすることは出来ません。Cells プロパティなら可能です。（と思います。）

>Sub Macro5() '2003→NG 型が一致しません
>'2007→アプリケーション定義またはオブジェクト定義のエラーです
>Range([A1], Cells("A3")).Value = 5
>End Sub

これは、ミスだと思いますが、 Cells("A3")　の引数で、"A3"　ですと、エラーが起こります。
　Range([A1], Cells(3, 1)).Value

のようにしなくてはなりませんね。私の勘違いでなければ……。

お礼

＞質問者様には、脱線させて申し訳ありません。（削除させられても良いです）

とんでもございません。
私にも興味深い話ですので、削除依頼はしません（^^）。

回答No.9

zap35

＃０３です。まずは補足質問への回答です
＞「Columns(5)」というのはどういう意味なのでしょうか
Columns(5)は左から５列目、つまりＥ列全体を示します。Ａ列がColumns(1)です。
同様にRows(5)だと５行目全体になります

2007でのEvalueteの件、確認もせず中途半端に書いたためご迷惑をおかけしました。２００３、２００７双方で以下のパターンを確認しました。Macro1～3はどちらでも動作しました。4,5はどちらもNGですが、メッセージは異なりました

Sub Macro1() 'OK
Evaluate("A1") = 1
End Sub

Sub Macro2() 'OK
[A1] = 2
End Sub

Sub Macro3() 'OK
Range("A1") = Evaluate("1+2")
End Sub

Sub Macro4() '2003→Rangeメソッドは失敗しました '_Global'オブジェクト
'2007→指定した名前のアイテムが見つかりませんでした
Range([4]) = 4
End Sub

Sub Macro5() '2003→NG 型が一致しません
'2007→アプリケーション定義またはオブジェクト定義のエラーです
Range([A1], Cells("A3")).Value = 5
End Sub

＃０３のときは、他の回答者さまの内容をイミディエイトで確かめたら見慣れないメッセージになったので「あれ、ダメになったの？」と早合点したものです。混乱させて申し訳ありません

お礼

補足質問へのご回答ありがとうございます。

回答No.8

Wendy02

#6 です。
#7 のKenKen_SPさん、ご確認ありがとうございました。
本体がないことには、文書を探しながら読むと手間が掛かってしょうがないです。VBAは、あまり変わってはいないという話は聞きますが、どうも、人から言われると不安になります。

回答No.7

KenKen_SP

#1 既ご回答のスタイルの方がスマートで良いと思いますが、別方法として。

Sub Sample()

　　Dim lLastRowNum As Long
　　Dim sh　　　　　As Worksheet

　　For Each sh In ThisWorkbook.Worksheets
　　　　lLastRowNum = sh.Cells(Rows.Count, "A").End(xlUp).Row
　　　　With Range(sh.Cells(1, "F"), sh.Cells(lLastRowNum, "F"))
　　　　　　.Formula = "=IF(A1=""A"",MAX(B1:E1),"""")"
　　　　　　.Value = .Value
　　　　End With
　　　　With Range(sh.Cells(1, "G"), sh.Cells(lLastRowNum, "G"))
　　　　　　.Formula = "=IF(A1=""A"",MIN(B1:E1),"""")"
　　　　　　.Value = .Value
　　　　End With
　　Next

End Sub

以下は参考情報ですが....

当方の Excel 2007 環境では、[A1] 表記が使えました。
Evaluate はオブジェクトブラウザ、ヘルプで確認できます。
大丈夫みたいですよ^^

お礼

ありがとうございます。
内容がすんなり理解できるようになるまで、もう少し勉強が必要なようです。

回答No.6

Wendy02

こんにちは。

ここの質問主の方の質問とは、直接関係のない話でお許しください。

#3のzap35さんの

>OFFICE2007ではエラーになりました。一応留意してください
>（とうとうEvaluateが使えなくなりましたね）

昨日、Excel 2007 Migration（英文） や Microsoft Support を調べたけれど、そういう内容がありませんし、Excel 2007の事前の英文発表でも、まだ、それ自体がなくなるはずがないと思うのです。また、Ver.4 Macro関数も同様で、これに代わる技術開発が伴わないものだと思います。

Application Object Member

http://msdn2.microsoft.com/en-us/library/bb211564.aspx
Evaluate Method[Excel 2007 Developer Reference]

これには、n-junさんのお話にも関係しています。そのサイトには、[A1]方式の入力方法も出てきます。

おそらく、親オブジェクトの呼び出しの問題ではないか、と思っています。Evaluate 単独では、その親オブジェクトとの不整合が起こることがあるので、エラーが起こるのと、いくつかのローカルな関数は、Evaluate の引数として受けることが出来ません。

今、私は、購入予定がないので、評価版をインストール－アンインストールしてレジストリを汚す気になれません。出来れば、どのような状況で起こるのか、もう一度確かめていただきたいと思って、投稿させていただきました。

回答No.5

n-jun

ANo.1 & 2 & 4です。

ところで、ANo.3さん
>F1に
>=IF(A1="A",MAX(E:E),"")
>G1に
>=IF(A1="A",MIN(E:E),"")
は、
F1に
=IF(A1="A",MAX(B1:E1),"")
G1に
=IF(A1="A",MIN(B1:E1),"")
ではないでしょうか？

回答No.4

n-jun

ANo.1 & 2 です。

ANo.3さん
>なお＃０１さんの[A1]の書き方は古いバージョンの書き方で、
>OFFICE2007ではエラーになりました。一応留意してください
有効な情報です。ありがとうございます。

#　当面2007には手が出ないので本人は大丈夫だとは思いますが、
　回答する際の注意事項として認識致します。

回答No.2

n-jun

全部のシートに対して行なうのなら、こんな感じでしょうか？
（ファイルをコピーしてから実行願います）

Sub Test()
Dim r As Range
Dim i As Integer

For i = 1 To Worksheets.Count
With Worksheets(i)

For Each r In .Range(.[A1], .Cells(Rows.Count, 1).End(xlUp))

If r.Value = "A" Then
With WorksheetFunction
r.Range("F1").Value = .Max(r.Range("B1:E1"))
r.Range("G1").Value = .Min(r.Range("B1:E1"))
End With
End If

Next

End With
Next
End Sub

Ａ列の空白セルが、Ａ列の最終セルである事が条件。
ご参考になれば。

お礼

早速のご回答、ありがとうございました。

回答No.1

n-jun

>A列の文字を調べてそれが"A"であった場合のみ
例えばせるＡ３の値が”Ａ”である場合？
それとも、Ａ３の値に”Ａ”が含まれる場合？

>一つの表だけならA列で並べ替えをして関数を使えば簡単にできますが、表がたくさんあるので
１つのシートに表がたくさんある？
複数のシートに表が１つずつある？

補足

A列の文字はアルファベット1文字です。A列のセルが”A”である場合です。
複数のシートに表が一つずつあります。

よろしくお願いいたします。