質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

4回答

2812閲覧

VBA VLOOKUP+指定範囲コピー 値で貼り付け

moe7474

総合スコア5

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2018/06/11 01:03

編集2018/06/11 02:34

前提・実現したいこと

VBA初心者です。よろしくお願い致します。

2つ行いたいことがあります。

(1)図1の表をしたから見ていき、K列が【今週】M~AO列をコピーして
その一つ上の【先週】のM~AO列に値で貼り付け

(2)図1の表を下から見ていき、K列が【今週】のM~AO列と、
グラフ範囲内のH列に下記VLOOKUPで参照した値を入力

=IFERROR(VLOOKUP($G行数,原価予実一覧!$A$3:$AO$10000,各列の2行目,0),"")

※図1のグラフの量は毎回変化します。

(図1)シート名:データ
イメージ説明
(図2)シート名:原価予実一覧
イメージ説明

説明不足の点がありましたら申し訳ございません。

ご教示お願い致します。

やったこと

やったこと致しましては、まったくの初心者の為インターネットで似たような案件を検索し、
実際に使えないかと探しましたが、K列の基準を指定してから何かを行うというところに
躓いてしまっている次第です。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

coco_bauer

2018/06/11 01:31

M~AOのようにアルファベットで表されているのは「列名」です。行は数字で表されます(なので、AO行という表記はあり得ません)。混乱しますので表記を修正したほうが良いです。
moe7474

2018/06/11 02:00

ご指摘ありがとうございます。勉強不足で申し訳ございません。
guest

回答4

0

ベストアンサー

VBA

1Sub test() 2 Dim rngKey As Range 3 Dim rngFind As Range 4 Dim c As Range 5 Dim rngFrom As Range 6 Dim rngTo As Range 7 Dim ixRow As Long 8 9 With Worksheets("データ") 10 Set rngKey = .Range(.Range("H4"), .Cells(.Rows.Count, "H").End(xlUp)) 11 End With 12 With Worksheets("原価予実一覧") 13 Set rngFind = .Range(.Range("A3"), .Cells(Rows.Count, "G").End(xlUp)) 14 End With 15 16 For Each c In rngKey 17 If c.Address = c.MergeArea(1).Address Then 18 Set rngTo = c.MergeArea.Offset(, 5).Resize(, 28) 19 rngTo.Rows(3).Copy rngTo.Rows(2) 20 ixRow = WorksheetFunction.Match(c.Offset(, -1), rngFind, 0) 21 Set rngTo = rngFind(ixRow, 1).Resize(3) 22 23 rngTo(1, 1).Value = rngFrom(1, 7).Value '見込み売価 24 rngTo(1, 2).Value = rngFrom(1, 8).Value '見込み原価 25 rngTo(3, 1).Value = rngFrom(3, 7).Value '今週売価 26 rngTo(3, 2).Value = rngFrom(3, 8).Value '今週原価 27 rngTo(1, 3).Resize(, 26).Value = rngFrom(1, 10).Resize(, 26).Value 28 rngTo(3, 3).Resize(, 26).Value = rngFrom(3, 10).Resize(, 26).Value 29 End If 30 Next 31End Sub

こんな感じでいいと思います。
が、セルの位置が微妙に違うかも知れません。
そちらで意図を汲み取って直していただけますか?

もしわからない部分があれば、また聞いて下さい。
処理速度とかは全然気にしてないので、
我慢できない速度ならそれも行ってください。
しかし、とりあえず動いてから、高速化は考えましょう。

投稿2018/06/11 04:56

編集2018/06/11 05:00
mattuwan

総合スコア2136

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

moe7474

2018/06/11 05:36

お世話になっております。 ありがとうございます! 少しずつ確認をしながら使用させていただきます。 迅速なご対応に感謝いたします。 説明不足の問にお付き合いくださりありがとうございました。
guest

0

>製品NO.は異なるが型式は同じという場合もございます。
>その場合でもMatch関数は使用できるのでしょうか?
どうせ非表示の作業列を使っておられるようなので、
文字列を繋ぎ合わせたものを入れる列を追加して、そこで検索するか、
Match関数も遅いかも知れないので、
あっさり自作で検索の仕組みを作ってもいいかもしれません。

とりあえず、処理速度は一切無視して、見えてる部分だけで、
転記元&転記先のセル位置を特定する方向でなにか考えてみます。

>最終的にJ列で昇順で並び替えをして、
とりあえず、並び替えは特に意味はないということで理解しておきます。

>データは必ず3行1組ですが、国別ではありません。
>情報漏洩防止のため、産地・果物で表しましたが、実際には
>果物→製品のNO.
>産地→製品の型式
>が入ります。
>製品NO.は異なるが型式は同じという場合もございます。
ということは、製品No.の順番は変更がないか無意味として解釈していいのかなぁ。。。
無意味はたぶん言い過ぎかな?例をみると固定でもう決まっている?
Vlooupの式を見ると
G列の1データの3行目で検索してるからそれで考えたらよさそうですかね?

投稿2018/06/11 03:30

mattuwan

総合スコア2136

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

moe7474

2018/06/11 03:54

お世話になっております。 あっさり自作で検索の仕組みを作ってもいいかもしれません。 >検索用に自作で作ったものが【図1のG列】と【図2のA列】になっています。 同じ製品NO.の1行目、2行目、3行目と検索するため【製品NO.+1/2/3】になっています。 製品No.の順番は変更がないか無意味として解釈 >図1の製品NO.の順番に変更はありません。 しかし、図2は途中で新しい製品が出てくると割り込むため変更が出てきてしまいます。 Vlooupの式を見ると G列の1データの3行目で検索してるからそれで考えたらよさそうですかね? >その検索値での検索をお願いいたします。 よろしくお願い致します。
guest

0

>現時点では、このデータをVLOOKUPですべて飛ばしています。
シート上に数式をセットしているということですか?
シート上で数式を入れていても、
マクロでVLookupを使っていてもいいですが、数式をどのようにしているか、
例をお願いします。
あと、書きかけているコードもあれば提示をお願いします。
今一つやりたいことと、やっていることが、こちらでつかめてません。

>ですが、値ですべて貼り付けてしまうと次回の作成ができないため
???です。

原価予実一覧シートを参照することが解っていれば、値であろうと数式であろうと
問題ないように思いますが?
しかも、数式だと先週に転記する前に原価予実一覧シートを更新してしまえば、
おかしなことになりますよね?
その辺はどうお考えですか?

あと、

グラフ→×
表→○
ですよね?
折れ線グラフとか棒グラフとかの話ではないですよね?

投稿2018/06/11 02:03

mattuwan

総合スコア2136

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

moe7474

2018/06/11 02:33

お世話になっております。 説明不足&説明下手で大変申し訳ございません。 シート上に数式をセットしているということですか? >シート上に数式をセットしております。 図1のM6のセルには、【IFERROR(VLOOKUP($G6,原価予実一覧!$A$3:$AO$10000,M2(2列目に拾いたい番号を入力しています),0),"")】と入力しています。 ※N列の原価の【見込み欄】だけは、【IFERROR(VLOOKUP($G6,原価予実一覧!$A$3:$AO$10000,$M1$,0),"")】となっています。 申し訳ございません。K列を拾う段階でつまずいてしまったため、 書きかけのコードは使えなかったため消してしまいました…。 次回作成に関して >現時点では 第一基準:K列【見込み・先週・今週】 第二基準:J列【隠れてしまっていますが、上から1・2・3・4と番号を付けています】 を基準に並び替え →今週のM~AO列をコピーして先週に値で貼り付け →J列を基準に並び替えて元の順番に戻す →原価予実のデータを新しくする →自動で切り替わってくれないため、M~AO列を範囲指定して【=と=】で置換 と作成をしております。 先ほども申し上げました通り行数がとても多いため、並び替えや置換を行うと 再検索もあり、とても時間がかかります…。 VBAでなく、関数だけで行う場合にすべてを値で貼り付けをしてしまうと 次回にもう一度関数の設定を行わなくてはいけないのでとても時間がかかってしまうため 行っていません。 マクロでの作成の場合、 (1)今週の行を先週に値で貼り付けるマクロ (2)原価予実一覧のデータ更新 (3)VLOOKUPで参照をするマクロ といった流れで使用できればと考えております。 折れ線グラフとか棒グラフとかの話ではないですよね? >勉強不足で申し訳ございません。 表です。 ご面倒をおかけしますがよろしくお願い致します。
mattuwan

2018/06/11 02:56

ごめんなさい。 質問ばかりになってどうすればいいかをアドバイスできなくて、 何となくわかりましたが、 また、新しい情報が出てきましたね^^; >第二基準:J列【隠れてしまっていますが、上から1・2・3・4と番号を付けています】 >を基準に並び替え なにをどう並び替えるのですか? それから、データは絶対3行1組で国別に並ぶんですよね? VLookup関数も便利ですが、融通が利かないので、 Match関数で国ごとの行の位置を特定し、そこから、相対的に、 1行目が見込み 2行目が先週の実績 3行目が今週の実績 と指定できると思いますがいかがでしょうか?
moe7474

2018/06/11 03:13

お世話になっております。 説明不足で申し訳ございません…。 至らない点が多い中ありがとうございます。 なにをどう並び替えるのですか? >J列には1・2・3・4と増えていき、最終行には11000と入っています。 最終的にJ列で昇順で並び替えをして、 (第一基準での並び替えから)元々の順番になるように設定しています。 マクロを使用する際は必要ないかと思い省略させていただいておりました。申し訳ございません。 データは絶対3行1組で国別に並ぶんですよね? >データは必ず3行1組ですが、国別ではありません。 情報漏洩防止のため、産地・果物で表しましたが、実際には 果物→製品のNO. 産地→製品の型式 が入ります。 製品NO.は異なるが型式は同じという場合もございます。 その場合でもMatch関数は使用できるのでしょうか? 産地・果物で表したことでわかりにくくなってしまっていましたら申し訳ございません。 よろしくお願い致します。
guest

0

ども^^

やりたいことは、

1)「データ」シートのK列が、「今週」の行を、K列の先週の行に転記し、
2)「原価予実一覧」シートの「実績」の行を、「データ」シートの対応する国(産地)の「今週」の行へ
転記したいということですか?

セルに数式をセットすると、自動で更新されるとわけわかんない状態になるので、
VBAを使って転記する作業を自動化したいという事でしょうか?

ちなみに「見込み」も両方のシートにあるようですが、転記して、値を更新しなくてもよいのですか?

投稿2018/06/11 01:29

mattuwan

総合スコア2136

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

moe7474

2018/06/11 01:51

ご返事いただきありがとうございます。 現時点では、このデータをVLOOKUPですべて飛ばしています。 図1の行数が11000行を超える為資料が8000KBを超える重さになってしまっています。 ですが、値ですべて貼り付けてしまうと次回の作成ができないため 少しでも軽くするためにマクロで作成を行えないかと考えております。 見込み欄が抜けておりました。(2)のように見込み欄を転記したいです。 図1のG列の【検索用2】のデータを基準に図2のA列から探し出し、VLOOKUPしたいと思っています。 お手数おかけして申し訳ございませんがご教示お願い致します。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問