VBAにて最大値と最大値までのデータの数を教えて

質問

はじめまして。VBA初心者です。
誰か教えてください。

下記のようにA列にデータが並んでおります。
A列はどこまでデータがあるかは、分かりません。
その中で、
　何とか独自でC列の１行目に、A列の最大値を記入する事まではできました。
しかし、A列の最大値が数字が入力されているA列の１行目から何番目にその
最大値がくるのかの計算が分かりません。
　　E列の１行目にその何番目にくるかをしめしたいのですが、
C列の１行目で示すように最大値の値をA列で何番目になるのかの方法を教えてください。
Rangeを使用して、Range("A1:最大値"）を選択して、countするとよいのではと思い、
挑戦していましたが、"最大値"の部分をどのように表記していいかわかりません。
　どなかた分かる方がいらっしゃいましたらよろしくお願いします。

　　　　　　A列　　　　B列　　　　C列　　　D列　　　E列
１行　　　　３　　　　最大値　　　１０　　　個数　　　　４
２行　　　　５
３行　　　　７
４行　　　１０
５行　　　　９
６行　　　　２
…　　　　　…
…　　　　　…

回答No.1

soixante · ベストアンサー

何も難しく考えずに　Max関数とMatch関数を使えばいいのでは？

Sub aaa()

Dim Rng As Range, Wsf As Object
Set Wsf = Application.WorksheetFunction
Set Rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))

Cells(1, 3).Value = Wsf.Max(Rng)
Cells(1, 5).Value = Wsf.Match(Wsf.Max(Rng), Rng, 0)

Set Rng = Nothing
Set Wsf = Nothing
End Sub

お礼

ご返答ありがとうございます。早速やってみましたが、すぐにできました。
初心者なもので2~3時間悩んでおりましたが、一瞬にして解決しました。ありがとうございます。

関数をうまく使用するとよいですね。
いろいろな参考書なども参考にしていますがifの条件分岐を使用して最大値を算出しており、これらを参考にしましたが、教えて頂いた内容が大変シンプルで分かりやすかったです。

記載して頂いた内容の最後の分の
Set Rng = Nothing
Set Wsf = Nothing
はどのような意味があるのですか？？
初心者な質問ですみません。

回答No.2

soixante

#1です。

オブジェクト型変数を使い終わったら開放しておくのが、メモリ上も効率が良いと
何かの本で習いました。

以下ご参照ください。

http://www.moug.net/tech/exvba/0150027.html

お礼

ご丁寧にありがとうございました。大変参考になりました。
特に長いVBAプログラムを作成するさいなどは、適宜Nothingを使用する必要性がありそうですね。
　また、変数と宣言Setステートメントついても、うまく理解できていない部分がありましたので、ありがとうございます。
　ご参照くださいました変数やSetを使用して、上述の質問内容の最大値とその最大値の個数の部分を、
以前は、C列とE列に代入していましたが
違うフォルダの違うExcelシートの特定部分に代入したいのですが…

　　　　　　A列　　　　B列　　　　C列　　　　D列　　　E列
１行　　　　３　　　　最大値　　　１０　　　個数　　　　４
２行　　　　５
３行　　　　７
４行　　　１０
５行　　　　９
６行　　　　２
…　　　　　…
…　　　　　…

　ご示し頂きました内容を抜粋すると、たぶん※印の部分の代入場所を変更するとよいと思うのですが、その前後も変更すべき点があると思いまして…

Sub aaa()

Dim Rng As Range, Wsf As Object
Set Wsf = Application.WorksheetFunction
Set Rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))

※Cells(1, 3).Value = Wsf.Max(Rng)
※Cells(1, 5).Value = Wsf.Match(Wsf.Max(Rng), Rng, 0)

Set Rng = Nothing
Set Wsf = Nothing
End Sub

度々で申し訳ありません。よろしくお願い致します。