EXCELでのデータ検索とセルへの値挿入

質問

お世話になります。EXCELマクロ初心者です。

sheet1のA列に数字、B列・C列空欄、D列に語幹、Ｅ列に語幹の意味、Ｆ列に単語名、Ｇ列に単語の意味が入っており、Ｆ列の総数はおおよそ7000程です。
sheet2のA列に数字、B列に数字または空欄、Ｃ列に数字または空欄、Ｄ列に語幹（ハイフンを含んだアルファベットの１部）、Ｅ列にＤ列語幹の意味が入力されています。
語幹別に、新規の単語を取得するたびに、sheet2の語幹部分をソートし、Ａ列の数字が更新され、その後、sheet１のＡ列数字が更新され、Ａ列・Ｆ列をキーとして、昇順にソートされます。

当初想定した以上にの語幹の数が増えてしまったので、その重要度を、sheet2のＢ列、Ｃ列に記録して、そのデータを、sheet1のB列、C列に転記せざるをえなくなっております。

現在、sheet1のＡ列に値について、１行目から、個別にsheet2Ａ列を検索し、Ｂ列・Ｃ列のデータを検索し、これを転記するマクロを組んでいるのですが、１行ごとに検索するため、おおよそ、４５秒もかかっています。
これを短縮する方法としては、検索した値を、配列変数のたとえば、ｉに、Ａ列の同じ数字の行数を取得して、Ｂ列・Ｃ列に一括して、sheet2の数字（空欄の場合は空欄）をそのまま転記することだと思うのですが、残念ながら、自分では組み立てられません。

どなたか、ご教示いただければ幸いです。
なお、ご教示いただいた内容を単純にマクロに転記するようなことではなく、自分なりに消化できてから、再度ご連絡をさしあげたいと思いますので、ご理解いただければと思います。
お手数をおかけしますが、よろしくお願いします。

回答No.4

mu2011 · ベストアンサー

NO2です。
>Worksheets("sheet1").Range(Cells(1, 1), Cells(my1Lr, 4)) = st1
>これで一応動くようなのですが、やはり最終行がない場合は、動きません。
>最終行の機能を教えていただければと思います。
　⇒前回でも記載していますが、st1はマクロ内の変数なので実シートに展開しないと編集内容が
　　実体になりませんので必須です。（例えは変ですが、下書き文を本文に書き写すイメージ）

以下参考です。
>my1Lr = Sheets("sheet1").Range("D65536").End(xlUp).Row
>my2Lr = Sheets("sheet2").Range("D65536").End(xlUp).Row
　⇒行数は「Rows.Count」を利用した方がエクセルのグレードにより行数が変わっても対応できる。

>st1 = Worksheets("sheet1").Range(Cells(1, 1), Cells(my3Lr, 4))
>With Worksheets("sheet2")
>st2 = .Range(.Cells(1, 1), .Cells(my4Lr, 4))
>End With
　⇒コードの記述は統一した方が良い。

　　又、回答補足のコードをみているとst1に格納時は「my3Lr」、展開時は「my4Lr」やFor分のエンド
　　条件が主体はA列なにのD列の行数になっていたり平仄が合っていませんので整理した方が
　　よいと思う。

お礼

mu2011様　日付をまたいで、ご丁寧にありがとうございました。最後にいただいた回答で理解できました。またご指摘いただいた点も、今後のために大変参考になりました。またお世話になるかもしれませんが、よろしくお願いします。本当にありがとうございました。

回答No.3

imogasi

補足での疑問について
A2:A5に１，２，３でも入れて
下記を実行してご覧。
Sub test01()
x = Range("A2:A5")
Range("C3:C6") = x
End Sub
C3:C6にそれらの値が入るだろう。
XはVARIANT型の変数で
何もデータ型を宣言してないのでVARIANT型になる。
エクセルVBA特有？の便利で、面白い機能だ。
配列のようなイメージなんだが、配列ではないようだ。
参考
http://pc.nikkeibp.co.jp/article/NPC/20070803/279065/　　など。
ーー
Sub test02()
Dim x As Integer
x = Range("A2:A5")
Range("C3:C6") = x
End Sub
にすると形が一致しませんのエラーになる。
今はそういうものとしておくのが良かろう。将来どういう仕組みになっているのか勉強して。
ーーー
しかしこの質問を読むと、頭がくらくらして判りにくい
（１）実例模擬データを小数作って説明する
（２）人間が手だ行でやるとし束愛の、作業ステップに沿って説明する。
（３）重点を絞って、質問文（内容）簡潔にする
余分な部分があるのではないか。
そういうことを今後の質問では留意をおねがいしますよ。

お礼

imogasi 様　補足への回答と、質問についてのご指摘ありがとうございます。特に、今後の質問の際には、留意します。ありがとうございました。

回答No.2

mu2011

NO1です。
>st1 = Worksheets("sheet1").Range("A1:G7000")
>st2 = Worksheets("sheet2").Range("A1:G7000")
>の部分なのですが、これは指示した範囲が全体として１つに格納されるのでしょうか、
>それとも当該範囲の個別のセルが１つずつ格納されるのでしょうか。
　⇒２次元配列で格納されています。（シートと同じ）

>また、検索・転記の対象が、Ａ列からＣである場合、Ｇ列まで指示するのはなぜでしょうか。
>最後に、最終行のコード、
>Worksheets("sheet1").Range("A1:G7000") = st1
>は何故必要なのでしょうか。
>実際にこのコードをはずして実行してみたところ、何の変化もないことは確認したのですが、値の転記は、
>st1(i, 2) = st2(j, 2)　'B列
>st1(i, 3) = st2(j, 3)　'C列
>で終わっていないのでしょうか。
　⇒サンプルですので、必要な範囲で結構です。
　　又、上記の転記はマクロ内の変数（メモリ）のみなので最後にシートへの転記が必要です。

お礼

mu2011様ありがとうございます。
何度も申し訳ありませんが、よろしくお願いします。

補足

mu2011様　ありがとうございます。
ご教示いただいた内容から、自分なりに、データの可変に対応したものを次のように作成してみました。不細工ですが…

Dim st1, st2 As Variant
Dim my1Lr, my2Lr As Integer

my1Lr = Sheets("sheet1").Range("D65536").End(xlUp).Row
my2Lr = Sheets("sheet2").Range("D65536").End(xlUp).Row

st1 = Worksheets("sheet1").Range(Cells(1, 1), Cells(my3Lr, 4))
With Worksheets("sheet2")
st2 = .Range(.Cells(1, 1), .Cells(my4Lr, 4))
End With

For i = 1 To my1Lr
For j = 1 To my2Lr
If st1(i, 1) = st2(j, 1) Then
st1(i, 2) = st2(j, 2) 'B列
st1(i, 3) = st2(j, 3) 'C列
Exit For
End If
Next
Next

Worksheets("sheet1").Range(Cells(1, 1), Cells(my1Lr, 4)) = st1

End Sub

これで一応動くようなのですが、やはり最終行がない場合は、動きません。
最終行の機能を教えていただければと思います。
何度も申し訳ありません。

回答No.1

mu2011

シート上で更新すると画面更新等、多量になれば時間がかかります。
案１．表更新処理の前後で画面更新を停止／開始を行う。
　　Application.ScreenUpdating = False '画面更新停止
　　　（更新処理）
　　Application.ScreenUpdating = True　'画面更新開始

案２．各シートデータ範囲のデータを変数として取り込み、処理完了後にコピーする。
データは、Cells(行,列)と同様に変数(行,列)として取り扱える。
　　　以下に配列を使用したサンプルコードです。
Dim st1, st2 As Variant
st1 = Worksheets("sheet1").Range("A1:G7000")
st2 = Worksheets("sheet2").Range("A1:G7000")
For i = 1 To 1000
For j = 1 To 7000
If st1(i, 1) = st2(j, 1) Then
st1(i, 2) = st2(j, 2)　'B列
st1(i, 3) = st2(j, 3)　'C列
Exit For
End If
Next
Next
Worksheets("sheet1").Range("A1:G7000") = st1

お礼

mu2011様　回答ありがとうございます。
補足質問については、現在は難しいでしょうか。

補足

mu2011 様
早速のご回答ありがとうございます。
試行してみたところ、処理時間は一瞬でした。
私は、参考書や記事を複数読んでも、特に配列の理解が難しいので、個別の例を確認しながら、やっと理解している段階なので、助かります。
mu2011様のサンプルコードについて補足質問させていただきたいのですが、まず、
st1 = Worksheets("sheet1").Range("A1:G7000")
st2 = Worksheets("sheet2").Range("A1:G7000")
の部分なのですが、これは指示した範囲が全体として１つに格納されるのでしょうか、それとも当該範囲の個別のセルが１つずつ格納されるのでしょうか。
また、検索・転記の対象が、Ａ列からＣである場合、Ｇ列まで指示するのはなぜでしょうか。
最後に、最終行のコード、
Worksheets("sheet1").Range("A1:G7000") = st1
は何故必要なのでしょうか。実際にこのコードをはずして実行してみたところ、何の変化もないことは確認したのですが、値の転記は、
st1(i, 2) = st2(j, 2)　'B列
st1(i, 3) = st2(j, 3)　'C列
で終わっていないのでしょうか。
重ね重ね申し訳ありませんが、よろしくお願いします。