Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 11 Pro : Excel 2016)
飛び飛びセル範囲に名前をつけている場合、[名前].cells(n) でセル指定は不可?
投稿日時: 24/02/24 10:39:48
投稿者: taichi

表題だけでは意味が解り難いので、具体例を上げます。
[A1:E2]に範囲Aと名前を付けた場合なら、範囲A.cells(n) で希望通りのセルを
指定できるのですが、
Range("A1:E2,A5:E6")に範囲Bと名前を付けた場合は、範囲B.cells(11)で[A5]を
指定したいのですが、実際は範囲B外の[A3]が指定された状態になりますよね。
これを範囲B.cells(11)で[A5]が指定できるようにする方法はありますか?
 
よろしくお願い致します。

回答
投稿日時: 24/02/24 11:18:52
投稿者: simple

簡単な指定方法は無いんじゃないですか?
 
For Each ... Nextで走査する方法が考えられますが、
以下のとおり、セル範囲を指定する順序に依存するので、注意が必要です。

Sub test()
    Dim rng1 As Range, rng2 As Range
    Dim k As Long
    
    Set rng1 = Range("A1:E2,A5:E6")  'Case 1
    Set rng2 = Range("A5:E6,A1:E2")  'Case 2
    rng1.Formula = "=ROW()&"",""&COLUMN()"
    
    Debug.Print getData(rng1, 11)   ' =>  5,1
    Debug.Print getData(rng2, 11)   ' =>  1,1
End Sub
Function getData(rng As Range, idx As Long) As Variant
    Dim r As Range
    Dim k As Long
    For Each r In rng
        k = k + 1
        If k = idx Then
            getData = r.Value
            Exit Function
        End If
    Next
    getData = "範囲外"
End Function

回答
投稿日時: 24/02/24 12:55:04
投稿者: Suzu

範囲B.Cells.Count では、20が返ります。
 
でも、それぞれ Item を確認すると
A1、B1、C1、D1、E1
A2、B2、C2、D2、E2
 
ここまでは良いですが この後
A3、B3、C3、D3、E3
A4、B4、C4、D4、E4
となってしまいます。
 
 
『こうなってしまっている』のはまごうことなき事実であり、
ユーザーにはどうする事もできない事柄です。
 
 
当方が探しえたのは、Areas の中身を確認すると
Areas.Count は、2 となり
 
Areas(1).Cells は
A1、B1、C1、D1、E1
A2、B2、C2、D2、E2
 
Areas(2).Cells は
A5、B5、C5、D5、E5
A6、B6、C6、D6、E6
 
です。
 
ですから
 

Sub TEST()
  Dim rng As Range
  Dim i As Long
  Dim j As Long
  Dim k As Long

  Set rng = Range("範囲B")
  For i = 1 To rng.Areas.Count
    For j = 1 To rng.Areas(i).Cells.Count
      k = k + 1
      Debug.Print k & " " & rng.Areas(i).Cells(j).Address
    Next
  Next
End Sub

 
上記コードの k の値が 質問者さんの 希望の引数 n と同一となるでしょうね。
 
要は、Areas(i).Cells.Count の累計数を 別変数として持たせて
nの値を超える直前の累計数 + (n - nの値を超える直前の累計数) を算出し
 
rng.Areas(i).Cells(nの値を超える直前の累計数 + (n - nの値を超える直前の累計数))
 
を求めれば良いのでしょう。
 
 
-----------------------------------------------------------------------------------------
以下雑談・・
上記は苦肉の策であり、マクロ化すると言うことは、繰り返し 同じ処理がありえるのですよね。
当方なら、A1:T1 最悪、A1:E4 で指定すれば済む様にします。
 
ユーザーに見せる表を データベースとして使用し、良かったためしがありません。
 
どうせ、「見せる表」なんて、ちょっとした事でセル位置が変わり
その度に、コード書き換えを迫られます。その度にコード見直しは苦痛でしかないので
複数行の表だとしても、表の見出しの行の 直前の数行に
  データベースのA の列は、1行目の A列、
        B の列は、2行目 の C列 ・・・
  の様な対応表を用意し、表示させる様にして運用している物はあります。

回答
投稿日時: 24/02/24 18:15:00
投稿者: WinArrow

セルアドレス表を作成する方法を掲示します。
 

Sub TEST()
Dim myADD, Tx As Long
Dim AX As Long, Cx As Long
    With Range("範囲B")
        ReDim myADD(1 To .Cells.Count)
        Tx = 1
        For AX = 1 To .Areas.Count
            For Cx = 1 To .Areas(AX).Cells.Count
                ReDim Preserve myADD(1 To Tx)
                myADD(Tx) = .Areas(AX).Cells(Cx).Address
                Tx = Tx + 1
            Next
        Next
    End With
    For Tx = LBound(myADD) To UBound(myADD)
        Debug.Print myADD(Tx)
    Next
End Sub

トピックに返信