VBA A,B,C…列方向へ検索値を移動したい

Question

お世話になっております。
下記、コードについて教えてください。

Sub 水平変位データ()
    Dim kensakuA As Range '検索する値
    Dim kensakuH As Range '検索範囲
    Dim kensakuK As String '検索結果
    Dim Data As Worksheet '水平変位データ
    Dim gyo
    Dim gyos
    Dim retu
    
    gyos = 3
    
    Set Data = Worksheets("水平変位データ")
    Set kensakuH = Range(Data.Range("C3"), Data.Range("AG25"))
    
    For gyo = 15 To 25
    Set kensakuA = Range("DZ14")
        kensakuK = Application.WorksheetFunction.HLookup(kensakuA, kensakuH, gyos, False)
        Range("DZ" & gyo).Value = kensakuK
        gyos = gyos + 2
   
    Next
End Sub

今現在のコードは、DZ14を検索して15行目～25行目まで下方向へ転記していくコードです。
今は、DZ14固定なのですが本来はEA14,EB14と横にずらして検索してすべての表を一括で埋めたいです。

理由：今現在、関数が入っていますがエクセル苦手な担当者が触るとなぜか一部が壊れています。

基本的には、月１回の変更です。（毎月１回作成するので）

そこが、壊れるとグラフデータにも色々支障が出てきてチェックが大変なのでVBAにて作成できないかと思ったのですが実際そんなことが出来るのでしょうか？
ご教示願います。

Answer 1

Cellsを使う回答が既出ですので、私の方からはOffsetを使う方法を紹介します。

Range("DZ14").Offset(0, x).Valueとして、xを増やしていけば、隣へ隣へと進めることができます。

実際にコードを書いてみました。
変数宣言は端折ってますのでご注意ください。

Sub 水平変位データ()
    Set Data = Worksheets("水平変位データ")
    Set 検索範囲 = Data.Range("C3:AG25")

    For 列 = 1 To 3
        転記元の行 = 3
        For 行 = 15 To 25
            検索値 = Data.Range("DZ14").Offset(0, 列).Value
            検索結果 = WorksheetFunction.HLookup(検索値, 検索範囲, 転記元の行, False)
            Data.Range("DZ" & 行).Offset(0, 列).Value = 検索結果
            転記元の行 = 転記元の行 + 2
        Next
    Next
End Sub

全部「水平変位データ」シートで行われると解釈してますので、常にData.Rangeとしました。
そうでないと別のシートがアクティブになっている場合に厄介なトラブルが起きますので。
※Sheetモジュールに直接書いている場合は別ですが。

#追記
シートが分かれているとのことで、修正版を追記します。

Sub 水平変位データ()
    Set Data = Worksheets("水平変位データ")
    Set 転記先シート = Worksheets("１")
    Set 検索範囲 = Data.Range("C3:AG25")

    For 列 = 1 To 3
        転記元の行 = 3
        For 行 = 15 To 25
            検索値 = 転記先シート.Range("DZ14").Offset(0, 列).Value
            検索結果 = WorksheetFunction.HLookup(検索値, 検索範囲, 転記元の行, False)
            転記先シート.Range("DZ" & 行).Offset(0, 列).Value = 検索結果
            転記元の行 = 転記元の行 + 2
        Next
    Next
End Sub

Comments

[cat_junko]

返信が、遅くなり申し訳ございません。
上記、やってみたのですがシート名：「水位変位データ」、シート名：「１」で分かれているからなのかうまくいきませんでした。
また、シート名「１」と言うのもうまくいかない原因なのでしょうか？
（状況にも、よるとは思いますが）

[thom.jp]

シート名「１」は悪くないですが、同じシートへ転記だと思ってました。
とりいそぎ、修正追記しましたのでご確認ください。

[thom.jp]

補足しますと、例えばシートモジュールにコードを書く場合、Range("A1")はそのシートのA1セルという意味になりますが、標準モジュールに書いた場合、Range("A1")はそのときActiveなシートのA1という意味になります。
シートを事前にActivateするコードを入れてもいいですが、できればシート.Range("A1")とフルで記載した方が変なバグを防げるので好みです。

また、手前味噌ですが、シート名が固定なら以下の方法も便利です。
http://thom.hateblo.jp/entry/2015/02/02/061313

[cat_junko]

返信が、大変遅くなり申し訳ございません。
また、新たなコードありがとうございます。
上記の方法で、再度やってみます。

[cat_junko]

返信が、大変遅くなり申し訳ございません。
上記、コードで納得いくまでやっておりました。
大変勉強になりました。
今後ともよろしくお願い致します。

Answer 2

恐らく以下のようなコードになるかと思います。
For gyo を For retu で繰り返します。

Sub hoge()
    Dim kensakuA As Range '検索する値
    Dim kensakuH As Range '検索範囲
    Dim kensakuK As String '検索結果
    Dim Data As Worksheet '水平変位データ
    Dim gyo
    Dim gyos
    Dim retu

    Set Data = Worksheets("水平変位データ")
    Set kensakuH = Range(Data.Range("C3"), Data.Range("AG25"))

    For retu = 130 To 132 ' DZ は 130 列目
        gyos = 3
        For gyo = 15 To 25
            Set kensakuA = Cells(14, retu)
            kensakuK = Application.WorksheetFunction.HLookup(kensakuA, kensakuH, gyos, False)
            Cells(gyo, retu).Value = kensakuK
            gyos = gyos + 2
        Next
    Next
End Sub

Comments

[cat_junko]

返信が、遅くなり申し訳ございません。
上記の方法で、うまくいきました。
行も列も、両方いっぺんに変数にすれば良かったんですね。
水位変位データのシートを参照してシート「１」に呼び込んでいるのですが水位変位データに対応する値がないと止まってしまって動かなかったので「エラーは飛ばす」で対応することできちんと動作するようになりました。

そして、シート名「１」というのが時々引っかかってエラーしていたのですが最終的には問題なく動くようになりました。
有難うございました。
また、宜しくお願いいたします。

Answer 3

Range()のかわりにCells()を使えば、行・列を数字で扱うことが可能です。

Cells(行, 列)

Comments

[cat_junko]

返信遅くなり申し訳ございません。
はい、Cellsで解決できました。
ありがとうございました。
また、宜しくお願い致します。