エクセルVBAで範囲内での位置取得（行＆列）

質問

Sub test()
Set Rng = Range("B2:E7")
Rng.Cells(2, 2).Select
End Sub

これで、範囲Rng内では2行/2列目となるC3セルが選択されます。
では、C3セルが、範囲Rng内で何行/何列目であるかを取得するにはどのように記述すればよいのでしょうか？
Rng.Cells(2, 2).Rowは、当たり前ですが、3になってしまいます。

回答No.4

Wendy02 · ベストアンサー

こんばんは。

端的に言うと、絶対参照を相対参照に戻すことですね。
Address メソッドの　「RelativeTo:=」に、入れてあげれば、相対的な位置を出します。

以下は、文字列の取得の仕方には問題があるけれども、左端上を基点として、相対参照に直しています。以下の文字列の取得は、正規表現なんて使うと、いかにも、それらしくはなるけれど、実際には、遅くなってしまいます。実際は、Ref までで取得は出来ています。

Sub TestSample()
　　Dim rng As Range
　　Dim Target As Range
　　Dim Ref As String
　　Dim i As Long, j As Long, n As Long, rw As Long, col As Long
　　Set rng = Range("B2:E5")
　　Set Target = Range("C4")

　　If Intersect(Target, rng) Is Nothing Then MsgBox "範囲には該当していません。": Exit Sub　　
　　Ref = Target.Address(0, 0, xlR1C1, , rng.Cells(1, 1))

　　'文字列から行数と列数を取得
　　i = InStr(Ref, "[")
　　j = InStr(Ref, "]")
　　n = InStrRev(Ref, "[")
　　rw = Mid$(Ref, i + 1, j - 1 - i) + 1
　　col = Mid$(Ref, n + 1, Len(Ref) - n - 1) + 1

　　MsgBox Target.Address(0, 0) & "は、『" & rng.Address(0, 0) & "』に対して" & vbCrLf _
　　& rw & "行目" & col & "列目"

　　Set Target = Nothing
　　Set rng = Nothing
End Sub

お礼

有難うございます。
Object.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo) という構文だったのですね。 とても勉強になりました。
文字列Refから行数と列数を取得するのはやはり大変ですねえ・・・。

回答No.8

kobouzu_su

エキスパートさん、Wendy02さん、こんばんは。

＞こうやって見ました。
＞Ref = Target.Address(0, 0, xlR1C1, , Rng.Cells(1, 1))
＞Ref = Replace(Replace(Replace(Ref, "R", ""), "[", ""), "]", "")
＞Dim myAdrs
＞myAdrs = Split(Ref, "C")
＞MsgBox Val(myAdrs(0)) + 1 & "　行　" & Val(myAdrs(1)) + 1 & " 列"

おお、さすがエキスパートさん。
これからも今回のようにひとつの解だけではなく少なくとも２つは考えるようにして
常に頭を柔らかく柔らかくふにゃふにゃにしておきませうねぃ。(^^;;;
そうすればきっと遥か遠くに微かではあるけれどWendy02さんの後姿が
目視できるようになる日が来ることでせう。

Wendy02さん、いつも勉強させていただいてます。
これからも実践的なコード、薀蓄のある解説、楽しみにしております。

回答No.7

Wendy02

こんにちは。

kobouzu_su　さん、コメントありがとうございました。

前回書いたように、一応、正規表現では、以下のように一回で取得は出来ます。ただ、「いかにも」っていう感じになって、正規表現の自己主張が強くって、自然な流れではないような気がします。（意味不明かな？）

Sub TestSample2()
　　Dim rng As Range
　　Dim Target As Range
　　Dim Ref As String
　　Dim Matches As Object
　　Set rng = Range("B2:E5")
　　Set Target = Range("C3")

　　If Intersect(Target, rng) Is Nothing Then MsgBox "範囲には該当していません。": Exit Sub
　　Ref = Target.Address(0, 0, xlR1C1, , rng.Cells(1, 1))

　　With CreateObject("VBScript.RegExp")
　　　.Pattern = "R(\[(\d+)\])*C(\[(\d+)\])*"
　　　Set Matches = .Execute(Ref)
　　　With Matches(0)
　　　　MsgBox Target.Address(0, 0) & "は、『" & rng.Address(0, 0) & "』に対して" & vbCrLf _
　　　　& Val(.submatches(1)) + 1 & "行目 " & Val(.submatches(3)) + 1 & "列目"
　　　End With
　　End With

　　Set Target = Nothing
　　Set rng = Nothing
End Sub

お礼

「正規表現」ですか・・・・。
まだエクセルVBAでさえおぼつかないのに、わたくしめにはちと難しすぎまするぅ。

有難うございました。

回答No.6

kobouzu_su

エキスパートMerlionさん、こんにちは。

＞文字列Refから行数と列数を取得するのはやはり大変ですねえ・・・。

頭の体操になって楽しいではありませぬか。。(^o^)。。

'------------------------------------------------
Sub TestSample()
Dim Rng As Range
Dim Target As Range
Dim Ref As String
Set Rng = Range("B2:E7")
Set Target = Range("B4")

Ref = Target.Address(0, 0, xlR1C1, , Rng.Cells(1, 1))

　Dim myAdrs, myRow, myClm

　myAdrs = Split(Ref, "C")
　myRow = Replace(Replace(Replace(myAdrs(0), "R", ""), "[", ""), "]", "")
　myClm = Replace(Replace(myAdrs(1), "[", ""), "]", "")

　MsgBox Val(myRow) + 1 & "　行　" & Val(myClm) + 1 & " 列"

End Sub
'--------------------------------------------------

それから、こういう類のものをテストするときは、境目を重点的にチェックしなければいけません。

１列目（例えば、B2,B3,B4）だとか
１行目（例えば、C2,D2,E2)などを。

因みに当方は、No3のオーソドックスものしか浮かびませんでした。(^^;;;
Wendy02さんは、すごい！

以上です。

お礼

ありがとうございます。

> 頭の体操になって楽しいではありませぬか。。(^o^)。。

確かに。(*´∇｀*)
配列が出てくるとは思いませんでした。
こうやって見ました。

Sub TestSample02()

Dim Rng As Range, Target As Range
Dim Ref As String
Set Rng = Range("B2:E7")
Set Target = Range("B2")

Ref = Target.Address(0, 0, xlR1C1, , Rng.Cells(1, 1))
Ref = Replace(Replace(Replace(Ref, "R", ""), "[", ""), "]", "")

Dim myAdrs
myAdrs = Split(Ref, "C")
MsgBox Val(myAdrs(0)) + 1 & "　行　" & Val(myAdrs(1)) + 1 & " 列"

End Sub

回答No.5

Wendy02

こんにちは。

>文字列Refから行数と列数を取得するのはやはり大変ですねえ・・・。

いえ、これが、なんとも言いようがないのでして……。
まだ、見当の余地は残っています。これで終わりではありません。

今回の書いたのは、最近の私のVBAの処理の仕方なのです。

正規表現なら一回で済むのですが、オートメーション・オブジェクトというのは、呼び出すのにタイムラグがあるので、結果的には、内部コマンドでしてしまったほうがよいと考えたのですが、しかし、どうも見栄えが悪いのです。

良いコードは見掛けも良いものだ、といわれますから、どうも、そこらあたりが正解に到達していないのかもしれませんね。最近、どうも雑になっていけませんね。（^^;

それに、Excel2003あたりですと、たぶん、List を使えるのではないか、と思いましたが、今回は、それはありませんからね。

お礼

有難うございます。
とても勉強になりました。
いつもお世話様です。　(o。_。)oペコッ

回答No.3

ja7awu

こんな感じで如何でしょうか。

Sub test()
Dim Rng As Range
Set Rng = Range("C3")
With Range("B2:E7")
　　If Not Intersect(.Cells, Rng) Is Nothing Then
　　　　MsgBox Rng.Row - .Cells(1).Row + 1 & "行/" & Rng.Column - .Cells(1).Column + 1 & "列目"
　　Else
　　　　MsgBox "範囲外です。"
End If
End With
Set Rng = Nothing
End Sub

お礼

有難うございます。取得できました。
やはりこのような力技になってしまうのですねえ。 φ(=__=; )

回答No.2

aru99

MsgBox (Rng.CurrentRegion.Row & "," & Rng.CurrentRegion.Column)
上記メッセージで2,2が返ってきます。

参考URL：

http://t_shun.at.infoseek.co.jp/My_Page/Excel-VBA/vba_page5.htm

お礼

Rng.CurrentRegion.RowやRng.CurrentRegion.ColumnではCurrentRegionの一番左上のセルの行と列を返すだけです。

回答No.1

zap35

期待されている回答はもっとスマートなものだと思いますが
　Rng.Cells(2, 2).Row - Rng.Cells(1, 1).Row + 1
で取得はできると思います。

お礼

zap35さん、早速有難うございます。
Rng.Cells(2, 2).Row - Rng.Cells(1, 1).Row + 1でもちろん取得はできるのですが、これってRng.Cells(2, 2)と書いた時点で既に範囲内で2行2列目ってわかっている計算ですよね。
例えば検索等で特定のセルのアドレスがわかった場合、それが表中の何行/何列目であるかを取得したかったのです。