いつも関数(LOOKUP)を使って集計をしているのですが、これがVBAで一発処理できれば効率がよくなると思い、頑張ってコードを書き書きしてます。
が、どうもうまくいかきません。下に今書いているコードを書きます。やりたい内容がおわかりいただければいいのですが・・・よろしくお願いします。
Subtest()
Dim ds,ss As Worksheet'(シート名)
Dim Dlrow As Long'(DataSheetの最終行)
Dim Slrow As Long'(DataSheetの最終行)
Dim i,r,dAs 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列の+1列
'Forr=10to45→r=J~AS(10to45)
'Ford=18to53→d=R~BA(18to53)
'Fori=3toSLrow
'Cell(r,i)=Lookup・・・・・
'nextr
'nextd
'nexti
EndSub
と、こんな感じで、頑張ってます。よろしくお願いいたします。
No.3ベストアンサー
- 回答日時:
>どの辺りをいじればいいのでしょうか
イレギュラーなので、1列毎に書くべきでしょう。
補足に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
この回答への補足
nishi6さん!ありがとうございました。
やはり、1行づつで良かったのですね。試しにしてみたらうまくはいったもののほんとにこれで良いのか・・・と不安だったもので・・・
これは、いろいろと使えそうです。大変助かりました。少しだけ仕組みが分かりました。後の今回分からなかったとこは勉強していきたいと思います。本当にありがとうございました。
No.4
- 回答日時:
こんにちは。
既に解決されたみたいなので、補足に対する回答だけ。。
関数なら、このようなケースの場合は 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
- 回答日時:
質問を読んで、『集計シートのB列とデータシートのH列を照合して一致したデータシートの値を持ってくる。
』ようですが・・・少し問題点が・・・
1.LOOKUPを使っているが、データシートのH列はソートされている?
2.LOOKUPで一致しないデータがあると、#N/Aのエラーがでる。
さらに悪いのは、違うデータを持ってくる事がある。どうやって対応している?
3.これだけの行数と列数に対して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に説明があります。
この回答への補足
nishi6さん!お久しぶりです。
私には??????ですが・・・すばらしい!です。とにかくできました。
>私ならFindメソッドを使うでしょう。
→この辺を勉強してみます。
///取り急ぎ補足ですが・・・///
>'集計表C列=データシートI列、集計表D列~I列までは同じ?
→いいえ。実は違います。
集計C列=データのI列
集計E列=データのP列
集計F列=データのQ列
集計G列=データのF列
集計H列=データのL列
集計I列=データのM列
です。どの辺をいじればいいのでしょうか?
Caseで1行づつ書くしかないのでしょうか?というかそれで良いのでしょうか?
>1.LOOKUPを使っているが、データシートのH列はソートされている?
→ 空いているセルはありませんが、昇順降順にはなっていません。他の列でソートしているので・・・
>2.LOOKUPで一致しないデータがあると、#N/Aのエラーがでる。
さらに悪いのは、違うデータを持ってくる事がある。どうやって対応している?
→ 対応に困っています。実際にデータがあるはずなのに持ってこない(#N/A)
>3.これだけの行数と列数に対してLOOKUPを使用して大丈夫?
やるとすれば、すぐ、値に書き換えるか。
と、すると、何のためのLOOKUPか。
→ おっしゃる通りです。私の知識不足です。LOOKUPしか思いつきませんでした。
No.1
- 回答日時:
こんにちは。
頑張っていらっしゃいますね。
データ構造が解からないので外しているかもしれません。
元ブックをコピーして、テスト環境で試して下さい。
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など、よく分かりません。これから勉強していきます。
それとは別に、ひとつ問題がありました。結果が、#N/Aのエラーです。
直接関数を入れてみても#N/Aなので、コードの問題ではないようです。考えられる問題点としては、どのようなことがあるでしょうか?
検索値、検査値、対応値の降順昇順ソートとか?・・・・・
現在全てランダムに並んでいます。どれかをソートすればどれかがランダムになるので・・・
現在の検索値、検査値は4桁から6桁の数字です。対応値は、文字列(漢字、かな、カナ)
とりあえず状況的にこんな感じなんですが、よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- Visual Basic(VBA) excel vbaでvlooupの変数がわかりません。 7 2022/05/30 09:35
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Visual Basic(VBA) いつもお世話になっております、VBAで教えて頂きたいのですが 2 2022/05/05 22:20
- Visual Basic(VBA) VBAが止まります。 1 2022/09/02 14:51
- Visual Basic(VBA) 稀に1円合いません? Sheet1から金額と個数を貼り付ける下記コードで、金額を切り上げるコードを何 3 2022/09/05 15:11
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Visual Basic(VBA) VBAで質問ですが、皆さんはどの様に導き出しているのでしょうか? 6 2022/05/03 21:53
- Visual Basic(VBA) VBAで時間(00:00形式)を積算(足し算)したい 1 2022/11/15 17:04
- Visual Basic(VBA) VBAで教えて頂きたいのですが? 1 2022/04/29 02:36
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
はがきについて。
-
エクセル 文字を増やしたい。
-
セルの内容表示が邪魔になる
-
Microsoft365に変えたのですが...
-
エクセルの計算
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
Excel ピボットテーブルで日付...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
Excelのチェックボックスの使い...
-
エクセル 白黒印刷で白線を印刷...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの条件付き書式につい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報