Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


2753 / 13646 ツリー ←次へ | 前へ→

【66148】列検索の列の変数対応について カンジ 10/8/5(木) 16:38 質問[未読]
【66149】Re:列検索の列の変数対応について Jaka 10/8/5(木) 17:15 発言[未読]
【66164】Re:列検索の列の変数対応について カンジ 10/8/6(金) 11:08 発言[未読]
【66166】Re:列検索の列の変数対応について カンジ 10/8/6(金) 11:11 発言[未読]
【66150】Re:列検索の列の変数対応について kanabun 10/8/5(木) 17:15 発言[未読]
【66163】Re:列検索の列の変数対応について カンジ 10/8/6(金) 10:56 発言[未読]
【66186】Re:列検索の列の変数対応について kanabun 10/8/6(金) 19:39 発言[未読]
【66188】Re:列検索の列の変数対応について kanabun 10/8/6(金) 20:16 発言[未読]
【66192】Re:列検索の列の変数対応について カンジ 10/8/7(土) 10:47 お礼[未読]
【66151】Re:列検索の列の変数対応について りん 10/8/5(木) 17:16 発言[未読]
【66162】Re:列検索の列の変数対応について カンジ 10/8/6(金) 10:46 発言[未読]
【66168】Re:列検索の列の変数対応について Jaka 10/8/6(金) 12:53 発言[未読]

【66148】列検索の列の変数対応について
質問  カンジ E-MAIL  - 10/8/5(木) 16:38 -

引用なし
パスワード
   列検索を列を変化させながら行おうとしていますが、列の変化がうまく
対応できていません。下記についてご指導ください。
よろしくお願いします。
Sub 列検索でデータを取り込む()
  Dim i As Integer
  Dim j As Integer
  Dim h As Integer
  Dim mysheet1 As String
  Dim mysheet2 As String
  mysheet1 = "sheet1"
  mysheet2 = "sheet2"
  
i = 3
h = 1
With Worksheets(mysheet1)
   Worksheets(mysheet2).Select
   Do While Worksheets(シート名1).Cells(i, 1) <> ""
     
   For j = 5 To 38 Step 3
    
   Worksheets(mysheet2).Select
   Columns(h).Find(Worksheets(シート名1).Cells(i, 1).Value).Select
       '1列置きに検索 ここのhが対応しない
   Sheets(シート名1).Cells(h, j).Value = ActiveCell.Offset(0, 1).Value
     
   h = h + 2
   Next j
     
  i = i + 1
  Loop
 End With
End Sub

【66149】Re:列検索の列の変数対応について
発言  Jaka  - 10/8/5(木) 17:15 -

引用なし
パスワード
   対応できていないというのはどのように対応できていないのでしょうか?
その辺りを詳しく書いておいたほうがいいですね。

一応、
Findで、引数を省力して書いてますが、
しない方がいいではなく、LookIn、LookAt等は省略しない事。

【66150】Re:列検索の列の変数対応について
発言  kanabun  - 10/8/5(木) 17:15 -

引用なし
パスワード
   ▼カンジ さん:

>列検索を列を変化させながら行おうとしていますが、列の変化がうまく
>対応できていません。

何をどうループしているのか、よくわかりません。
コードの途中に出てくる 「シート名1」という変数は
どこで宣言されているのですか?
このままでは コンパイルを通りませんよね?

適当に変数を書き換えてインデントをつけてみると
以下のようになります。

Sub 列検索でデータを取り込む2()
  Dim i As Long
  Dim j As Long
  Dim h As Long
  Dim S As String
  Dim c As Range
  Dim WS1 As Worksheet
  Dim WS2 As Worksheet
  
  Set WS1 = Worksheets("sheet1")
  Set WS2 = Worksheets("sheet2")
  
  h = 1
  With WS1
    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
      S = .Cells(i, 1).Value
      For j = 5 To 38 Step 3
        Set c = WS2.Columns(h).Find(S)
        '1列置きに検索 ここのhが対応しない
        WS1.Cells(h, j).Value = c.Offset(, 1).Value
  
        h = h + 2
      Next j
    Next
  End With
End Sub

で、野郎としていることを日本語でより具体的に説明されると、
質問する方も回答する方も前へすすめると思います。

現状、
[Sheet1]のA列の値 S で
  [Sheet2]の h番目の列から検索 (3行目から順次くりかえし)
  見つかったセルc([Sheet2]h列)の右隣りのセルの値を
  [Sheet1] の(h行?、j列?) に転記する
  (以上同じ検索を For j = 5 To 38 Step 3 回 繰り返す)

という、意味不明なコードになってます。

【66151】Re:列検索の列の変数対応について
発言  りん E-MAIL  - 10/8/5(木) 17:16 -

引用なし
パスワード
   カンジ さん、こんにちわ。

>列検索を列を変化させながら行おうとしていますが、列の変化がうまく
>対応できていません。
具体的にはどう対応できていないのでしょうか?

j のループが終わった段階でhが24になるので、次のjループの時に1に戻すならhの位置はDo Loopの内側。

>Sub 列検索でデータを取り込む()
>  Dim i As Integer
>  Dim j As Integer
>  Dim h As Integer
>  Dim mysheet1 As String
>  Dim mysheet2 As String
>  mysheet1 = "sheet1"
>  mysheet2 = "sheet2"
>  
> i = 3
>     
>   Worksheets(mysheet2).Select
>     
>   Do While Worksheets(シート名1).Cells(i, 1) <> ""

  h = 1 'iが変わるたびに1列目にもどすならhはここで初期化

>    For j = 5 To 38 Step 3

>   Columns(h).Find(Worksheets(シート名1).Cells(i, 1).Value).Select
>       '1列置きに検索 ここのhが対応しない ←どういうふうに?
   MsgBox Columns(h).Address(External:=True), vbInformation, h '確認してみてください 

>   Sheets(シート名1).Cells(h, j).Value = ActiveCell.Offset(0, 1).Value
>     
>   h = h + 2
>     
>   Next j
>     
>  i = i + 1
>     
>  Loop
>     
>End Sub

シート名1はmysheet1ですか?
iとjの関連は考えなくてもいいのですか?

【66162】Re:列検索の列の変数対応について
発言  カンジ E-MAIL  - 10/8/6(金) 10:46 -

引用なし
パスワード
   ▼りん さん:
ご回答ありがとうございます。
説明が不十分で申し訳ありません。
実行したいことは
sheet1のCells(i,1)の値をsheet2の1列目から探し、Offset(0,1)の値をsheet1の
5列目に持ってくる
次に、sheet1のCells(i,1)(これを繰り返す)の値をsheet2の3列目から探し、Offset(0,1)の値をsheet1の8行目に持ってくる
順次、sheet1の検索元は常にCells(i,1)で、sheet2の5列目、7列目、9列目、・・・を
sheet1の11列目、14列目、・・・に持ってくる。(5,8,11、・・・と計12回繰り返す)。
このことを実行したいのですが。

> MsgBox Columns(h).Address(External:=True), vbInformation, h '確認してみてください
 
これを確認してみました
結果:「エクセル名」sheet2$A:$A が$Y:$Yまで繰り返し出てきました。
   検索相手先の列選択はできているようなのですが。

現在はここまでの確認しか進んでいません

シート名1はmysheet1ですか?
シート名1はmysheet1の間違いでした。

【66163】Re:列検索の列の変数対応について
発言  カンジ E-MAIL  - 10/8/6(金) 10:56 -

引用なし
パスワード
   ▼kanabun さん:
ご回答ありがとうございます。
説明が不十分で申し訳ありません。
実行したいことは
sheet1のCells(i,1)の値をsheet2の1列目から探し、Offset(0,1)の値をsheet1の
5列目に持ってくる
次に、sheet1のCells(i,1)(これを繰り返す)の値をsheet2の3列目から探し、Offset(0,1)の
値をsheet1の8行目に持ってくる
順次、sheet1の検索元は常にCells(i,1)で、sheet2の5列目、7列目、9列目、・・・を
sheet1の11列目、14列目、・・・に持ってくる。(5,8,11、・・・と計12回繰り返す)。
ご回答いただいた内容を適用し実行して見ましたが一部でデバックが発生して
しまいました。ご指導いただければと思います。

Sub 列検索でデータを取り込む改良版2()
  Dim i As Long
  Dim j As Long
  Dim h As Long
  Dim S As String
  Dim c As Range
  Dim WS1 As Worksheet
  Dim WS2 As Worksheet
  
  Set WS1 = Worksheets("sheet1")
  Set WS2 = Worksheets("sheet2")
 
  h = 1
  With WS1
     For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
       S = .Cells(i, 1).Value    'WS1の1列i番目の値を取得する
       For j = 5 To 38 Step 3
         Set c = WS2.Columns(h).Find(S)
         WS1.Cells(h, j).Value = c.Offset(, 1).Value
         '↑ここでデバックが発生 実行時エラー91
      
       h = h + 2
     Next j
     Next
  End With
  
  End Sub

上記デバックの解決策を教えていただけませんでしょうか。
よろしくお願いいたします。

【66164】Re:列検索の列の変数対応について
発言  カンジ E-MAIL  - 10/8/6(金) 11:08 -

引用なし
パスワード
   ▼Jaka さん:
ご回答ありがとうございます。
説明不足申し訳ありません。直前のカンジの発言で説明してみました。
皆さんのご指導を受けて現在も作成途中です。一部解決していません。
よろしくお願いいたします。

【66166】Re:列検索の列の変数対応について
発言  カンジ E-MAIL  - 10/8/6(金) 11:11 -

引用なし
パスワード
   ▼Jaka さん:
ご回答ありがとうございます。
説明が不足し申し訳ありません。直前のカンジの発言で説明しました。
皆さんのご指導でチャレンジしていますが、まだ途中です。
よろしくお願いいたします。

【66168】Re:列検索の列の変数対応について
発言  Jaka  - 10/8/6(金) 12:53 -

引用なし
パスワード
   つける場所が無いのでここに。

え〜とですね。
まず、Findの書き方で引数は省略しない事と書きましたよね。

それと、

Find・・・・.Select

と、Findしたものを直接操作すると(上記の場合は、Select)、
見つからなかった場合に実行エラーになります。

だから、Findしたものを変数にセットして、変数にセットされたかどうかの
判定をしてから、見つかったものをを操作します。
Findで検索すれば、使用例がいくつか見つかると思います。

【66186】Re:列検索の列の変数対応について
発言  kanabun  - 10/8/6(金) 19:39 -

引用なし
パスワード
   ▼カンジ さん:

>実行したいことは
>sheet1のCells(i,1)の値をsheet2の1列目から探し、Offset(0,1)の値をsheet1の
>5列目に持ってくる
>次に、sheet1のCells(i,1)(これを繰り返す)の値をsheet2の3列目から探し、Offset(0,1)の
>値をsheet1の8行目に持ってくる
>順次、sheet1の検索元は常にCells(i,1)で、sheet2の5列目、7列目、9列目、・・・を
>sheet1の11列目、14列目、・・・に持ってくる。(5,8,11、・・・と計12回繰り返す)。

具体的にいうと、
> 実行したいことは

Sheet1のCells(i,1)の値を
  Sheet2の              Sheet1の
 【1列目から探し】 Offset(,1)の値を  .Cells(i,5)にもってくる
 【3列目から探し】 Offset(,1)の値を  .Cells(i,8)にもってくる
 【5列目から探し】 Offset(,1)の値を  .Cells(i,11)にもってくる
 【7列目から探し】 Offset(,1)の値を  .Cells(i,14)にもってくる
 【9列目から探し】 Offset(,1)の値を  .Cells(i,17)にもってくる
 【11列目から探し】 Offset(,1)の値を  .Cells(i,20)にもってくる
 【13列目から探し】 Offset(,1)の値を  .Cells(i,23)にもってくる
 【15列目から探し】 Offset(,1)の値を  .Cells(i,26)にもってくる
 【17列目から探し】 Offset(,1)の値を  .Cells(i,29)にもってくる
 【19列目から探し】 Offset(,1)の値を  .Cells(i,32)にもってくる
 【21列目から探し】 Offset(,1)の値を  .Cells(i,35)にもってくる
 【23列目から探し】 Offset(,1)の値を  .Cells(i,38)にもってくる

(以上を 繰り返す)


ということですか?

【66188】Re:列検索の列の変数対応について
発言  kanabun  - 10/8/6(金) 20:16 -

引用なし
パスワード
   ▼カンジ さん:

>> 実行したいことは
>
>Sheet1のCells(i,1)の値を
>  Sheet2の              Sheet1の
> 【1列目から探し】 Offset(,1)の値を  .Cells(i,5)にもってくる
> 【3列目から探し】 Offset(,1)の値を  .Cells(i,8)にもってくる
> 【5列目から探し】 Offset(,1)の値を  .Cells(i,11)にもってくる
> 【7列目から探し】 Offset(,1)の値を  .Cells(i,14)にもってくる
> 【9列目から探し】 Offset(,1)の値を  .Cells(i,17)にもってくる
> 【11列目から探し】 Offset(,1)の値を  .Cells(i,20)にもってくる
> 【13列目から探し】 Offset(,1)の値を  .Cells(i,23)にもってくる
> 【15列目から探し】 Offset(,1)の値を  .Cells(i,26)にもってくる
> 【17列目から探し】 Offset(,1)の値を  .Cells(i,29)にもってくる
> 【19列目から探し】 Offset(,1)の値を  .Cells(i,32)にもってくる
> 【21列目から探し】 Offset(,1)の値を  .Cells(i,35)にもってくる
> 【23列目から探し】 Offset(,1)の値を  .Cells(i,38)にもってくる
>
>(以上を 繰り返す)
で合ってるなら、

次のようにすればできると思います
 (内側のLoop jを Sheet2の【検索列番号】に変えてあります。なので Step 2 )

Sub 列検索でデータを取り込む3()
  Dim i As Long
  Dim j As Long
  Dim h As Long
  Dim S As String
  Dim c As Range, r2 As Range
  Dim WS1 As Worksheet
  Dim WS2 As Worksheet
  
  Set WS1 = Worksheets("sheet1")
  Set WS2 = Worksheets("sheet2")
  Set r2 = WS2.UsedRange
  With WS1
    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
      S = .Cells(i, 1).Value
      h = 5
      For j = 1 To 23 Step 2 '← j は Sheet2の検索列番号
        Set c = r2.Columns(j).Find(S, , xlValues, xlWhole)
        If Not c Is Nothing Then   '見つかったら
                       'Sheet1の h列に値コピー
          .Cells(i, h).Value = c.Offset(, 1).Value
        End If
        h = h + 3 '次のコピー列
      Next j
    Next
  End With
End Sub

また、h は j の関数なので 以下のようにも書けると思います。

Sub 列検索でデータを取り込む4()
  Dim i As Long
  Dim j As Long, h As Long
  Dim S As String
  Dim c As Range, r2 As Range
  Dim WS1 As Worksheet
  Dim WS2 As Worksheet
  
  Set WS1 = Worksheets("sheet1")
  Set WS2 = Worksheets("sheet2")
  Set r2 = WS2.UsedRange
  With WS1
    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
      S = .Cells(i, 1).Value
      For j = 1 To 23 Step 2 '← j は Sheet2の検索列番号 計12列検索
        Set c = r2.Columns(j).Find(S, , xlValues, xlWhole)
        If Not c Is Nothing Then 'Sheet1の h列に値コピー
          h = 3.5 + j * 3 / 2   '◆転記先列番号の計算
          .Cells(i, h).Value = c.Offset(, 1).Value
        End If
      Next j
    Next
  End With
End Sub

【66192】Re:列検索の列の変数対応について
お礼  カンジ E-MAIL  - 10/8/7(土) 10:47 -

引用なし
パスワード
   ▼kanabun さん:
そしてご回答いただきました皆さんありがとうございました。
実行したいことの説明もこのように記述する、ということも学ばせて
いただきました。
そして下記の3・4のどちらもきちんと処理することができました。
ありがとうございました。
ご回答の指示にどう対処すればよいかも迷いながらの超初心者ですが
今後ともよろしくお願いいたします。

>Sub 列検索でデータを取り込む3()
>  Dim i As Long
>  Dim j As Long
>  Dim h As Long
>  Dim S As String
>  Dim c As Range, r2 As Range
>  Dim WS1 As Worksheet
>  Dim WS2 As Worksheet
>  
>  Set WS1 = Worksheets("sheet1")
>  Set WS2 = Worksheets("sheet2")
>  Set r2 = WS2.UsedRange
>  With WS1
>    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
>      S = .Cells(i, 1).Value
>      h = 5
>      For j = 1 To 23 Step 2 '← j は Sheet2の検索列番号
>        Set c = r2.Columns(j).Find(S, , xlValues, xlWhole)
>        If Not c Is Nothing Then   '見つかったら
>                       'Sheet1の h列に値コピー
>          .Cells(i, h).Value = c.Offset(, 1).Value
>        End If
>        h = h + 3 '次のコピー列
>      Next j
>    Next
>  End With
>End Sub
>
>また、h は j の関数なので 以下のようにも書けると思います。
>
>Sub 列検索でデータを取り込む4()
>  Dim i As Long
>  Dim j As Long, h As Long
>  Dim S As String
>  Dim c As Range, r2 As Range
>  Dim WS1 As Worksheet
>  Dim WS2 As Worksheet
>  
>  Set WS1 = Worksheets("sheet1")
>  Set WS2 = Worksheets("sheet2")
>  Set r2 = WS2.UsedRange
>  With WS1
>    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
>      S = .Cells(i, 1).Value
>      For j = 1 To 23 Step 2 '← j は Sheet2の検索列番号 計12列検索
>        Set c = r2.Columns(j).Find(S, , xlValues, xlWhole)
>        If Not c Is Nothing Then 'Sheet1の h列に値コピー
>          h = 3.5 + j * 3 / 2   '◆転記先列番号の計算
>          .Cells(i, h).Value = c.Offset(, 1).Value
>        End If
>      Next j
>    Next
>  End With
>End Sub

2753 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free