【Excel VBA】シートの中から特定の文字列のセル位置を取得するには？

Question

「入力シート」というシートのD列にある
「■交通費明細」というセル位置の2つ下の位置を調べたいです。

例えば「■交通費明細」の場所がD74だったら、D76を取得し、
target という変数に「D76」代入するにはどうしたら良いでしょうか？
(つまりMsgbox target　で「D76」が表示されるという事)
「■交通費明細」はD列にあるが畳用に応じて行位置が変わるためです。

※その後で、D76からW190(右に１７個目)の範囲を「取込用シート」のA2セルに値で貼る予定です。

ちなみに、こういう時のロジックを考える場合、
もし何かの事情で列挿入・削除等により多少前後にずれる可能性を考えて、
現実的な範囲B列～F列等で探すべきでしょうか？
どんな場所に移動していても対応できるようにシート内全セルから探すのが良いのでしょうか？
それともD列から動かないように運用ルールで徹底させるべきでしょうか？

詳しい方、よろしくお願いいたします。

Qchan1962 · Accepted Answer

＞endcell はE列を基準に一番下の行を取得しています。(例えば200)
(「■交通費明細」がF列だったら一つ右のG列が最下部取得の基準)
　つまり、■交通費明細が見つかった右の列と言う事ですね。
例で使っているOffsetを理解しましょう。これは検索すれば、沢山の解説サイトがありますので調べてください。

サンプルを踏まえて、右隣をOffsetで示すと
endcell = .Cells(Rows.Count, r1.Offset(,1).Column).End(xlUp).Row
となります。

＞そして、17個右にある列がU列なので、"U" & "200" から「U200」を作れませんでしょうか？
「■交通費明細」がD列にあればU列になります。

＞Address関数がちんぷんかんぷんで
Addressは、セル番号を視覚的に表示するために入れたもので
なさりたい処理では不要と思います。
.Addressは、セル番号（A1形式アドレス）を返します。
.Address(0, 0)　0はFalseと言う意味で.Address(False, False)と同じです。
(0, 0)を省略すると .Address　絶対参照$が入ります
省略は.Address(1, 1)または、.Address(True, True)と同じです。
数式入力や視覚的確認などに使う事があります。

＞CopyRange.Copy Worksheets(2).Range("A1") を値で貼り付けるにはどうしたら良いでしょうか？

PasteSpecialメソッドを使用すれば出来ます。

CopyRange.Copy
Worksheets(2).Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False '値貼り付けの場合は必要かな
この場合、表示の抑制などもあった方が良いかも、、

値のみの貼り付けで良い場合、コピーメソッドを使用せず
同じ範囲へ値を代入する方法もあり、こちらを使う事が多いですが
Resizeの使い方を覚える必要が出て来ます。

上のコピーと同じ結果を示すサンプル
Worksheets(2).Range("A1").Resize(CopyRange.Rows.Count, CopyRange.Columns.Count).Value = CopyRange.Value

CopyRange.Rows.Countは範囲内（CopyRange）行数を取得しています。
CopyRange.Columns.Countは上記同様、列数です

なので
サンプルは視覚で確認するために色々書いていますが、

Sub sample1()
    Dim target As Range, r1 As Range
    Dim CopyRange As Range
    Dim endcell As Long

With ActiveSheet
        Set target = .UsedRange.Find(What:="■交通費明細", LookAt:=xlWhole)
        If target Is Nothing Then Exit Sub    '見つからなければ、終了
        Set r1 = target.Offset(2)
        endcell = .Cells(Rows.Count, target.Offset(, 1).Column).End(xlUp).Row
        If endcell < r1.Row Then endcell = r1.Row
        Set CopyRange = .Range(r1, target.Offset(endcell - target.Row, 17))
    End With

Worksheets(2).Range("A1").Resize(CopyRange.Rows.Count, CopyRange.Columns.Count).Value = CopyRange.Value

End Sub

r1は基準になり複数回出て来ますのでオブジェクト変数にsetして判り易くしています。

纏めるとVBAで範囲を操作する場合、Offset　Resize　を理解すると良いと思いますのでご自身で調べて検証、実験を繰り返せば理解できると思います

Qchan1962 · Answer

#2です
しばらく時間が空いてしまいましたが
別途、最終行を取得していて、変数名は endcellにしてるのですが、
endcellが200だった時、17個右にある列と組み合わせて、「U200」を取得するにはどうしたら良いでしょうか？
結果的には「D76:U200」をコピーして別ブックに値で貼り付けたいのです。
もし「■交通費明細」が「F74」だったら「F76:W200」になります。

対象になるセルを各RangeObjectでセットしてそのアドレスを表示する
サンプルです。サンプルはアクティブシートを対象にしています。
endcell値の変更などを行って確認してください。
一応、コピー処理もコメントとして書いています。

Sub sample()
    Dim target As Range
    Dim r1 As Range, r2 As Range
    Dim CopyRange As Range
    Dim endcell As Long
    With ActiveSheet
        Set target = .UsedRange.Find(What:="■交通費明細", LookAt:=xlWhole)
        If target Is Nothing Then Exit Sub '見つからなければ、終了

Set r1 = target.Offset(2)
        MsgBox (r1.Address(0, 0))

endcell = .Cells(Rows.Count, r1.Column + 17).End(xlUp).Row
        ' r1.Column + 17 D列の場合U列になります。E列ならV列が対象
        'endcell = .Cells(Rows.Count, "U").End(xlUp).Row　U列確定の場合

If endcell < r1.Row Then endcell = r1.Row
        '対象列に値が無いなどで第一セルより上の行№が取得された場合の対策、取敢えず同じ行

Set r2 = r1.Offset(endcell - r1.Row, 17)
        MsgBox (r2.Address(0, 0))

Set CopyRange = .Range(r1, r2)
        MsgBox (CopyRange.Address(0, 0))
    End With

'下記は取得した範囲をコピーしてシートインデックス2のA1セル以降にコピーします
    'CopyRange.Copy Worksheets(2).Range("A1")
    'Application.CutCopyMode = False

End Sub

mygoonickname · Answer

おはようございます。

他の方も書かれている通り、フォーマットは極力固定が良いかと思います。
現状は、どの様な使われ方をしているのでしょうか？
また、列がズレるのは、どの様な時なのでしょうか？

シート内の全ての中から、■交通費明細を探して、その2行下のアドレス
表示でしたら、下記の様に１行で記載は可能です。

MsgBox Cells.Find(What:="■交通費明細").Offset(2).Address

Cells.Find(What:="■交通費明細").Offset(2).select
若しくは、上記の様にすれば、セルを選択した状態になるので、
Selectiounなどで、データの取得、書き込みが可能です。

但し、■交通費明細が見付からなかった場合はエラーが発生しますし、
複数あった場合などは、１つ目だけになります。

どの様な方法にするかは、方針を決めて、それに対応したマクロを作成
されるのが良いかと思います。

Qchan1962 · Answer

＃２です
ご質問の例では問題ないと思いますが、UsedRangeの為
Set target1 = Range(target.Offset(2), target.Offset(190 - target.Row, 23 - target.Column))
は、エラーの可能性があります。
190　23　の定数から-ではなく、target.Row+定数などとするべきでしたね
+でもエラーの発生する可能性は残りますのでUsedRangeを使用する場合、エラー対策を入れてください。

Qchan1962 · Answer

こんばんは
＞「■交通費明細」というセル位置の2つ下の位置を調べたいです。
この場合、■交通費明細が一意であることが重要です。

シートはアクティブシートとしていますが、サンプルコードです
Sub sample()
Dim target As Range
Dim target1 As Range
Set target = ActiveSheet.UsedRange.Find(What:="■交通費明細", LookAt:=xlWhole)
If Not target Is Nothing Then
MsgBox (target.Offset(2).Address(0, 0))
Set target1 = Range(target.Offset(2), target.Offset(190 - target.Row, 23 - target.Column))
MsgBox (target1.Address(0, 0))
End If
End Sub

＞ちなみに、こういう時のロジックを考える場合、
＞もし何かの事情で列挿入・削除等により多少前後にずれる可能性を考えて、現実的な範囲B列～F列等で探すべきでしょうか？

探すのであれば、使われている範囲UsedRangeを探すべきと思います。
B列～F列（想定範囲）を超えた場合に使えなくなるので。
他の処理も不具合が出ない設計なら良いのではと思います。

＞それともD列から動かないように運用ルールで徹底させるべきでしょうか？

こちらの場合が圧倒的に多いと思います。
ご存知の通り、VBAで処理したものは（通常）元に戻す事が出来ません。
仕様を確定し運用ルールを徹底、さらに操作に対して保護やアラート、
入力制限などをVBAでルーチンを組むのが良いと思います。

私の場合、趣味の範疇ですが頼まれて（無償）EXCELでシミュレーションを作成する事があります。
その場合、知識のない方が運用するので入力制限などはもちろん、
リボンなどもカスタマイズして、そのブックはExcelのデフォルト操作が出来ないようにしています。

何れに致しましても、設計次第と言う事で私にはどちらが良いかを断定できません。

fujillin · Answer

こんばんは

＞もし何かの事情で列挿入・削除等により多少前後にずれる可能性を考えて、
＞現実的な範囲B列～F列等で探すべきでしょうか？
べき論ではなくて、実情を考慮したうえで、質問者様がどのようにしたいかですね。

検索すべき対象がシート内に一意であるなら、どの範囲から検索しても同じ結果を得ることができます。
とは言え、無駄な範囲を含めればそれだけ効率が悪くなることになるでしょう。
一方で、複数ヒットする可能性があったり、ヒットしない可能性もあるのかなどによっても、記述するコードは変わってくると思われます。

セル範囲から値で検索したければ、Range.Findメソッドを使うのが簡単だと思います。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.find

ヒットしたセルから、相対的に行や列が一定値ずれたセル位置を得たければ、Range.Offsetで取得できます。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.offset

【Excel VBA】シートの中から特定の文字列のセル位置を取得するには？

＞endcell はE列を基準に一番下の行を取得しています。

#2です

おはようございます。

＃２です

こんばんは

こんばんは

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング