エクセルＶＢＡ（ＬＯＯＫＵＰ関数）Ｗｉｎ2000、Ｏｆｆｉｃｅ2000

質問

いつも関数（LOOKUP）を使って集計をしているのですが、これがVBAで一発処理できれば効率がよくなると思い、頑張ってコードを書き書きしてます。
が、どうもうまくいかきません。下に今書いているコードを書きます。やりたい内容がおわかりいただければいいのですが・・・よろしくお願いします。
Subtest()
Dim ds,ss As Worksheet'(シート名)
Dim Dlrow As Long'(DataSheetの最終行)
Dim Slrow As Long'（DataSheetの最終行）
Dim i,r,ｄAs Long’（行、列カウンタ）
Set ds=Worksheets("DataSheet")
Set ss=Worksheets("syukei")
For DL row =1 To ds.Range("B65536").End(xlUp).Row 'DataSheetの行数判定
Next
ForSLrow=1 To ss.Range("B65536").End(xlUp).Row '集計シートの行数判定
Next
'現在セルにある関数→集計シートA3=LOOKUP(B3,DataSheet!3:H6308,DataSheet!C3:C6308)
'対応する列
'検索値は全て、集計シートのB列
'検査範囲は全て、データシートのH列
↓////※ここらへんから苦戦してます////
'集計シートのA列＝データシートのC列
WithWorksheets("syukei")
Fori=3ToSLrow
'.Cells(i,1)="=Lookup(Cells(i,2),Worksheets（DS).cells3,8):cells(DLrow,8),worksheets(DS).cells(3.3):cells(DLrow:3)"
Nexti
EndWith

'以降, 集計C列＝データのI列のように対応する列毎に、上のコードで処理していこうと思っています。（もしかしてダサイやり方ですか？）
'J列以降は、以降集計J～AS列とデータR～BA列の＋１列
'Forr=10to45→r＝J～AS（10to45）
'Ford=18to53→d＝R～BA（18to53）
'Fori=3toSLrow
'Cell（r,i）＝Lookup・・・・・
'nextr
'nextd
'nexti
EndSub
と、こんな感じで、頑張ってます。よろしくお願いいたします。

回答No.3

nishi6 · ベストアンサー

＞どの辺りをいじればいいのでしょうか
イレギュラーなので、１列毎に書くべきでしょう。
補足にD列がない？下はB、D列は書き込んでいません。

'データシートの値を集計表に書く
For Col = 1 To 45
　　If Not (Col = 2 Or Col = 4) Then
　　　　Select Case Col
　　　　　　Case 1: pCol = 3 '集計表A列=データシートC列

　　　　　　Case 3: pCol = 9 '集計表C列=データシートI列
　　　　　　Case 5: pCol = 16 '集計表E列=データシートP列
　　　　　　Case 6: pCol = 17 '集計表F列=データシートQ列
　　　　　　Case 7: pCol = 6 '集計表G列=データシートF列
　　　　　　Case 8: pCol = 12 '集計表H列=データシートL列
　　　　　　Case 9: pCol = 13 '集計表I列=データシートM列

　　　　　　'集計表J列=データシートR列、8列ずれ
　　　　　　Case 10 To 45: pCol = Col + 8
　　　　End Select
　　　　wsS.Cells(Rw, Col) = wsD.Cells(fRw, pCol)
　　End If
Next

補足

nishi６さん！ありがとうございました。
やはり、1行づつで良かったのですね。試しにしてみたらうまくはいったもののほんとにこれで良いのか・・・と不安だったもので・・・
これは、いろいろと使えそうです。大変助かりました。少しだけ仕組みが分かりました。後の今回分からなかったとこは勉強していきたいと思います。本当にありがとうございました。

回答No.4

papayuka

こんにちは。

既に解決されたみたいなので、補足に対する回答だけ。。

関数なら、このようなケースの場合は LOOKUP では無く
INDEXとMATCHを組み合わせて↓こんな感じでしょうか。

集計シートのA3に
=INDEX(DataSheet!$A$3:$BA$6308,MATCH(B3,DataSheet!$H$3:$H$6308,0),3)

ただ、この手の関数は使いすぎると猛烈に重くなるので程ほどに。

お礼

papayukaさん！ありがとうございました。
＞=INDEX(DataSheet!$A$3:$BA$6308,MATCH(B3,DataSheet!$H$3:$H$6308,0),3)
OKでした。
でも、確かに重くなりました。

今回もいろいろと勉強になりました。また何かありましたらよろしくお願いします。しばらくVBAにはまりそうです。

回答No.2

nishi6

質問を読んで、『集計シートのB列とデータシートのH列を照合して一致したデータシートの値を持ってくる。』ようですが・・・

　少し問題点が・・・
　　１．LOOKUPを使っているが、データシートのH列はソートされている？
　　２．LOOKUPで一致しないデータがあると、#N/Aのエラーがでる。
　　　　　さらに悪いのは、違うデータを持ってくる事がある。どうやって対応している？
　　３．これだけの行数と列数に対してLOOKUPを使用して大丈夫？
　　　　　やるとすれば、すぐ、値に書き換えるか。
　　　　　と、すると、何のためのLOOKUPか。

という事で、私ならFindメソッドを使うでしょう。データのソート、見つからない場合の対応も簡単ですし。

ただ、下のコードのデータシートから集計シートに書き出すところは自信なしです。質問を読んで、よく分からなかったところです。
集計シートのB列とデータシートのH列を照合して、一致したら、その行の処理を行っています。セル単位の処理ではないです。

LOOKUPは使っていませんので、不要なら読み飛ばして下さい。

Sub Syuukei()
　　Dim wsS As Worksheet '集計表
　　Dim wsD As Worksheet 'データシート
　　　　Set wsS = Worksheets("Syukei")
　　　　Set wsD = Worksheets("DataSheet")

　　Dim Rw As Long '集計表の行カウンタ
　　Dim Col As Integer, pCol As Integer '集計表とデータシートの列カウンタ
　　Dim searchArea As Range '検索する範囲
　　Dim fCell As Range '見つけたセル
　　Dim fRw As Long '見つけたセルの行

　　Set searchArea = wsD.Range("H3:H" & wsD.Range("H65536").End(xlUp).Row)
　　Rw = 3
　　While wsS.Cells(Rw, 2) <> ""
　　　　'集計表のB列の値でデータシートのH列を検索
　　　　Set fCell = searchArea.Find(wsS.Cells(Rw, 2), Lookat:=xlWhole)
　　　　If Not fCell Is Nothing Then
　　　　　　'見つかったらその行数を求める
　　　　　　fRw = fCell.Row
　　　　　　'データシートの値を集計表に書く
　　　　　　For Col = 1 To 45
　　　　　　　　If Col <> 2 Then
　　　　　　　　　　Select Case Col
　　　　　　　　　　　　'集計表A列=データシートC列
　　　　　　　　　　　　Case 1: pCol = 3
　　　　　　　　　　　　'集計表C列=データシートI列、集計表D列～I列までは同じ？
　　　　　　　　　　　　Case 3 To 9: pCol = Col + 6
　　　　　　　　　　　　'集計表J列=データシートR列、8列ずれ
　　　　　　　　　　　　Case 10 To 45: pCol = Col + 8
　　　　　　　　　　End Select
　　　　　　　　　　wsS.Cells(Rw, Col) = wsD.Cells(fRw, pCol)
　　　　　　　　End If
　　　　　　Next
　　　　End If

　　　　Rw = Rw + 1 '次の行
　　Wend
　　Set searchArea = Nothing
End Sub

※気がついた事
　For DLrow =1 To ds.Range("B65536").End(xlUp).Row
　Next
　DLrow は最終行+1になってしまうはずです。書くなら、
　DLrow = ds.Range("B65536").End(xlUp).Row でしょう。

　Dim ds,ss As Worksheet
　これも書くなら
　Dim ds As Worksheet
　Dim ss As Worksheet
　です。最初の書き方では、dsはバリアント変数です。

　Dim i,r,d As Long
　も
　Dim i As Long,r As Long,d As Long
　としたほうがいいでしょう。この辺りはHelpに説明があります。

補足

nishi６さん!お久しぶりです。
私には？？？？？？ですが・・・すばらしい！です。とにかくできました。
＞私ならFindメソッドを使うでしょう。
→この辺を勉強してみます。

///取り急ぎ補足ですが・・・///

＞'集計表C列=データシートI列、集計表D列～I列までは同じ？
→いいえ。実は違います。
集計C列＝データのI列
集計E列＝データのP列
集計F列＝データのQ列
集計G列＝データのF列
集計H列＝データのL列
集計I列＝データのM列
です。どの辺をいじればいいのでしょうか？
Caseで1行づつ書くしかないのでしょうか？というかそれで良いのでしょうか？

＞１．LOOKUPを使っているが、データシートのH列はソートされている？
→ 空いているセルはありませんが、昇順降順にはなっていません。他の列でソートしているので・・・

＞２．LOOKUPで一致しないデータがあると、#N/Aのエラーがでる。
　　さらに悪いのは、違うデータを持ってくる事がある。どうやって対応している？
→ 対応に困っています。実際にデータがあるはずなのに持ってこない（＃N/A）

＞３．これだけの行数と列数に対してLOOKUPを使用して大丈夫？
　　　　　やるとすれば、すぐ、値に書き換えるか。
　と、すると、何のためのLOOKUPか。
→ おっしゃる通りです。私の知識不足です。LOOKUPしか思いつきませんでした。

回答No.1

papayuka

こんにちは。
頑張っていらっしゃいますね。

データ構造が解からないので外しているかもしれません。
元ブックをコピーして、テスト環境で試して下さい。

Sub ATest()
Const sN = "DataSheet"
Dim dRange As Range, sRange As Range

　With Worksheets(sN)
　　 Set dRange = .Range("H3:H" & .Range("H65536").End(xlUp).Row)
　End With

　With Worksheets("syukei")
　　 Set sRange = .Range("B3:B" & .Range("B65536").End(xlUp).Row)
　End With

　sRange.Offset(0, -1) = "=LOOKUP(B3," & sN & "!" & dRange.Address & _
　　　　　　　　　　"," & sN & "!" & dRange.Offset(0, -5).Address & ")"

　Set dRange = Nothing: Set sRange = Nothing
End Sub

補足

papayukaさん！早速の回答ありがとうございます。この前も大変お世話になりました。
早速テストしました。回答の内容はGOODです。とりあえず私には？？？？？で結果オーライ状態です。Dim dRange As Range, sRange As Range
.Rangeの使い方など、Offsetの使い方、Addressなど、よく分かりません。これから勉強していきます。

それとは別に、ひとつ問題がありました。結果が、＃Ｎ／Ａのエラーです。
直接関数を入れてみても＃Ｎ／Ａなので、コードの問題ではないようです。考えられる問題点としては、どのようなことがあるでしょうか？
検索値、検査値、対応値の降順昇順ソートとか？・・・・・
現在全てランダムに並んでいます。どれかをソートすればどれかがランダムになるので・・・
現在の検索値、検査値は4桁から6桁の数字です。対応値は、文字列（漢字、かな、カナ）
とりあえず状況的にこんな感じなんですが、よろしくお願いします。