[[20090914221328]]　『【VBA】フィルタ後の一行目のセルから値を取得しａx（あん）　ページの最後に飛ぶ

『【VBA】フィルタ後の一行目のセルから値を取得したい』（あん）

 VBAを勉強し始めたのですが分からない箇所があるため質問させてください。
 4列目までが項目のタイトルで、5列目からレコードが記入されている表があります。
 そこからオートフィルタで条件が絞られた後、絞られた条件の一番上の行（見た目上は5行目）の2列目のセルの値を変数Yに取得したいと思っています。

 オートフィルタで絞る条件は毎回違うため
 6行目
 10行目
 11行目…
 という風に表示されたり
 8行目
 9行目
 11行目…
 と表示されたりとまちまちですが常に一番上の行から値を取得したいのです。

 単純に5行目の2列目を取得しようとしたら無理でした。
 変数に値を取得するときの記述方法を教えてください。
 お願いします。

 Y=Cells(5,2).Value
 こういう事ですか？
　　　　（にん）

回答ありがとうございます。

 これだと普通に5行目2列目を取得してしまうのではないでしょうか？
 もしフィルタで5行目が表示されていなかったら取得できずにエラーになったりしませんかね？
 実際の5行目2列目ではなくてフィルタがかかったあとの表示上の5行目2列目を取得したいのです。
 フィルタがかかるので実際は何行目が上から5列目に表示されるかはそのとき次第ということです。
 分かりにくかったり、 Y=Cells(5,2).Valueについてこちらが理解しきれていなかったらすみません。

 標準モジュールに

 '===============================================================
 Option Explicit
 Sub test()
    Dim auf As AutoFilter
    Dim g0 As Long
    Dim rng As Range
    With ActiveSheet
       If .AutoFilterMode Then
          Set auf = .AutoFilter
          With auf.Range
             On Error Resume Next
             Set rng = .Range("a2").Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)
             If Err.Number = 0 Then
                MsgBox "フィルタ領域の１行目２列目のデータは  " & rng.Cells(1, 2).Value
             End If
             On Error GoTo 0
          End With
       End If
    End With
 End Sub

 既にオートフィルタにてフィルタがかかっているシートをアクティブにした状態で
 上記testを実行してみてください。

 フィルターセル範囲の１行目２列目のデータを表示します。

 ichinose

 スレ主ではありませんが....
  ichinoseさんのコードを　オー!! これは便利　と使おうとした所
  時々違う値を返すときが有るようです

        A           B             C
 4    朝食        昼食          夕食
 5    ご飯      ヤキソバ      ハンバーグ
 6    パン        弁当          焼肉
 7    卵          牛丼          カレー
 8   ミルク  　サンドイッチ    ラーメン
 9   味噌汁      おにぎり       すき焼き
 10   ご飯        弁当           焼肉
 11  ミルク      おにぎり        カレー

  こういう表がありオートフィルタで、ミルクを絞り込むと

       A            B              C
 4   朝食　　　　　昼食　　　　　夕食
 8   ミルク　　 サンドイッチ　　ラーメン
 11  ミルク　　　おにぎり　　　 カレー

  となった状態で
 >MsgBox "フィルタ領域の１行目２列目のデータは  " & rng.Cells(1, 2).Value
  の場合　サンドイッチを返してくれます。これをチョっといじって
                        ,,,,,,,,,,,,                         ,,,,,,
 >MsgBox "フィルタ領域の２行目３列目のデータは  " & rng.Cells(2, 3).Value
 として　カレー　を返してほしかったのですが、なぜか すき焼き が返って来ます
 なぜでしょう？　わたしの使い方がおかしいのでしょうか。お教え下さい
 宜しくお願いします　　　　（ゴンタ）

 >MsgBox "フィルタ領域の２行目３列目のデータは  " & rng.Cells(2, 3).Value
 >として　カレー　を返してほしかったのですが、なぜか すき焼き が返って来ます
 >なぜでしょう？
 わかりやすい記述ですね！！

 rng.Cells(1, 2).Value これで正常な値を返す場合は、あくまでも1行目だけなんです。
 提示された例で検証すると、この状態で変数rngが取得しているセル範囲は、
 $A$8:$C$8,$A$11:$C$11
 rng.Cells(1, 2).Valueは、rng.areas(1).cells(1,2)と同じです。
 一番目の領域 $A$8:$C$8に対して、1行目の2列目ですから、Ｂ８（サンドイッチ）の値を返しますが、

 rng.Cells(2, 3).Valueとなると、rng.areas(1).cells(2,3)と同値ですから、
 一番目の領域 $A$8:$C$8に対して、2行目の3列目ですから、Ｃ９(すき焼き）の値を返します。
 （cells(x,y)は、親オブジェクトの範囲を超えたら、超えたセルアドレスを返します。)

 作業シートを設けて、

 Rng.Copy worksheets("作業シート").range("a1")
 等とすれば、worksheets("作業シート").Cells(2,3).Value
 で取得できますけどね！！私なら、たぶんこれ、余り考えなくて良いから・・・。
 作業シートを使わないとすると・・・、

 Option Explicit
 '=====================================================================
 Sub test()
    Dim auf As AutoFilter
    Dim g0 As Long
    Dim rng As Range
    With ActiveSheet
       If .AutoFilterMode Then
          Set auf = .AutoFilter
          With auf.Range
             On Error Resume Next
             Set rng = .Range("a2").Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)
             If Err.Number = 0 Then
                MsgBox "フィルタ領域の2行目3列目のデータは  " & get_find_rng(rng, 2, 3).Value
             End If
             On Error GoTo 0
          End With
       End If
    End With
 End Sub
 '=========================================================================
 Function get_find_rng(rng As Range, rw As Long, col As Long) As Range
    Dim arng As Range
    Dim crng As Range
    Dim g0 As Long
    Set get_find_rng = Nothing
    g0 = 0
    For Each arng In rng.Areas
       If rw <= arng.Columns(1).Rows.Count + g0 Then
          Set get_find_rng = arng.Columns(1).Cells(rw - g0, col)
          Exit For
       End If
       g0 = g0 + arng.Columns(1).Rows.Count
    Next
 End Function

 ichinose

 えっと。。。衝突しちゃったので
 この部分だけ。

 フィルタがかかっている利点があまり無いですが
 行の状態（表示か・非表示か）を見ながら
 数えて行っても、目的のデータは得られそうに思います。

    Sub 一行ずつ()
    Dim i As Long, cnt As Long
        With ActiveSheet
            If .AutoFilterMode Then
                With .AutoFilter.Range
                    For i = 2 To .Count
                        If Not .Rows(i).Hidden Then
                            cnt = cnt + 1
                            If cnt = 2 Then '←ここが行
                                MsgBox .Cells(i, 3).Value
                                Exit For        '~ここが列
                            End If
                        End If
                    Next
                End With
            End If
        End With
    End Sub

 ただ、これなら　フィルタを利用せず
 「A列が××って成っている２番目の行」
 の様に指定して検索させた方が、はやいかも知れません。

 簡単なのは、ichinoseさんが書いて居られる
 ＞作業シートに貼り付けて
 ＞worksheets("作業シート").Cells(2,3).Value
 と思います。

 (HANA)

 ichinoseさんHANAさん有難うございます。
 どちらも希望どおり カレー が返って来ます
 コード中の
 >If .AutoFilterMode Then
 >ActiveSheet.AutoFilter.Range
 など、私にとっては初めて見る記述で、ステップインしながらイミディエイトウインドゥで
 なるほど～ と頷いている所です。（いままでオートフィルタ状態の表をマクロで操作する事が無かったため）
 これから どんどん活用させていただきます。
  スレ主の（あん）さん お邪魔しました
　　　　　　　　　　　　　　　　　　　　　　　　　（ゴンタ）

コメント返信：

[ 一覧(最新更新順) ]

[[20090914221328]] 『【VBA】フィルタ後の一行目のセルから値を取得しａx（あん） ページの最後に飛ぶ

[[20090914221328]]　『【VBA】フィルタ後の一行目のセルから値を取得しａx（あん）　ページの最後に飛ぶ