エクセルマクロ offsetの使い方

質問

Sub tess()

With Worksheets("Sheet1")
Cells(1,11)=Application.WorksheetFunction.Max(Range("A1:A10"))
End With
End Sub

上記でA1～A１０のうちの最大値１つがA１２に表示されます。
ここで上記コードに手を加えて、例えばA１２にA８の”下のセル１つの値”を表示したいのですが、
Cells(1,11)=offset.(-1, 0).Application.WorksheetFunction.Max(Range("A1:A10"))
と”offset(-1,　0)”　を加えても動きません。WorksheetFunctionを加えても動きません。

offsetを入れる位置と使い方がわかりません。是非よろしくお願いいたします。（OS：WindowsXP、Excel2003）

回答No.3

tom04 · ベストアンサー

何度もお邪魔します。

なかなかご希望に添えないようなので・・・
もう一度質問文を読み返してみました。

もしかして行・列の配置が全く逆になっているのではないのでしょうか？

もしそうであれば

Sub test1()
Dim i, j As Long
For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To 10
On Error Resume Next
If Cells(i, j) = WorksheetFunction.Max(Range(Cells(1, j), Cells(10, j))) Then
Cells(11, j) = Cells(i - 1, j)
End If
Next i
Next j
End Sub

またはOFFSETを使いたいとすれば

Sub test2()
Dim i, j As Long
For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To 10
On Error Resume Next
If Cells(i, j) = WorksheetFunction.Max(Range(Cells(1, j), Cells(10, j))) Then
Cells(11, j) = Cells(i, j).Offset(-1)
End If
Next i
Next j
End Sub

※　前回（No.2）のコードは各行の1列目～10列目（A列～J列）の最大値の一つ左列のセルを11列目（K列）に表示！

※　今回のコードは各列の1行目～10行目の最大値の一つ上の行のセルを11行目に表示！

としています。

尚、前回同様1行目に最大値がある場合はエラーとなり、何も表示されないはずです。

お礼

お忙しい中、ありがとうございます。明日試してみたいと思います。また結果をお返事いたします。

回答No.2

tom04

No.1です！
たびたびお邪魔します。

どうしてもOFFSETを使うとすれば、こんな感じですかね？
（最大値は複数なし。A列に最大値はないとしています。）

Sub test1()
Dim i, j As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 10
On Error Resume Next
If Cells(i, j) = WorksheetFunction.Max(Range(Cells(i, 1), Cells(i, 10))) Then
Cells(i, j).Select
Cells(i, 11) = Selection.Offset(, -1)
End If
Next j
Next i
End Sub

上記と同様の考え方ですが、OFFSETを使わず単に

Sub test2()
Dim i, j As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 10
On Error Resume Next
If Cells(i, j) = WorksheetFunction.Max(Range(Cells(i, 1), Cells(i, 10))) Then
Cells(i, 11) = Cells(i, j - 1)
End If
Next j
Next i
End Sub

でも同様の結果になると思います。

もし、最大値が複数あった場合は最終列の最大値の1列前が表示されると思います。
それからA列に最大値がある場合は空白になります。
参考になれば良いのですが・・・m(__)m

お礼

ありがとうございます。試してみましたがうまくいきませんでした。上記の
If Cells(i, j) = WorksheetFunction.Max(Range(Cells(i, 1), Cells(i, 10))) Then
Cells(i, 11) = Cells(i, j - 1)
の中でCells(i,j)のままですと、Cells（i,1)～Cells(i,10）の最大値の直ぐ左隣が表示できません。悩んでおります　m(__)m

回答No.1

tom04

こんばんは！
直接の回答ではないのですが・・・

＞上記でA1～A１０のうちの最大値１つがA１２に表示されます

に関してですが、最大値が表示されるのはK1セルになります。

それからOFFSETの使い方として、基準のセルから　何行下・何列右　という使い方になりますので
（マイナスの場合は上・左ということになります）

仮にA1セルから2行下・3列右（D3セル）の場合は
Cells(1,1).Offset(2,3)・・・
といった使い方になります。

質問内にあるOFFSETの使い方がはっきり判らないのですが、もしかして下記のようなことがご希望なのですかね？

Cells(1, 11).Offset(1) = Application.WorksheetFunction.Max(Range("A1:A10"))

もしそうであれば、Cells(1, 11)　「K1セル」の1行下のセル（K2セル）にA1～A10の最大値が表示されます。

この程度ですが、参考にならなかったらごめんなさいね。m(__)m

お礼

はじめまして。ご回答ありがとうございます。それから説明不足ですみません。

(Sheet1) （1）（2）（3）（4）（5）（6）（7）（8）（9）（10） （11）
(　A　）　４　 ２ 　 ５ 　２ 　 １　 ９ 　７ 　５　 ２ 　 ５ 　 １
(　B　）
(　C　）
(　D　）

この場合、A1～A10までの最大値は98なので、その隣のA5の"1"をA11に表示したいと思います。また、同じ様に下位の行（B以降について）もループして表示したいのです。
よろしくお願いします。

補足

上記１部訂正いたします。（誤）９８→（正）９

(Sheet1) （1）（2）（3）（4）（5）（6）（7）（8）（9）（10） （11）
(　A　）　４　 ２ 　 ５ 　２ 　 １　 ９ 　７ 　５　 ２ 　 ５ 　 １
(　B　）
(　C　）
(　D　）

この場合、A1～A10までの最大値は”9”なので、その隣のA5の"1"をA11に表示したいと思います。また、同じ様に下位の行（B以降について）もループして表示したいのです。
よろしくお願いします。