[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『転記』(謙児)
いつもお世話になり有難うございます。
質問は、下記1と追加2です。
下記コードを実行すると、1、Range("b1000").End(xlUp).Offset(1).Select
でエラーが生じます。(アプリケーション定義又はオブジェクト定義の
エラー)
色々調べましたが、何が悪いのかわかりません。
教えて頂けないでしょうか?
(5年前に作成しました表(VBA含みます)の中にある一部、転記コードを
利用して貼り付けしたのですがーーー その表は、転記が出来ます。)
したいことは、次の通りです。
Sheets("請求書")b91:ab91のデータをSheets("保存先")b2:b91に転記します。
業者毎に転記を繰り返し行います。
(b91:ab91のデータは、Sheets("請求書")の上の、行と列で色々な項目の
入力箇所です。)
そのデータをb91:ab912にいったん揃えてからSheets("保存先")
(一行目には、b91:ab912のデータ項目名を横列にb1からab1に
あらかじめ入力しています。)に転記をしていきます。
Sub jikkou()
Application.ScreenUpdating = False
Sheets("請求書").Select Range("b91:ab91").Select Selection.Copy
Sheets("保存先").Select
Range("b1000").End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False Sheets("請求書").Select Range("a12").Select Application.ScreenUpdating = True
End Sub
*追加質問です:
請求書ですから項目名の内、摘要欄が17行あります。
その他の項目は、一行だけです。
摘要欄の1行目のデータがb91だとしましたらb107行迄続きます。
そうしますと Sheets("請求書").Select
Range("b91:ab107").Selectの範囲でいいのでしょうか?
それと1回目に保存先に転記した後で、2回目に保存先に転記した場合には、
1回目転記したデータ2行目の下3行目から転記されますか?(摘要欄以外の項目データは、1行分の為、2回目の転記の際には、2行目から貼り付け
されるかと思います。摘要欄のデータは、17行の内16行分は、消えます
か?)
追加2、それとも1回目の転記データ、最大行の17行目の下18行目から、
2回目の転記データすべてが、18行目から転記されますか?
追加2、を実行したいのです。
以上ですが、長々なって判読しずらくて申し訳ございません。
1と追加2、のコードを教えて頂けませんでしょうか?
< 使用 Excel:Excel2013、使用 OS:Windows10 >
とりあえず気が付いた点だけ。
Selection.PasteSpecial の最後、行継続文字 アンダースコア _ が抜けています。
ここでの記述ミスなら済みません。
上記部分修正後
こちらで実行しましたがエラーなしで正常に動いていますよ。
では
m(__)m
(隠居じーさん) 2018/03/16(金) 10:42
が、その前に、まずはそのコードを整理するところから手を付けた方がよいとおもいます。
具体的には、〇〇〇を選択して、選択しているものを×××するとなっている部分です。
〇〇〇.Select Selection.××× ↓ 〇〇〇.××× 大抵の場合は、上記のように〇〇〇を×××する。というように記述できます。
そのほかも含め、ちょこっと整理すると、提示のコードはこんな感じになります。
Sub jikkou()
Application.ScreenUpdating = False '画面更新を停止
Sheets("請求書").Range("B91:AB91").Copy Sheets("保存先").Range("B1000").End(xlUp).Offset(1, 0).PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False
With Application .CutCopyMode = False 'コピーモードを解除(コピー元の点線囲みを解除) .Goto Reference:=Sheets("請求書").Range("A12"), Scroll:=True .ScreenUpdating = True '画面更新を再開 End With End Sub
主要な動きとしては、
(1)「請求書」シートの「B91:AB91」をコピーする。
(2)コピーしたものを「保存先」シートのA1000からみて上端の1行下のセルを起点とした範囲に貼付
ということになります。
以上を踏まえて、
質問の1つめ。
>ange("b1000").End(xlUp).Offset(1).Select でエラーが生じます。
これは、「"b1000"」がまずいと思われます。
Excel君にとっては、全角、半角は別物です。
全角小文字のbという列は、定義をしていない限りありませんので、Excel君的には、なんのこっちゃ状態でそのようなエラーを返してきます。修正は、私が整理したもののように、半角文字で指定してあげてください。
質問の2つめ。
>請求書ですから項目名の内、摘要欄が17行あります。
>その他の項目は、一行だけです。
>摘要欄の1行目のデータがb91だとしましたらb107行迄続きます。
>そうしますと Sheets("請求書").Select
> Range("b91:ab107").Selectの範囲でいいのでしょうか?
お考えのとおりで良いかと思いますが、先のとおり、〇〇〇を選択して、選択しているものを×××する という記述ではなく、〇〇〇を×××するという記述をオススメします。
質問の3つめ
>回目に保存先に転記した後で、2回目に保存先に転記した場合には、1回目転記したデータ2行目の下3行目から転記されますか?
>(摘要欄以外の項目データは、1行分の為、2回目の転記の際には、2行目から貼り付けされるかと思います。摘要欄のデータは、17行の内16行分は、消えますか?)
状況(どのようなデータが貼り付けられているか)によります。
提示のコードで貼付先を決めているのはこの部分です。
Sheets("保存先").Range("B1000").End(xlUp).Offset(1, 0)
実際にどこに貼り付けられるかは、1回目のコピーを実行後に、手動で以下の操作をしてみてください。
(1)保存先シートの「B1000」を選択する。
(2)Ctrlキーを押しながら↑キーを押す。
(3)Ctrlキーを押さずに、1回↓キーを押す。
この操作で選択されたセルこそが2回目の貼付先(の起点)になります
貼り付けられたデータのB列に歯抜けがあれば、よろしくないことが起きるということが確認できるかと思います。
(もこな2) 2018/03/16(金) 10:48
>ひとつずつ解決されてはどうでしょうか。その方がお互いに解りやすく、回答もつき
やすいように思います。 ---次回からそのように致します。
>Selection.PasteSpecial の最後、行継続文字 アンダースコア _ が抜けています。
−−−コピーするときに見過ごさないように気をつけます。有難うございました。
もこな2様へ
わかりやすい説明を数々頂き光栄です。(最後に質問をしていますので、よろしくお願いいたします。)
順序としましては、
> 〇〇〇.Select
Selection.××× ↓ 〇〇〇.××× 大抵の場合は、上記のように〇〇〇を×××する。というように記述できます。 ---そのようにします。
>全角小文字のbという列は、定義をしていない限りありません---気づきませんでした。
>実際にどこに貼り付けられるかは、1回目のコピーを実行後に、手動で以下の操作をしてみてください。
(1)保存先シートの「B1000」を選択する。
(2)Ctrlキーを押しながら↑キーを押す。
(3)Ctrlキーを押さずに、1回↓キーを押す。
この操作で選択されたセルこそが2回目の貼付先(の起点)になります ---手動でしましたら問題なく
行けました。
それによって
私の当初の質問で、>摘要欄の1行目のデータがb91だとしましたらb107行迄続きます。
>そうしますと Sheets("請求書").Select Range("b91:ab107").Selectの範囲でいいのでしょうか?が
解決されました。(2回目のコピーも1回目のb107行の次から転記されました。)
ところが、摘要欄17行分をb91からb108迄入力しているから、
Sheets("保存先").Range("B1000").End(xlUp).Offset(1, 0).PasteSpecial _の
B1000の行と一致しているので2回目の貼り付けがうまくいき喜んでいましたが、
ふと思ったのですが、B列どうしだから合致するけどB列以外の列に複数行の文字が入った場合
は、どうなるかと思ってテスト的にB列は、文字は、1行だけ、C列に17行分文字を入れて
マクロを実行しましたら、シート名:"保存先"のB列には、1回目は、文字がB2に入りC2から
17行分貼り付けされました。
しかし2回目は、B3に文字が入りましたが(当たりまえですよね)C列は、C3から2回目の文字が
上書きされました。(1回目のC2の文字は残りC3からは2回目の文字が上書きされました。
質問:Sheets("請求書").Range("B91:AB91").Copy この範囲でCからAB列で複数の行での文字が、
入った場合の方法を教えて頂けますか?
よろしくお願いいたします。
今から会議に入りますので(2時間ほど)、急いでいただく必要は、ありませんので---
シート名:保存先への転記が上手くいきました。本当に嬉しいです。
(謙児) 2018/03/16(金) 13:46
VBA 最終列取得 なんてキーワードで検索するとよいとおもいます。
たとえばこんなサイトがヒットします。
https://tonari-it.com/excel-vba-row-column/
(もこな2) 2018/03/16(金) 15:03
上記サイトをかなりの時間かけて見ていますが、
説明の段階では、なるほどと思い見ていましたら
しかし、これは、こういうところは、いいが
こういうところに不都合がある。
違う行、列の検索でもこういうところはいいが、
こういうところが悪い。
やろうとしている事に応じた使い分けが必要です
と書いていますが、自分で調べて構文に入れよう
としたのですが、どこに何を入れていいのかわかりません。
折角ヒントを頂きながら申し訳ありませんが、
転記の際に、1回目以降、以前の保存が2行以上のデータがあるときは、
2回目以降の転記は、2行以上のデータで最後の行が終わった次の次の行から
(1回目と2回目の間を見やすくするために一行スペース空けます。という意味です。)
転記されますようにお願いします。
申し訳ありません。
よろしくお願いいたします。
お願いできますか?
<S3> 上方向に最終行を検索する
MaxRow = Range("A65536").End(xlUp).Row
MaxCol = Range("IV1").End(xlToLeft).Column ' IV1=256列
これはもっとも知られている方法でしょうか。VBAの参考書などでも例としてでているようです。
<S2>ではセルが連続していない場合は×でしたが、こちらは連続していなくてもちゃんと動作します。仕組みとしてはワークシートの最下端(65536行)から上に向かって空白でないセルを検索するというものです。これだと途中で空白セルがあっても問題ないですよね。
(謙児) 2018/03/16(金) 18:07
そうすることでフィルターやピボットテーブルの機能が使えるようになると思います。
(まっつわん) 2018/03/16(金) 18:25
S3> 上方向に最終行を検索する
MaxRow = Range("A65536").End(xlUp).Row
MaxCol = Range("IV1").End(xlToLeft).Column ' IV1=256列
これはもっとも知られている方法でしょうか。VBAの参考書などでも例としてでているようです。
<S2>ではセルが連続していない場合は×でしたが、こちらは連続していなくてもちゃんと動作します。仕組みとしてはワークシートの最下端(65536行)から上に向かって空白でないセルを検索するというものです。これだと途中で空白セルがあっても問題ないですよね。
(謙児) 2018/03/16(金) 18:07
お返事が、頂けないのは、転記の時に摘要は、
1行にせざるを得ないていうことでしょうか?
どなたでも結構です。お返事下さい。
(謙児) 2018/03/17(土) 06:41
>お返事が、頂けないのは、転記の時に摘要は、 >1行にせざるを得ないていうことでしょうか?
単にこのトピを見ていないだけだと思いますよ。
絶対的に「1行にせざるを得ない」と言う訳ではないですが、 その方があとあと楽になると言うことです。
「取出し易い」ように保存すると、あとあと効率よく再利用が出来ます。
>(1回目と2回目の間を見やすくするために一行スペース空けます。という意味です。) 「見易さ」を重視しているようですが、1レコードが17行もあったら、目的のレコードを 探すもの面倒になり、必ずしも見易いとは言えないです。
所謂「システム」に近い作りになって来ているので、骨格をチャンと考えるのも大事ですよ。
「見る」なんてことは、納品書、請求書、もしくは「閲覧シートを作って、そちら」の方でやればいいように思います。 そうすれば、取り出しやすいようにデータが(1行に)保存されている方が楽・・・だと思いませんか?
まぁ、最終的な判断は、謙児さんがすることですけどね。
あとは謙児さんが決めた方針に従って、具体化することになります。 1レコード1行方式じゃないケースでは、最終レコードの最終行が 何行目にあるか確実に認識できる必要があります。
ある時はB列が最下行、ある時はC列が最下行、 はたまたX列が最下行になると言うことだと、非常に面倒になります。
それが避けられない現実なら、それを受け入れて対処しなければなりませんが・・、 こちらは、最下行は必ずB列(摘要欄)なのだろうと読んでいたのですが、 急に歯抜けの状態もあり得る話になり(本当?※)、そうでもない雲行きになっているし、 1行一本化保存の話も勃発しているので、どうしたもんかなぁと思っているところです。
(本当?※) >ふと思ったのですが、B列どうしだから合致するけどB列以外の列に複数行の文字が入った場合 >は、どうなるかと思ってテスト的に・・・
「テスト的」なんですよね? 実際はB列だけが複数行になるんじゃないですか?
どうもよく分からないです。
(半平太) 2018/03/17(土) 07:55
おはようございます。 >お返事が、頂けないのは、転記の時に摘要は、 >1行にせざるを得ないていうことでしょうか? >どなたでも結構です。お返事下さい。
なんとなく悲壮感みたいなのは伝わってきて何とかお答えしようと 朝から繰り返しご質問内容を読み返しているんですけど、 正直、全然、わかりません。(^^;
>摘要欄の1行目のデータがb91だとしましたらb107行迄続きます。 >そうしますと Sheets("請求書").Select >Range("b91:ab107").Selectの範囲でいいのでしょうか?
なぜ?文脈の最後が でしょうか? なのがわかりませんが、 いいんじゃないんですか?
>それと1回目に保存先に転記した後で、2回目に保存先に転記した場合には、 >1回目転記したデータ2行目の下3行目から転記されますか? >(摘要欄以外の項目データは、1行分の為、2回目の転記の際には、2行目から貼り付けされるかと思います。 >摘要欄のデータは、17行の内16行分は、消えます か?)
ここもなぜ? 文脈の最後が >転記されますか? とか >消えます か? なのかがわかりませんが、 転記されるんですか? 消えるんですか?って こちらがお聞きしたいですよね????
>追加2、それとも1回目の転記データ、最大行の17行目の下18行目から、 >2回目の転記データすべてが、18行目から転記されますか?
ここもなぜ? >18行目から転記されますか? なんですか? 転記されるんですか?ってこちらがお聞きしたいですよね???
で、要するに、何かの範囲を何処かへコピーされたいんでしょ?
お返事がないのは多分、ご質問の内容が皆さんわからないんだと思いますよ?
いいや、わからないのは私だけだったりして( ̄▽ ̄;) 私の場合、軽いノリで入って実はとんでもない難件だったりしますからねぇ(笑)
>質問:Sheets("請求書").Range("B91:AB91").Copy >この範囲でCからAB列で複数の行での文字が、入った場合の方法を教えて頂けますか?
これも想像力をMaxにしますと、方法は色々ありますし、シートの構成にもよりますが、
一番、オオソドックスなのは、 Range("C91").CurrentRegion でしょう
次に、 Intersect(Range("C:AB"), Range("C91").CurrentRegion) かな?
でもこれだと 文字の入った範囲 複数行 があやふやなので、 Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")) かな?
ということで、解析しながら私なりにコードを書き直してみました。 どうでしょうか?
Option Explicit Sub jikkou() Application.ScreenUpdating = False With Sheets("請求書") Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Copy Sheets("保存先").Range("b" & Rows.Count).End(xlUp).Offset(1) _ .PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Application.CutCopyMode = False .Select .Range("a12").Select End With Application.ScreenUpdating = True End Sub
衝突しましたけど、そのままUpされてました。すみません。 v(=∩_∩=)v (SoulMan) 2018/03/17(土) 09:12
さっき、私もまっつわん様が言われるように扱いやすいと言われているので、
転記する際に、思い切って摘要欄を1行で17列分を
作ろうかと考えました。
>「見る」なんてことは、納品書、請求書、もしくは「閲覧シートを作って、そちら」の方でやれば
いいように思います。
そうすれば、取り出しやすいようにデータが(1行に)保存されている方が楽・・・だと思いませんか?
私自身で利用するのであれば、その通りです。しかし当該質問には記載していませんでしたが、(必要な
質問のみを記載した後で自分が他にやりたいことをつなげるつもりでした。ですからアドバイザーの方に
余分な事を書かない方が読みやすいと思っていました。)
と言いますのは、全体像ですが、
1、まず弊社の見積り、納品書、請求書等の発行は、各部署がフォームがバラバラでしたので、
統一するつもりでした。
2、どうせ作成するのなら皆さんが作りやすいようにと
主に下記8つのシートを作成しました。
一つ目は、入力説明(自動化の説明及び各マスターへの追加説明)
二つ目は、自動選択(フォームコントロールの図形を利用して、例えば、相手先を選び
クリックすると 相手先名(住所等)及び明細に、別シート、見積書に自動入力されます。
あるいは、下記各マスターを利用して書式設定、コントロールの書式設定とつなぎ
合わせます)
三つ目は、見積書(実際に印刷等されるシートです。)
四つ目は、転記(今回の質問)
五つ目は、支払先名、住所、摘要欄のマスター
(ここで摘要欄のデータを入れています、もちろん定期的なものだけで、違う内容は、
適宜、見積書の摘要欄に書いて頂きます。)
六つ目は、単価のマスター
七つ目は、部門のマスター
八つ目は、備考欄のマスター
以上です。
>「見る」なんてことは、納品書、請求書、もしくは「閲覧シートを作って、そちら」の方でやればいい
ように思います。 そうすれば、取り出しやすいようにデータが(1行に)保存されている方が楽・・・
だと思いませんか?
−−−私自身が作るのならそう思います。
「見る」、これも作る人の事を考えて思ったことです。
>「テスト的」なんですよね? 実際はB列だけが複数行になるんじゃないですか?
‐‐‐迷わせた言葉ですみません。今から会社に行かなければなりません。
会社に着きましたらここを再確認します。(私もテスト的ーーーで疑問に思う事がありました。)
今、この文書をアップしようとしましたら他の人と衝突とエラーになりました。
再度見ますとSoulMan様からのお返事です、ぎりぎりの時間で今から会社に行きます、
会社で見ますので申し訳ありません。
(謙児) 2018/03/17(土) 10:14
Backで永ちゃんがガンガン鳴っている様なロジックを書いてみたいね で、私風に書くと
Sub てすと() Dim MyA As Variant Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With Sheets("保存先").Range("b" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA Application.ScreenUpdating = True Erase MyA End Sub
と、指が勝手に動きます。ここまでくると病気?です。 ※今から、土筆を取りに行くのとお馬さんモードに入ります。
♪RockYou!何があった?ノリが悪いぜ! ♪最高な俺を見せてやるぅぜ! ♪お前だけに逢いに来た 今夜こそは腰をMoMoMoもっと振れよ! ♪Rock'n'Roll! 生きてるって感じたはずさ、、、 なんちゃって
乗ってね Rock'n'Roll!に感謝しようぜぇ〜〜
ガンガン鳴って、、、ないね(^^; v(=∩_∩=)v (SoulMan) 2018/03/17(土) 11:13
(隠居じーさん) 2018/03/17(土) 14:08
お返事有難うございました。
下記の件ですが、
それと1回目に保存先に転記した後で、2回目に保存先に転記した場合には、
>1回目転記したデータ2行目の下3行目から転記されますか? >(摘要欄以外の項目データは、1行分の為、2回目の転記の際には、2行目から貼り付けされるかと思います。 >摘要欄のデータは、17行の内16行分は、消えます か?)
> ここもなぜ? 文脈の最後が >転記されますか? とか >消えます か?
>なのかがわかりませんが、 転記されるんですか? 消えるんですか?って
>こちらがお聞きしたいですよね????
上記は,転記のマクロがうまくは走らなかった状態の質問状で確認をしました為です。
Sou Man様のコードは、1回目としてうまく行きましたが、2回目を実行しますと1回目のデータにかぶさっ
てしまいます。(1回目のデータ2行目からは消去されます)
改めて流れをまとめます。
ご教授いただきたいのは、下記 転記の手順:です。
配置:
請求書(日付、支払先、部門、摘要、他)の各項目名(位置がバラバラ)のデータを
同じシートのB91〜AB107に並べています。因みにB91の式は、=B5 他の列も同様です。
(一つのセル幅を2にしていますので、たくさんの列になっています。)
そのうち、実際には、3列分は、複数行使用しています。
内訳:B列:〒番号、住所(2行分使用)名前(2行分使用)計5行分
C列:納品場所、納期、本見積有効期限、お支払い条件 計4行分
D列:摘要、最大17行分
(D列のみ相手会社の明細によっては、17行未満になります。)
シート名"保存先"は、請求書発行済みのデータを1件、1件、並べて保管する為のものです。
転記の手順:
1、1回目、A社の請求書が作成、確定しましたら、(その時のデータは、B列 5行分、C列4行分、
D列17行とします。他は、1行です。)シート名:"請求書"のB91〜AB107内のデータを
シート名"保存先"B2へ貼り付けします。(貼付範囲は、B2:AB18)
2、次に2回目のB社の請求書が作成、確定しましたら、(その時のデータは、B列 5行分、C列4行分
D列6行とします。)シート名:"請求書"のB91〜AB107内のデータをシート名"保存先"B20か
らAB25に貼り付けたいのです。
以降、同様です。よってシート名:"請求書"のB91〜AB107内のデータを
シート名"保存先に転記するときは、シート名"保存先"で前回のB列からAB列内の最大行の2つ下の行から
B列からAB列を貼付けをしたいのです。
以上です。
上記のまとめが、やっとまとまりましたが、相手に理解して頂こうと文章を記載することは、
難しいですね。
どうかご理解していただくことを祈っています。
よろしくお願いいたします。
(謙児) 2018/03/17(土) 14:17
後です、確認をします、有難うございました。
(謙児) 2018/03/17(土) 14:35
(隠居じーさん) 2018/03/17(土) 14:42
隠居じーさんへ 土筆、ボコボコ生えてましたよ 大漁です トピ主さんごめんなさいね
えっ〜っと、今は、書けませんけど、保存先のどこに転記するか?ですよね
必ず、ある列があるならそらの列のoffset(1) か、 UsedRange.offset(1) で、いいような気がしますけど、ちょっと今出先なんですみせん
そう言うのって作成者の意図が重要ですよね
B2からB19へと決め打ちするのか? 後からゆっくりと見てみます
(SoulMan) 2018/03/17(土) 14:55
私のやり方が悪いのか、
実行しましたが、2回目を実行すると1回目のデータと重なりました。?
(謙児) 2018/03/17(土) 15:10
全然、悪くないですよ 一回目と何も変えてませんから、 今、ちょっとお馬さんモードなんで ちょっと待てて下さいね (SoulMan) 2018/03/17(土) 15:25
以下、大切なことを言い忘れていました。
皆様が、おっしゃる、シート名:"保存先"に1社について摘要欄を1行の横並びに17列で表した方が
何をするにしても便利といわれることは理解できているのですが、固守しました点は、次の通りです。
請求書の項目名で、摘要欄の右項目は、単価、数量、金額 がある事です。
摘要欄の明細行ごとに単価、数量、金額が定まっています。
そのために保存先には、請求書と同じフォームでする方がわかりやすいと思ったからです。
(シート名:"保存先"に摘要欄の複数件の転記が可能だったら、シート名:"保存先"の摘要欄の右列に単価、
数量、金額の項目を入れるつもりでした。)
気づくのが遅すぎて皆様に貴重な時間を取って頂き申し訳なく思っています。
(謙児) 2018/03/17(土) 16:09
お待たせしました。
>シート名"保存先に転記するときは、 >シート名"保存先"で前回のB列からAB列内の最大行の >2つ下の行からB列からAB列を貼付けをしたいのです。
2行 空けるんですね
Sub てすと() Dim MyA As Variant Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With With Sheets("保存先") .UsedRange.Offset(.UsedRange.Rows.Count + 2).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA End Sub
これでどうなりましたか? 駄目だったら、 ↓ここの + 2 を +3 とか +4 とかしてみて下さい。 .UsedRange.Offset(.UsedRange.Rows.Count + 2).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA v(=∩_∩=)v (SoulMan) 2018/03/17(土) 16:16
あっ、すみません。 B列が守られませんね? これで、最大行の +4 を変えてみて下さい。
Sub てすと() Dim MyA As Variant Dim 最大行 As Long Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With With Sheets("保存先") 最大行 = .UsedRange.Rows.Count + 4 .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA End Sub v(=∩_∩=)v (SoulMan) 2018/03/17(土) 17:07
すみません。 なんか今日は、今日も?ボロボロですね(;^_^A
でも、安心してください。スーパーサイヤ人級に想像力をMaxにしましたから、、、 もう、このトピも終わらせますから、、 お前は悟空か?
いいえ、I am a SoulMan なんちゃって もうええちゅうねん! そんな前置きはええから、早よ回答せんかえ! おっさん! はい!わかりました。
で、お散歩に行って考えていましたら、 最大行を一回目とそれ以降で変えないといけませんね?
ちゃんと Debugして イミディエイトWindowで確認にて 変数を調整すればいいんですよね? 指先に頼らないで基本!基本!
これで、どうでしょう?
Sub てすと() Dim MyA As Variant Dim 最大行 As Long Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With With Sheets("保存先") 最大行 = .UsedRange.Rows.Count '一回目の最大行 実情に合わせて +1 を調整してください If 最大行 < 2 Then 最大行 = .UsedRange.Rows.Count + 1 '二回目以降の最大行 実情に合わせて +4 を調整してください Else 最大行 = .UsedRange.Rows.Count + 4 End If .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA End Sub v(=∩_∩=)v (SoulMan) 2018/03/17(土) 18:36
こんばんは〜 ^^
左にひとつ ずれているような。。。かんじが しないでも。。。
気のせいでしょうか 違ってましたらご容赦下さいませ。
(隠居じーさん) 2018/03/17(土) 18:49
出来ましたことは、請求書からの保存先へ
転記が出来ましたことです。どんなに嬉しいことでしょうか?
かなわぬことがかなったのですから---有難うございます。
ただ2つありまして、
1、1回目は、シート名”請求書”から
シート名"保存先"に最大値17行目分(試しに数字1から17迄を入力しました。)が転記されました
が、最大行 = .UsedRange.Rows.Count + 4に替えると保存先、1行目から4行目迄がブランクになりま
す。
従って1行目は、見出しを手入力していますので転記は、2行目から実行しますので、Count + 4は、
2に替えています。
2、1回目の転記では、1から17迄入力されましたが、2回目の転記では、1回目の最終行17が2回目の
1に上書きされています。3回目は、2回目の最終行17が3回目の1に上書きされています。
最大行 = .UsedRange.Rows.Count + 2 は、2回目からいかされていないのでしょうか?
行いたいことは、2回目のデータが転記されるのは、1回目のデータの最後(18行目)次の19行目
は、空けます。
20行目から2回目のデータを転記します。ということです。
(>2つ下の行からB列からAB列を貼付けをしたいのです。 2行 空けるんですね---勘違いさせて
申し訳ございませんでした。)
なにとぞよろしくお願いいたします。
(謙児) 2018/03/17(土) 19:11
うぅぅん、駄目でしたかぁ、、、、 後は、↓ここの 3 と If 最大行 < 3 Then
↓この +1 と 最大行 = 最大行 + 1
↓この +3 を 最大行 = 最大行 + 3 を調整してみて下さい。
やっぱり、疲れているみたいです。( ̄▽ ̄;)
Sub てすと() Dim MyA As Variant Dim 最大行 As Long Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With With Sheets("保存先") 最大行 = .UsedRange.Rows.Count '一回目の最大行 実情に合わせて +1 を調整してください If 最大行 < 3 Then 最大行 = 最大行 + 1 '二回目以降の最大行 実情に合わせて +3 を調整してください Else 最大行 = 最大行 + 3 End If .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA End Sub v(=∩_∩=)v (SoulMan) 2018/03/17(土) 19:29
ちょっと休んだら体力が回復してきました。 Game Is Over です。 カウンターを仕込みましたので、初回だけ、はい をクリックしていただいて 後は、MsgBoxのアドレスを控えて調整してみてください。 いい結果が出ることを期待しています。 では、では、
Sub てすと() Dim MyA As Variant Dim 最大行 As Long Static k As Long If vbYes = MsgBox("カウンターをリセットしますか?", vbYesNo + vbDefaultButton2) Then k = 1 Else k = k + 1 End If Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With With Sheets("保存先") 最大行 = .UsedRange.Rows.Count '一回目の最大行 実情に合わせて +1 を調整してください If 最大行 < 3 Then 最大行 = 最大行 + 1 MsgBox k & " 回目は " & .Range("B" & 最大行).Address(0, 0) & " に張り付けます" '二回目以降の最大行 実情に合わせて +3 を調整してください Else 最大行 = 最大行 + 3 MsgBox k & " 回目は " & .Range("B" & 最大行).Address(0, 0) & " に張り付けす" End If .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA End Sub v(=∩_∩=)v (SoulMan) 2018/03/17(土) 23:46
おはようございます ^^ 作ってみました。多分また勘違いしているかもですが。外していましたら ご容赦下さい。突っ込み歓迎!^^;;;。。。勉強中!
Option Explicit '********************************************************** Sub main() Dim sh01 As Worksheet, sh02 As Worksheet, sh03 As Worksheet Dim i As Long, gyo As Long, buf, rr As Range, r As Range Set sh01 = ThisWorkbook.Worksheets("請求書") Set sh02 = Sheets("保存先") buf = sh01.Range("B91:AB107") gyo = sh02.Range("D" & sh02.Rows.Count).End(xlUp).Row + 1 If gyo > 2 Then If (gyo - 2) Mod 18 > 0 Then gyo = gyo - ((gyo - 2) Mod 18) + 18 End If End If sh02.Range("B" & gyo).Resize(UBound(buf, 1), UBound(buf, 2)) = buf End Sub (隠居じーさん) 2018/03/18(日) 08:21
なんか、私には理解不能な展開になっているなぁ。
こんなのでいいと思っているんですけども・・
Sub jikkou() Dim Rw As Long, RwNext As Long
Application.ScreenUpdating = False Sheets("請求書").Range("B91:AB107").Copy
With Sheets("保存先") Rw = .UsedRange.Find(What:="*", After:=.Range("B1"), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
RwNext = Rw + IIf(Rw = 1, 1, 2)
.Cells(RwNext, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End With
Application.CutCopyMode = False Sheets("請求書").Select Application.ScreenUpdating = True Range("A12").Select End Sub
(半平太) 2018/03/18(日) 08:30
早くご回答を得ているのに、お返事が遅くなり申し訳なく思っています。
今、この画面を覗きましたら隠居じー様からと半平太さまからご回答を頂き
有難うございました。後で確認をさせて頂きます。
SoulMan様
1回目の転記で、データ1〜17迄が貼付けされました。2回目のデータを転記すれば 前のように
17が消えずに 1からスタートして17迄貼付けされました。嬉しかったです。
ところが、最初の転記で、カウンターをリセットしますか? はい をクリックしますと
1回目は、B3に貼り付けます となりB3から貼り付けされます。(1行分空きたいところ2行分空いて
います。
続けて2回目を実行(カウンターをリセットしますか?いいえ)しますと1回目のデータ17の次に2行分
空いて3行目にデータの1が貼付けされます。?
わからないながらも構文4の数値を色々触って確認をしましたが、思うようにならず、どう変更をかけたら
良いかわかりません。
>最大行 = .UsedRange.Rows.Count
> '一回目の最大行 実情に合わせて +1 を調整してください
私の伝える判断が違っているかもしれませんが、'一回目の最大行 実情に合わせて +1 を調整してくだ
さい 。は、この作品が出来れば、他の人に使用してもらうのに使用者に構文を調整してもらうのは、
いささか問題ではないでしょうか?
SoulMan様の思いが違っていれば申し訳ありません。
以上、よろしくお願いいたします。
(謙児) 2018/03/18(日) 12:00
大丈夫ですよ 直ぐに終わりますから(笑)
すみませんが、 タイトル という名前のシートを一枚作って下さい。 そのタイトルというシートの一行目に タイトル を記入してください。
で、一回目は、必ずカウンターをリセットしてください。 2回目以降は、そのままでいいです。 では、では、
Sub てすと() Dim MyA As Variant Dim 最大行 As Long Static k As Long If vbYes = MsgBox("カウンターをリセットしますか?", vbYesNo + vbDefaultButton2) Then k = 1 Sheets("保存先").Cells.Clear Sheets("保存先").Rows(1).Value = Sheets("タイトル").Rows(1).Value Else k = k + 1 End If Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With With Sheets("保存先") 最大行 = .UsedRange.Rows.Count '一回目の最大行 実情に合わせて +1 を調整してください If 最大行 < 3 Then 最大行 = 最大行 + 1 MsgBox "ここは、1回目の最大行です" MsgBox k & " 回目は " & .Range("B" & 最大行).Address(0, 0) & " に張り付けます" '二回目以降の最大行 実情に合わせて +3 を調整してください Else 最大行 = 最大行 + 3 MsgBox "ここは、2回目以降の最大行です" MsgBox k & " 回目は " & .Range("B" & 最大行).Address(0, 0) & " に張り付けす" End If .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA End Sub v(=∩_∩=)v (SoulMan) 2018/03/18(日) 13:09
保存先の1行目は、別シートで作成されたタイトルの文字が入っています。
実行すれば2行目からデータの1から17が縦に貼り付けされています。
しかし2回目からの実行後は、最初のデータ数字1は、1回目の最後17の次に1行空くのではなく
2行空いて3行目からデータが入ります。
3回目も同じです。
何回もお手間を取らせてすみません。
(謙児) 2018/03/18(日) 13:42
もう少しです。
Sub てすと() Dim MyA As Variant Dim 最大行 As Long Static k As Long If vbYes = MsgBox("カウンターをリセットしますか?", vbYesNo + vbDefaultButton2) Then k = 1 Sheets("保存先").Cells.Clear Sheets("保存先").Rows(1).Value = Sheets("タイトル").Rows(1).Value Else k = k + 1 End If Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With With Sheets("保存先") 最大行 = .UsedRange.Rows.Count '一回目の最大行 実情に合わせて +1 を調整してください If 最大行 < 3 Then 最大行 = 最大行 + 1 MsgBox "ここは、1回目の最大行です" MsgBox k & " 回目は " & .Range("B" & 最大行).Address(0, 0) & " に張り付けます" '二回目以降の最大行 実情に合わせて +3 を調整してください Else 最大行 = 最大行 + 2 MsgBox "ここは、2回目以降の最大行です" MsgBox k & " 回目は " & .Range("B" & 最大行).Address(0, 0) & " に張り付けす" End If .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA End Sub v(=∩_∩=)v
(SoulMan) 2018/03/18(日) 13:49
お疲れ様でした。
上記、1行空けて次のデータが張り付けられました。
長く、一生懸命に尽くして頂き有難うございました。
結果上手く行きましたから、上記でお伝えしましたように
この表は、他の社員に使っていただくために、 MsgBox のボタンは、
はずして頂いて何回も転送を繰り返しても1行空けた結果になりますように
出来ませんか?
先ほど、MsgBox を’して試したところ2・3回転送しても2行目から貼り付けされます。
たぶんそれは、常に言われた1回目は、はい、クリックしてください、そこからxx回目として
データ行が次々下の行に貼り付けされると思うのですが?
如何でしょうか?
(謙児) 2018/03/18(日) 14:58
1件のデータのある大部分は1行にして摘要欄だけ17行使ってるんですよね?
すっごい中途半端じゃないですか?
この際なので僕なら、
シート上に
入力用シート
閲覧用(印刷も兼ねる?)シート
検索(あるいは抽出)シート
マスターデータ(非表示※ユーザーに触らせないため)シート
などを用意し、
他に、別ブックに1件1行でデータを蓄積するかな。
でも、全然VBA使ったことなかったら、簡単には作れないかもですね。
とにかく根気が必要です。
画面を作るのとどんな機能を持たせるかで5日
プログラムを書くのに5日
動作確認に5日
このシステムを作るのに集中してそれくらいかかるから、
プログラム書くのに一々調べていたら何カ月かかるのかなぁ。。。
バグにはまったら動作確認だけでくじけそうになりますし^^;
あ、気分で読んで、気分で書いてますので、
見ないときは1週間くらい見ないときもありますし、
30分おきに見てたりしてますので、そこはご了承願います。
仕事で張り付いているわけではなく、
趣味で口挟んでるだけなので悪しからず。
一人に負担をかけず、わかる人が書き込めるときに書いてもらうつもりで
お願いします。
(まっつわん) 2018/03/18(日) 15:09
お疲れさまでした。 思いのほか難産でしたね(;^_^A
初めて転記する時は、保存先 をクリアして タイトルは タイトル シートに書き込んでから実行してください。
多分、これでいいと思いますが。最後は、永ちゃんで閉めすか?
では、では、
Sub てすと() Dim MyA As Variant Dim 最大行 As Long Application.ScreenUpdating = False With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With With Sheets("保存先") 最大行 = .UsedRange.Rows.Count If 最大行 < 2 Then 最大行 = 最大行 + 1 .Cells.Clear .Rows(1).Value = Sheets("タイトル").Rows(1).Value Else 最大行 = 最大行 + 2 End If .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA End Sub
♪RockYou!!! こんなもんかい? お前のレヴェル!!!!
♪Maxに まだ、まだ、まだ、まだ、まだ、まだ、 いってなぁ〜いぜ!!!!!
♪お前だけに逢いに来た!!!!
♪今夜こそは声を MoMoMo もっと来いよ!!!!
♪Rock'n'Roll! 言葉なんかいらねぇだろ???、、、
♪Rock'n'Roll!に感謝しようぜぇ〜〜!!!!!!!!!
♪ありがとう!!!!!
ぶっ飛ぶ準備は、できたかい???? v(=∩_∩=)v (SoulMan) 2018/03/18(日) 16:00
>一人に負担をかけず、わかる人が書き込めるときに書いてもらうつもりで
> お願いします。
その通りだと思います。注意していきます。
SoulMan様、
疲れさせて申し訳なく思っています。
私の気持ちは、当該コメントは、これで終わりにしたいと思っています。が
2018/03/18(日) 16:00記載のコードを実行しましたが、保存先には、データが転記されていませんでした。
(保存先は、ブランクです。)
初めて転記する時は、保存先 をクリアして
タイトルは タイトル シートに書き込んでから実行しました。
(謙児) 2018/03/18(日) 16:43
これでどうなりますか? Sub てすと() Dim MyA As Variant Dim 最大行 As Long Dim i As Long Dim j As Long Dim MyFlg As Boolean With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value End With For i = LBound(MyA, 1) To UBound(MyA, 1) For j = LBound(MyA, 2) To UBound(MyA, 2) If MyA(i, j) <> "" Then MyFlg = True Next Next If MyFlg = False Then MsgBox " 請求書にデータがありません。" & vbCrLf & vbCrLf & _ "請求書にデータを入力してから実行してください" Erase MyA Exit Sub End If Application.ScreenUpdating = False With Sheets("保存先") 最大行 = .UsedRange.Rows.Count If 最大行 < 2 Then 最大行 = 最大行 + 1 .Cells.Clear .Rows(1).Value = Sheets("タイトル").Rows(1).Value Else 最大行 = 最大行 + 2 End If .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Erase MyA MsgBox "処理が完了しました" End Sub v(=∩_∩=)v (SoulMan) 2018/03/18(日) 17:09
1回目と2回目以降、回を増やすことに1行ずつ空いていました。
本当に長い間、有難うございました。
教えて頂きたいのは、
1、下記コードは便利だと思いますので利用したいのですが、91:107行のデータの
一部を消去してコードを実行しましたが、請求書にデータを入力してから実行してくださいとメッセージは、
出ないのですが?
2、それと転記のコードを試行錯誤している間に、気づいたのですが、
保存先にデータを転記するときに、コードの最後に MsgBox "請求書のデータを保存先 " 01 " 番目に
記録しました。というメッセージと それによって、シート名:"保存先"のA列に"01"番の表示を
表すことが出来ますか?
以上よろしくお願いいたします。 転記の質問は、これをもちまして終えます。
この転記が終えれば、実際に他の見積書、納品書等に転記するコードを作成して、マスター他
作業がスムーズに行くように手順を踏んでアイデアを考え進んで行きます。
もしその時に質問があれば新規の質問をさせて頂きます。
どうぞよろしくお願いいたします。
>If MyFlg = False Then
MsgBox " 請求書にデータがありません。" & vbCrLf & vbCrLf & _ "請求書にデータを入力してから実行してください" Erase MyA Exit Sub >End If
(謙児) 2018/03/18(日) 19:07
>If MyFlg = False Then
MsgBox " 請求書にデータがありません。" & vbCrLf & vbCrLf & _ "請求書にデータを入力してから実行してください" Erase MyA Exit Sub >End If
(謙児) 2018/03/18(日) 19:40
すみません。 なんか自分でもいつも?の切れ?がないのが実感できます。かなり疲れてます。_| ̄|○
まぁ、わたしゃ、大体、何時もこんなもんですけどね( ̄▽ ̄;)
>一部を消去してコードを実行しましたが、 >請求書にデータを入力してから実行してくださいとメッセージは、 出ないのですが?
一部を消したぐらでは出ません。何も転記されないとおっしゃたのでデータの有無を調べただけす。
一応、請求書のC91からF100にデータがない場合に警告を出す様にしました。
>保存先にデータを転記するときに、コードの最後に MsgBox "請求書のデータを保存先 " 01 " 番目に記録しました。 >というメッセージと >それによって、シート名:"保存先"のA列に"01"番の表示を表すことが出来ますか?
後、番号?も出す様にしておきました。
もう少し検証した方がいいかもしれませんし、何か他に手がありそうな気もしますが、指が動きません。(;^_^A
これで、HappyEnd になるといいですね(笑)
注意)明日からしばらくパソコンがなくなるので回答できません。
では、では、
Sub てすと() Dim MyTbl As Range Dim MyA As Variant Dim 最大行 As Long Dim i As Long Dim j As Long Dim k As Long Dim MyFlg As Boolean With Sheets("請求書") MyA = Intersect(.Range("C:AB"), .UsedRange, .Rows("91:107")).Value 'ここの範囲はお好みに合わせて下さい Set MyTbl = .Range("C91:F100") End With For i = LBound(MyA, 1) To UBound(MyA, 1) For j = LBound(MyA, 2) To UBound(MyA, 2) If MyA(i, j) <> "" Then MyFlg = True Next Next If MyFlg = False Then MsgBox "請求書にデータがありません。" & vbCrLf & vbCrLf & _ "請求書にデータを入力してから実行してください。" Set MyTbl = Nothing Erase MyA Exit Sub End If If Application.CountA(MyTbl) = 0 Then MsgBox "請求の " & MyTbl.Address(0, 0) & " に、 データがありません。" & vbCrLf & vbCrLf & _ "請求書の " & MyTbl.Address(0, 0) & " にデータを入力してから実行してください。" Set MyTbl = Nothing Erase MyA Exit Sub End If Application.ScreenUpdating = False With Sheets("保存先") 最大行 = .UsedRange.Rows.Count If 最大行 < 2 Then 最大行 = 最大行 + 1 .Cells.Clear .Rows(1).Value = Sheets("タイトル").Rows(1).Value k = k + 1 .Range("A" & 最大行).Value = Format(k, "0# 番") Else k = 1 .Range("A2").Value = Format(k, "0# 番") 最大行 = 最大行 + 2 For i = 2 To 最大行 If Application.CountA(.Rows(i)) = 0 Then k = k + 1 .Range("A" & i + 1).Value = Format(k, "0# 番") End If Next End If .Range("B" & 最大行).Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA End With Application.ScreenUpdating = True Set MyTbl = Nothing Erase MyA MsgBox "請求書のデータを保存先 " & Format(k, "0# 番") & " に保存しました。" End Sub
SoulMan 危うし、、、 いつも元気な SoulMan が今回に限って元気をなくすことがあるだろうか? いや、ないだろう、、、 と、思わず古典の反語が出てしまいましたね(笑) 落ちは、、大黒かぁ???? v(=∩_∩=)v (SoulMan) 2018/03/18(日) 21:09
(謙児) 2018/03/18(日) 21:18
今日からしばらくパソコンが使えないとおっしゃっていましたので、
このコメントは、しばらく見れないかもしれませんが、
これで、HappyEnd になりました。嬉しい限りです。
有難うございました。私の為に貴重な時間と労力を費やして頂きすみませんでした。
と同時に有難うございました。
どうぞご自愛くださいませ。
他の方にも、色々ご親切に教えて頂き嬉しく思っています。
有難うございました。
私が今後気をつけないといけないメッセージは、ありがたく受け取り注意していきます。
(謙児) 2018/03/19(月) 12:41
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.