『値だけをコピー＆ペースト』（＜nori＞） Excel：2000 Windows：2000 Sub Macro3() Range("N8:N77").Select Selection.Copy Range("H8").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub 上のまくろはマクロ記録したものです。 これをN8に入力があったら、P8にその値だけが入るようにするにはどのようにすればよいのでしょうか？ 因みに、N8からN77までのセルには、=VLOOKUP($B8,INDIRECT($F$4),2,FALSE)の返り値が入ります。 本来は、H8からH77に入っていた式ですが、VLOOKUP関数の検索範囲がINDIRECT関数の返り値に なっている関係で、その値が変更になったときにもN/Aエラーが出ないに対策をと考えておりま す。宜しくお願い致します。 ---- 意味がよく分からないのですが・・・。 １． 上記コードは、N8:N77をコピーして、H8から貼り付けるのですよね？ ＞P8にその値だけが入るようにするには ＞本来は、H8からH77に入っていた式ですが この辺りと整合性はとれていますか？ それとも、上記コードとは別の話でしょうか？ ２． N8セルには関数が入力されているのですよね？ ＞N8に入力があったら の≪入力≫と、関数が入力されている【入力】と言う言葉は どの様に使い分けますか？ ３． ＞P8にその値だけ 「その」は何を指しますか？ ４． 今回のご質問は　Macro3を実行したときでも N8に入力がなかったら　そこに書かれているコードを実行しない様にしたい と言う事ですかね？ セルの状況がどの様な状況の時に上記マクロを実行したら この様に成るのを、そうさせずこの様にしたい。 と言うように、 　A.実行前のワークシートの状況 　B.希望しない実行結果 　C.希望する結果 ３つそろえてご提示の上、ご説明頂くのが 分かりやすいのかもしれません。 なお、表はエクセルから直接貼り付けられますので ご活用下さい。 (HANA) ---- HANAさん、お世話になります。 エクセルから直接貼り付けること試してみたのですが、ずれてしまってかえって見辛いかと思いまして、 簡略化して書きます。 B　　 D　　 E　　　 F　　　　　　G　　　　H　　　　I　　　J　　　K　　　L 検索値　業者名　番号　検索値を　検索値を　　VLOOKUP　数量 VLOOKUP VLOOKUP　=I＊K 入力規則　　　　　整形した値１ 整形した値２ カタログ名　　 単位を　 単価を　 金額 （リスト）　　　　　　（関数）　　関数）　 ページ等を 求める　 求める　を求める 　　　　　　　　　　　　　　　　　　　　　　　　求める 　　　　　 例えば、BC(結合)に四菱鉛筆　B：12本入と入力すると、Fには鉛筆、GにはB：12本入、Hにはカタログ名とページ数、 Iには希望の数量を入力、Jには単位としてダース、Kには単価として、720、LにはI*Kが入ります。 この様な形なのですが、F3セルに係を選択する入力規則（リスト)があり、もし、ここでA係を選択すると B、H、J、K（検索値、検索範囲）が変化するように設定しました。 ここで問題となるのが、検索値のB、検索範囲のH、J、Kが変わってしまうと、先に入力してあった、H、J、Kの 式が正常に動作しなくなり、エラーが出てしまいます。 そこで考えたのがH、J、Kに入る値は、他で求め、その値だけをH、J、Kにペーストすればと考えました。 そして、希望する物品請求の入力が終わると、業者名でフィルターをかけ、業者毎のシートに転記して、経理に 上げる請求伝票が出来る様にと考えております。 業者名でフィルターをかけ、請求伝票を作るところは完成し、渡したところ、係により、請求する物品や購入先 が異なるので、そのことも考慮して欲しいとの要望があり、やり始めたのですが、あーれ！状態になってしまいました。 こんな文章でお判り頂けますでしょうか？　本当にスミマセン！！！ ---- う〜ん、取りあえず　こちらの続きと言う事ですね。 [[20080423091646]]『マクロ記録したものを関数化したい』（nori） 一応↑でも同じ様な事を書いたのですが・・・ 入力用のシートと、提出用のシートを分ければ良いんじゃないですか？ 必要最低限のマクロで済ませようと思うなら 入力が終わったら、別のシートへ転記して 入力シートのデータは消して仕舞うのが簡単かと思います。 もちろん、間違えて別のシートに入力されてしまうのなら そちらは保護をかける等必要に成ってくると思いますが。 可能ですか？不可能ですか？ そうそう、新規投稿の際は ＞（）の中に自分のニックネーム と言う事になっていたと思います。 勝手ながら、お名前の前後に　括弧をつけさせて頂きます。 なお、通常文でも　名前は自動では入りませんので コメント記入の際は、その都度ご署名を御願いします。 (HANA)　　　　　　　　 ---- HANAさん　お世話になります。マナーを知らず失礼いたしました。 「マクロ記録したものを関数化したい」は皆様のおかげで完成し、今、順調に動いております。 また、現在、入力用シートと提出用シートも別にしてあります。 今回の問題は、入力用シートでの処理なのです。VLOOKUP関数の検索値、検索範囲をあるセルを 参照して、決定するようにしたのですが、この場合に、幾つか入力した後、検索値、検索範囲を 変更してしまうと、先に入力してあった値がエラーになってしまいます。それを回避する方法と して、VLOOKUP関数で得られる値を入力用シートの別の所にし、その値だけを入力用シートして 使っている場所に転記すればVLOOKUP関数の検索値、検索範囲の変更により、エラーが出ても問 題を解決できるのではと考えているのです。 なぜ、この様にしたのかと言うと、検索値（入力規則のリスト）、検索範囲が非常に大きな場合 にそれを分割して小さくして、検索を容易に出来るようにする。また、係ごとに発注先や発注品 目が違うので、この様にしたほうが効率的になるのではと考えたからです。 無駄な努力ですか？？？ ---- ごめんなさい、何がなさりたいのかは分かるのですが 処理の中のイメージがつかないのですよね。 ＞幾つか入力した後、検索値、検索範囲を変更してしまうと ユーザーは何故その様な事をするのですか？ ついうっかり？ それとも、入力済みのものを変更しようとして？ ＞H、J、Kに入る値は、他で求め、その値だけをH、J、Kにペーストすればと考えました。 確かにBを変更してもエラーには成りませんが、変更されて仕舞ったら その行は整合性がとれなくなると思いますが　良いのですか？ 流れとしては Ａ−１．B8でリストから選択 　−２．別の場所で、H8,J8,K8の値を求める 　−３．コピーし、H8,J8,K8に値を貼り付ける をなさろうと思っているのですよね？ このマクロはいつ実行するのですか？ Bに値を一つ入れるたびに一行ずつ行うのですか？ 全て入力が終わった後に実行実行し たとえば5行入力済みだった場合は Ｂ−１．B8:B12の選択されているデータを元に 　−２．H8:K12の値を別の場所で求め 　−３．コピーし、該当個所に貼り付ける を行う様な事を想定して居られるのでしょうか？ それにしても、再度マクロを実行する時に B列の値が変わってしまっていたら 別の場所ではエラー値が出ているので それを貼り付けると、やっぱりH,J,K列は エラーになりますよね？ ・・・コードの中で 「H,J,K列に既に値が貼り付いている場合は貼り付けない」 と言う処理を入れるなら別ですが・・・・ それも、Ａの方法で人が一行ずつ実行させ その一行がどうか？を判断するのはそう難しく無いと思いますが 有る範囲をコピーし、別の範囲に貼り付ける際に 一行ずつ貼り付け先のセルに既に値が入っているか居ないか 確認しながら貼り付けていく　と言うのは 少し高度な技術かと思います。 ＞現在、入力用シートと提出用シートも別にしてあります。 と言う事ですが、提出用シートにはどの様にデータを移動させて居るのですか？ Bの値が変わっても、変わらない様に成っているのでしょうか？ 問題は、一度確定したB列の値を変更されてしまう所に有ると思います。 ですから・・・・表現が難しいですが・・・・ 「入力する場所」と、入力されたデータを「のけておく場所」を作って １．入力が終わってマクロを実行したら、入力済みデータは ２．のけておく場所へ移動させ、値にする。 ３．移動した後の入力する場所のデータは消す の様にしておけば、既に入力されたデータがおかしく成る事はありませんよね。 こちらでしたら、記録の範囲内で出来そうに思います。 ・・・うまく伝わりますかね？ ＞なぜ、この様にしたのかと言うと の部分は、非常に良い考えだと思います。 そして、Bが変えられてしまうとおかしな事になる これも分かりますよ。 たとえば、別の場所を AA列以降とすると 私は、noriさんのご希望は 　B列で選択し、AA列以降で対応する値を求め 　その結果を H,J,K列に貼り付ける だと思っています。 私がお薦めするのは 　B列で選択し、H,J,K列に数式で求め 　B:Lの値をAA列以降に貼り付ける です。 一回に入力する量が少ないので有れば 上部分10行程度を入力する場所とし 下部分に入力済みのデータを貼り付けて行けば データを見失う可能性も低くなると思います。 ちなみに、このマクロはnoriさんが使用される物ですか？ それとも、不特定多数の方が使用するのですか？ (HANA) ---- HANAさん、お世話になります。 ＞幾つか入力した後、検索値、検索範囲を変更してしまうと ＞ユーザーは何故その様な事をするのですか？ ＞ついうっかり？ ＞それとも、入力済みのものを変更しようとして？ 分かり辛くてすみません 例えば、Ａ係で、Ｂ商店から、Hogeと言う商品を買おうと思い、Ｂ列のリストで品名を選択するとき、 Ｂ商店が取り扱う品物の一覧が、Ｂ列のリストの中に入って居たほうがHogeを選択しやすい。 また、会社で一括して買ってある「用品」を選ぶときは、用品の一覧が、リストに表示されるように したほうが選択がしやすい、だろうと考えました。そこで、Ｆ3セルで用品を選択すれば用品のリスが 見られるようになり、幾つか選択したとします。その後、Ｃ社からあの商品、Ｄ社からあの商品とあの 商品をと選択することを考えていただくとお分かり頂けると思います。 これを実現するためにVLOOKUP関数の検索範囲をINDIRECT関数で指定するようにすればと考え、指定の 仕方を教えていただき、動くようになったのですが、用品を請求するために検索範囲を動かしてしまう のですからＢ商店の商品Hogeを購入するためにVLOOKUP関数の検索範囲が結果として動いてしまい、エラ ーが出てしまいます。 これを回避する方法をと考えたのです。できることなら、Ｂ列のリストで商品が選択されたときにコピ ー＆ペーストが起きたらば良いなーと考えております。 提出用のシートには、請求する物品を入力し後で転記用のマクロが書かれたボタンを押すことによって 業者名ごとにフィルターをかけ、業者名のついたシートに転記するようになっております。 不特定多数の方が使う予定です。 自分ではここまでは、「ふむふむ」と悦に入っていたのですが、もしかするとトンでもない勘違いを しているのかもしれないのですが？？？？ 問題点等ありましたらご指摘ください。もし、コリャぁダメだという印象でしたら、率直にその旨お伝 え下さい。やめます。 ---- えっとですねぇ いきなり「F3セル」と言われても分からないんですよね。 そちらのシート構成が見える訳ではないのですから。 ですから、レイアウトを御呈示の際は　列番号だけではなく 行番号もつけてください。 [B:C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [3] A係　　▼ 　： ↑入力規則（A係/用品) 　： ↓入力規則（F3の値に対応し変化） ◆セルには関数が入力済み [7] 検索値▼ 業者名 番号 品目◆ 備考◆ ページ★ 数量 単位★ 単価★ 金額◆ [8] 鉛筆 B：12本入 鉛筆 12本入 P32 5 ダース 720 3,600 [9] ↑=VLOOKUP($B8,INDIRECT($F$3),2,FALSE) ★セルに同様の式 上の様に成っていますか？ そして、「A係」以外の物を選ぼうと思ったらF3セルの値を変更する必要がある。 すると、★セルの値が変わってしまうから困る。 と言う事ですかね？ 私なら、F3セルを行毎に作りますけど。 ただし、上と同じなら選択しなくて良い様にします。 例えば↓の様な感じで [A] [B]▼ [C]▼ [1] 関数用 場所選択 品目選択 [2] A係 A係 A [3] A係 B [4] 用品 用品 C [5] 用品 A2に =IF(B2="","",B2) A3に =IF(C2<>"",IF(B3="",A2,B3),"") の式が入っています。 このA列の値を数式や入力規則に使えば 「入力のためにどうしてもF3の値を変更しなきゃいけないのに 　これをすると、計算がおかしくなるのよ」 なんて事には成らないと思います。 もちろん当初と違って、律儀な人には 「A列に表示されたのと違う物を選びたい時にだけ 　B列を選択すれば良いからね！！」 と、伝える必要が有ると思いますが。 ちなみに ＞コリャぁダメだ と言うより、 「そこまでやらなくて良いんじゃないの？」 と思ってしまいます。 （余り理解出来ていない所も有りますが・・・。） 一応確認ですが レイアウトのご説明時にF8:G8は 　検索値を整形した値（関数） と書いてありますが、マクロではなく 関数で行っているのですよね？ (HANA) ---- HANAさん、有難う御座います。 >上の様に成っていますか？ >そして、「A係」以外の物を選ぼうと思ったらF3セルの値を変更する必要がある。 はい、HANAさんがお書きになった様になっております。 >私なら、F3セルを行毎に作りますけど。 >ただし、上と同じなら選択しなくて良い様にします。 >例えば↓の様な感じで > [A] [B]▼ [C]▼ >[1] 関数用 場所選択 品目選択 >[2] A係 A係 A >[3] A係 B >[4] 用品 用品 C >[5] 用品 >A2に >=IF(B2="","",B2) >A3に >=IF(C2<>"",IF(B3="",A2,B3),"") >の式が入っています。 >このA列の値を数式や入力規則に使えば >「入力のためにどうしてもF3の値を変更しなきゃいけないのに >これをすると、計算がおかしくなるのよ」 >なんて事には成らないと思います。 この部分に関してはよく理解できていません。自分の作成中のシートに 写して、試してみます。 >私なら、F3セルを行毎に作りますけど。 >ただし、上と同じなら選択しなくて良い様にします。 >例えば↓の様な感じで 通勤の際にこの方法、頭に浮かびました。試して見ます。 お示しいただいた方法で無いと、すっきりしませんね。 >レイアウトのご説明時にF8:G8は >検索値を整形した値（関数） >と書いてありますが、マクロではなく >関数で行っているのですよね？ F8セルには、=IF($B8="","",LEFT($B8,FIND(" ",$B8,1)-1)) G8セルには、=IF($B8="","",REPLACE($B8,1,FIND(" ",$B8,1)," "))と書き込みました。 沢山のことご提示頂きましたので、ご返事するまでにチョッと時間を下さい。試してみます。 ---- HANAさん、ご無沙汰して申し訳ありません。 これを実際にお使いになる方々にご意見を伺いましたところ、「そこまでの機能は要らない」と言われて気分が萎えてしまい、手を付けておりません。 それでも、完成させたいと思い出し、また、始めました。 ただ、このまま放置しても置けませんので、一旦閉じ、出来上がった時点で、ご報告させていただきます。 お蔭様で、方向性が定まりましたので、時間はかからないと思います。本当に有難う御座いました。 ---- ご報告有難う御座います。 私はもう、しばらくは待って ＞出来上がった時点で、ご報告 と言う形に成ると決めていたのですがね。（笑） 確かに、なにも無いでひたすら待つよりは 事の経緯が分かって、ゆっくり待つ方が良いですが やっぱり待ってしまう事には変わりないですからね。 ＞「そこまでの機能は要らない」と言われて気分が萎えてしまい、 でも、今回の件で　大きな飛躍を遂げられたのではないでしょうか？ （・・・大げさすぎますかね？笑） 今回の方法は（前スレも含めて　手法で有れば全般としてそうだと思いますが） 「知っている」「一度考えた事がある」　と 「知らない」とでは、大きな違いが有ると思います。 今回は、残念ながら不採用と言うことになって（？） これに費やした時間が無駄に成った様に思われるかもしれませんが この考え方が使える日が、そう遠くない未来に有ると思いますので 何事にもめげず、頑張ってくださいね。 (HANA)