Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
findを使って、行ごとに連続処理できる転記をしたい
投稿日時: 19/12/12 19:03:06
投稿者: meron

findメソッドを使って商品名を検索
商品名を検索後、三つの値(価格1-3)を転記したい。
別シートに転記、商品名はすでに記載されています。
価格1-3の順番は変わっていませんが、商品名の場所が変わります。
これからも商品名が増える予定です
乱雑に並んでいたものを、一時整える目的です。
 
※コードのどこが間違えているかご教示お願いいたします。
  VBA初心者です、お手柔らかにおねがいいたします。
シート1                   シート2
 
     B C D E    B  C D E
 3 商品名 価格1   価格2   価格3  4 商品名 価格1 価格2 価格3
 4 c     100    200    300  5    a
 5 a    300 400 600 6 b
 6 b    50     800    54 7 c
   ・
   ・
   ・
   ・
   ・
   n
 
Sub macro()
    Dim myval(3)
    Dim i
    Dim y
    Dim s
     
    Set ws1 = Worksheets("sheet1")
    Set ws2 = Worksheets("sheet2")
     Set t = ws1.Columns("b").Find(what:=x)
 
    k = 4
    n = Cells(1048576, 2).End(xlUp).Row
     
    For i = k To n
     x = Cells(i, 2).Value
     
    For y = 1 To 3
     myval(y) = ws1.Cells(t.Row, y + 2).Value
      
    For s = 1 To 3
     myval(s) = ws2.Cells(i, s + 2).Value
     
    Next s
     Next y
    Next i
    
End Sub

回答
投稿日時: 19/12/12 19:35:31
投稿者: simple

こんばんは。
 
まず、インデントをしっかりつけたほうが処理の構造が、より分かり易くなるはずです。
(内容は一切変えていません。)

Sub macro()
    Dim myval(3)
    Dim i
    Dim y
    Dim s

    Set ws1 = Worksheets("sheet1")
    Set ws2 = Worksheets("sheet2")
    Set t = ws1.Columns("b").find(what:=X)
    k = 4
    n = Cells(1048576, 2).End(xlUp).Row
    For i = k To n
        X = Cells(i, 2).Value
        For y = 1 To 3
            myval(y) = ws1.Cells(t.Row, y + 2).Value
            For s = 1 To 3
                myval(s) = ws2.Cells(i, s + 2).Value
            Next s
        Next y
    Next i
End Sub

(1)変数はすべて宣言して下さい。そのとき、整数ならLongといった型を指定してください。
  冒頭に、Option Explicitとすると、未宣言の変数には警告が出ます。
   これを利用するとよいでしょう。
(2)Rangeオブジェクトにはワークシートの指定を漏らさないようにしましょう。
(3)findは一回だけでなく、繰り返しの中で利用するようにしてください。
(4)配列はため込むだけですね。
   一行毎に配列に書き込むのは、余り効率的とはおもえません。
   まずは、配列を使わないもので、トライして、その後、配列使用に移った方がよいと
   思います。
頑張って下さい。

投稿日時: 19/12/12 21:29:43
投稿者: meron

ありがとうございます。
今回は勉強がてらのものだったので、そこまで実践的な方法でなくても色々な方法を試しています。
 
実際にこの方法以外の方法ではできたのですが、この方法だけなかなかできなかったので皆さんの知見を聞いてみようと思いました。
 
何時間も画面とにらめっこしているのですが、全く分からなくなってしまいました。
ご教示いただけると幸いです。
 
アドバイスありがとうございます!!

回答
投稿日時: 19/12/12 22:32:29
投稿者: simple

(1)(2)について、意味はわかりますか?

投稿日時: 19/12/13 00:12:46
投稿者: meron

意味わかります!!
今回以外基本的にちゃんとつかってます!
 

回答
投稿日時: 19/12/13 09:54:38
投稿者: simple

ああ、そうだったのですね。
でも、どうして今回だけ?
そういう人には、下記文末の(*)を実行することをお薦めします。
 
さて、
(3)の意味もわかりますか?
検索値とそれにヒットするセルの関係を規定しておけば、あとはよしなにやってくれるはず
と言う考えだとすると、それは、ワークシート上の関数的なものと混同しているのかもしれません。
一行一行順を追って実行していく必要があります。
Xを設定するまえに、それを使ってFindしても無駄です。
また、繰り返しの中で実行しないと意味をなしません。
 
>コードのどこが間違えているかご教示お願いいたします
ということでしたが、それだけだとなんですので、
検索と転記のところを質問者さんの方針に沿って実行すると、
こんなことになるでしょう。
 

For k = startRow To endRow
    s = ws2.Cells(k, 2).Value
    Set found = ws1.Columns("B").find(What:=s, After:=ws1.Range("B1"), _
                LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext)
    ws2.Cells(k, 2).Resize(1, 4).Value = found.Resize(1, 4).Value
Next
(注1)最後の一文は、ヒットしたセルから右に4個分の情報を配列に入れて転記しているのと同等です。
(注2)Findの引数は、省略しないほうがよいと思います。
     直前に行った作業の履歴に影響されますから、部分マッチの検索をしたあとに実行すると
      "a"に対して"aaaa"がヒットするといったことが起きてしまいます。
(注3)ヒットしない場合の対応も必要かもしれません。
 
もちろん、色々な手法があります。
数十万行にわたるものなら、作業結果を配列に保持しておいて、
一括してシートに書き込むといった手法が速度上有効になることもあります。
また、Match,VLOOKUPなどの使用も考えられるでしょう。
 
(*)---------------------------
Option Explicit
をモジュールの一行目に挿入するようにして下さい。
そうすれば、今回のような未宣言の変数には警告が出て、 
しかも場所を特定してくれますから、原因が直ぐに判明します。 
下記参照。
http://officetanaka.net/excel/vba/beginner/06.htm

なお、
ツール − オプション − 編集 で
「変数の宣言を強制する」にチェックを入れておけば、
モジュールを作成した時点で、Option Explicitが自動的に挿入されるので、
手間が省けます。
一度だけチェックを入れておけば、以後、気にする必要はありません。
↑ここは是非実行して下さい。

投稿日時: 19/12/14 09:58:43
投稿者: meron

すごい細かくありがとうございます。
 
実はいま勉強がてら上司にこれをやってこいと言われました。
上司は少し適当なところもあるので、少ない処理ならここは省いてもいいよと言われてこうなりました。笑
少ない処理でも勉強のために付けようと思いました。
下記文末(※)知ってましたが、使ってなかったので、使おうと思います。
 
貴重なアドバイスありがとうございます!!
 
 
⑶の意味分からなかったので、細かく教えていただいて良かったです。
少し混同していたかもしれません。
xを定義してからset、繰り返しの中で!とても理解できました。
 
 
vlookupは使ったことあったのですが、matchは使ったことなかったので課題とは別に自分でやってみたいと思います。
本当にありがとうございました!!
返信遅くなってしまい申し訳ありませんでした。