エクセルvba 一つ上の行を指定した回数分コピーする。

教えて下さい。vba初心者です。

A B C D E F G H
7 522 加藤 沖縄 みかん 縄 1 1/5 空欄
8 123 吉田 愛媛 りんご 水 3 2/10 空欄

1️⃣一番最後に入力した行をすぐ下にコピーしたい。（ここでは7行目…入力して行くと行は増えます。）
2️⃣指定回数はE列。

この列ではE列が3回になりますので、
コピーしたい範囲はA〜G列を、すぐ下の8行〜9行までの2回分コピーしたいです。

完成希望例
A B C D E F G H
7 522 加藤 沖縄 みかん 縄 1 1/5 空欄
8 123 吉田 愛媛 りんご 水 3 2/10 空欄
9 123 吉田 愛媛 りんご 水 3 2/10 空欄
10 123 吉田 愛媛 りんご 水 3 2/10 空欄

にしたいです。

質問者からの補足コメント

• 2️⃣の訂正です。
  指定回数はF列です。
  
  1番上のABCはエクセルの列番号
  左端の7と8はエクセルの行番号です。
  初めて質問した為、見にくくなってしまい、すみません。

    補足日時：2017/03/13 01:57

• 11行目からも同様に異なる新たなデータを入力後、
  Ｆ列の回数分コピーしたいです。
  
  データをどんどん追加して行きたいのです。

    補足日時：2017/03/13 23:25

• 画像を補足させて頂きました。
  ご指導お願いします。

  
    補足日時：2017/03/14 00:06

• yokomaya様
  画像です。

  
    補足日時：2017/03/15 00:24

• ymkomayaさま

  
    補足日時：2017/03/15 00:25

• ymkomayaさま

  
    補足日時：2017/03/15 00:26

• ymkomayaさま
  こちらがエラーの画像です。

  
    補足日時：2017/03/15 00:28

• ymkomayaさま
  
  先程のR列の画像です。
  17列〜21列にしたいです。
  ご不便お掛けしますが、ご教授お願いします。

  
    補足日時：2017/03/15 23:02

• 出来ました！うれしいです(喜）
  添付方法を勉強しておりました。
  今回教えていただいたプログラムをコピーして、貼り付けてみました。
  TABキーでインデントを何度か入れたりしながら、作動するように頑張ってるのですが、なかなか赤文字エラーが取れないです。
  どこでＴＡＢを入れたらよいのか教えていただけますでしょうか？
  
  あと２回で補足ができなくなってしまうと…yokomayaさまに繋がらなくなってしまうのが不安です。
  こちらの他にあと２つお聞きしたい事が残っていまして…
  ①管理表のＡ１部分に検索をかけたいです。Ａ７以降に入力した番号をＡ１に入力すると（例　Ａ１に123と入力後、Ａ７に登録した123と合致するデータを全て呼び出したいです。
  ②前回教授頂いた条件検索の横に番号Ａ列とＦ列の２個の条件に一致するデータを取り出す関数も教えてください。（管理表Ｆ列でコピー回数指定の後、上書き直してます。）

  
  No.18の回答に寄せられた補足コメントです。 補足日時：2017/03/16 15:49

• 最後の補足になってしまいました。
  今まで本当にお世話になり、感謝申し上げます。
  これからもよろしくお願いします<m(__)m>

  
    補足日時：2017/03/16 21:43

No.24 ベストアンサー 回答者： yokomaya 回答日時： 2017/03/17 07:41

前の絵のリスト範囲の所、名前定義も使えますよ。

というか、数式を入れるところの右のボタンみたいのを
押すと、入力する代わりにマウスで別シートでも
ドラッグで範囲指定できるから、範囲指定とぴったり
合致すれば、勝手に名前に変換されるみたいだった。
名前で指定の時、先頭に＝はつけないみたいです。

ボタンはどっちをお使いですか？
呼び出している　subの名前で判ります。
Private Sub CommandButton1_Click()
と決まっているのがActiveXの方です。

この回答へのお礼

ありがとうございました。
名前の定義を使ったオートフィルターの詳細設定がどうしてもわかりませんでした。
今日の質問①
データが増えるので名前の定義をしたんですが、
定義の設定する場所はオートフィルターをかける
リストの範囲の設定方法がわかりませんでした<m(__)m>
この場合の名前の定義の方法とフィルター詳細設定（名前の定義を使用した）の方法をご教授お願いします。
今日の質問②と③もお願いします。

起動ボタンについては
ＳＵＢの名前を調べてみたんですが・・・マクロを見ても
何が書いてあるのか理解できず・・・
取りあえず、作っているボタンの方法ですが、
開発リボン➡挿入➡コントロールフォームで選んでいます。
次に右クリックでマクロの登録をしていますm(__)m

画像を添付したいので新しい質問を立ち上げます。
題名は同じですので、見ていただけますか？
今後ともよろしくお願いします。

お礼日時：2017/03/17 15:35

No.23 回答者： yokomaya 回答日時： 2017/03/16 23:16

元のシートと表示シートが違うんだね。

フィルターの詳細設定にうってつけの
状態だから（使ったことあるかな？）
もしなかったら画像を一度試してみて。
こっちでやるのもアリかなという気もして。

オートフィルターの場合マクロは
（ActiveSheetはWorksheets("Sheet1”）に置き換えてもいいです）
　　If ActiveSheet.AutoFilterMode Then　　’フィルターの▼が表示されているか
If ActiveSheet.AutoFilter.FilterMode Then
MsgBox "絞り込まれています"
Else
MsgBox "絞り込まれていません"
End If
End If
ってことらしいから、これを使えばオートフィルターで処理できると思うよ。

あとフィルタのマクロ記録は試した？

ボタンは貼り付けるところフォームとActiveXの
２種類あったはずなんだけど、どっち使ってるかな。
フォームは昔の形式で多分今はActivveXが
推奨されてると思うんだけど、ちょっと使い方が
異なるので。

この回答へのお礼

ありがとうございました。オートフィルターにこんな使い方があったとは…！

教えてくださいっ<m(__)m>
リストの範囲ですが、添付資料の通りですと、参照が正しくありませんとなってしまいます。
試しにＧ列の範囲をＧ15までと設定したら、表示が可能でした。
これからもデータがたくさん増えると思われるのです。
名前の定義を使いたかったのですが、定義の設定が出来ない状態でした。

お礼日時：2017/03/17 00:26

No.22 回答者： yokomaya 回答日時： 2017/03/16 21:13

補則の追加見つけました。

どこにあるかわからなくて


＞=VLOOKUP(K17&L171,回数,15,FALSE)　
なんだろこれ？
回数ってのが何か判らないけど
１７行目と１７１行目？

Lも17か、ふむふむ。
VLOOKUPは領域の左端列を探すから
そこの列に、この二つを接続した文字列があるならだけど・・・。
もひとつ意図がつかめないなあ。

ＶLOOKUPみたいなことを二つの列を
対象に両方合致したらみたいなことかな？

No.21 回答者： yokomaya 回答日時： 2017/03/16 20:24

＞①管理表のＡ１部分に検索をかけたいです。

Ａ７以降に入力した番号をＡ１に入力すると（例　Ａ１に123と入力後、Ａ７に登録した123と合致するデータを全て呼び出したいです。
フィルターとどう違うイメージでしょうか？
表内のセルを選択して
データ→フィルタ
A列の▼ですべてのチェックを外し
１２３だけチェック入れてOKした時と
どう違うイメージか教えてください。

②前回教授頂いた条件検索の横に番号Ａ列とＦ列の２個の条件に一致するデータを取り出す関数も教えてください。（管理表Ｆ列でコピー回数指定の後、上書き直してます。）
こっち、ごめんなさい。何の話か判らないです。
前回って？この質問？

ここまで書きかけて用があって出てました。
新規に質問立ち上げていただいてもついていきますよ。

この回答へのお礼

ありがとうございます。今後もご教授お願いします<m(__)m>
①はイメージはオートフィルターと同じです。
Ａ１に番号を入力して、隣に検索ボタンを作りたいというイメージです。
私のレベルはマクロまでで（汗）ボタンも作れます(汗）
フィルターのチェックを外したりする手間を省略出来るものならしたいのです。（A列の番号検索は毎日多用することになるのです。）
②２か所条件に一致するデータを表示させたいです。

今からイメージをツールで補足に送ります(*^-^*)
ツールの使い方マスターしました(喜）
また知識が増えてうれしいです。ありがとうございました。

お礼日時：2017/03/16 21:36

No.20 回答者： yokomaya 回答日時： 2017/03/16 16:27

ごめんなさい。

貼り付けてからコメント打ったので
そこは注釈つけちゃいけない構文の途中でした。
アポストロフィ以降を消すか
上の行に移動願います。
ってか、貼りなおしますね。

ElseIf Target.Column = Columns("Q").Column Then

For Each cl In Me.UsedRange.Columns(1).Cells 'A列の個々のセルでTargetの行以外を見て
If cl.Row <> Target.Row And _
cl.Text = Target.Offset(0, -16).Text Then 'Targetの行と文字が一致したら
Application.EnableEvents = False 'イヴェント発生停止
cl.Offset(0, 16) = Target.Text 'その行のQ列のセルに
Application.EnableEvents = True
End If
Next

この回答へのお礼

ありがとうございますm(__)m
出来ました。
vbaが出来ると、作業時間が大幅に削減出来ますね。私もvbaを使いこなしてみたいです。

次々に大変恐縮ですが、今日の補足の質問①と②のご教授お願いしますm(__)m

お礼日時：2017/03/16 16:57

No.19 回答者： yokomaya 回答日時： 2017/03/16 15:41

＞今回回答頂いた、式をコピーしてみましたが、進めませんでした（涙）

プログラムないしはコードっていいます。
等号、不等号は条件判断や代入に使ってますから
部分的には式ともいえますが、全体では式とはいわないです。

で、そのコードが動かない場合、メッセージやエラー番号が出るなら
必ずもれなくお伝え願います。
色が変わったり、カーソルが止まったりしてコードのうちの
エラー発生個所が判るなら、モアベターです。

この回答へのお礼

ありがとうございます。
わかりました。
早速ですが…
添付画面のand の後ろにコンパイルエラー:不正な文字です。とコメントが出てしまいました。

お礼日時：2017/03/16 16:16

No.18 回答者： yokomaya 回答日時： 2017/03/16 07:19

ElseIf Target.Column = Columns("Q").Column Then

For Each cl In Me.UsedRange.Columns(1).Cells
If cl.Row <> Target.Row And _　　’A列の個々のセルでTargetの行以外を見て
cl.Text = Target.Offset(0, -16).Text Then　’Targetの行と文字が一致したら
Application.EnableEvents = False 'イヴェント発生停止
cl.Offset(0, 16) = Target.Text　　　’その行のQ列のセルに
Application.EnableEvents = True
End If
Next

以上をNO7の下方にENDIFが２行ある間に
挿入してください。

※この掲示板はインデント（行頭の空白）が消されます。
僕の所では、行頭に添付の様に空白を入れてます。
これはとても大事なのでそちらでもTABキーで
インデントを挿入するように願います。

※おすすめの教科書はグーグル先生です。
昔は書籍を読みましたが、検索の利便の前には
ページをめくる作業が色褪せます。
よく目にするのはTANAKAさんのページかな。
網羅度がハンパないです。
それもあってヒット率が高い所為もあると思います。

※No15のSnippingToolの件
＞色々試して見ましたが対応してないタイプで、
Windowsは何ですか？７以降ならあると思うのですが。
Windows＋’R’でファイル名を指定して実行は出ませんか？
どこで進めないか教えてください。

この回答へのお礼

いつも大変お世話になり、ありがとうございます。

昨日まで画面の旗とRをしたり、エクスプローラーから検索かけたり・・しながら探していました。過去のメールを再度読み直ししていたら、ウィンドウズマーク＋Rでとあり、試してみました。こちらの方も何度もご教授下さり、ありがとうございます。
いつもご親切にご教授下さり、感謝の気持ちでいっぱいです。私一人ではここまで進めることができませんでした。

今回回答頂いた、式をコピーしてみましたが、進めませんでした（涙）
早速教えていただいたsnippingtoolを使って送信してみようと思います。

お礼日時：2017/03/16 15:25

No.17 回答者： yokomaya 回答日時： 2017/03/15 21:43

＞A列の番号123の時（重複してる番号あります。

）R列に格納容器番号を1列分に入力した時に、同じコード123の入ったデータの行のR列に格納容器番号を自動でコピー出来る方法はありますでしょうか？

基本、頭で、整然とルール化出来ることは、出来ないことはほぼないです。
逆に動作仕様が固まってないことはプログラムにしようがないけど。

ちょっとその前にエラーがどうだったのか、ご説明頂いても
いいですか？何かもやもやします。

＞R列に格納容器番号を1列分に入力した時に、
で、ここんとこ意味が判らないです。
R列に１列分？上から下まで？なわけないですよね。
逆に１行分でもおかしい。
そしたら１セルだけしかないから
行分て表現ありえないと思うし・・・・。

それとこれは必ずオートマティックにやっていい話？
ボタンとかで起動が希望？

R列まであるんだ、G列で起動しちゃったけっどよかったのかな？
G列変更時の７が、G列の番号だから変更必要ならご随意に。

この回答へのお礼

エラーの件の詳細から報告するべきでした。ご無礼をお許し下さい。
検索する枠が一つに対して、vlookup関数を使う場所が5個ありました。
名前の定義を一箇所にしか設定しておらず、関数を入れる部分の度に名前の定義をしてから関数を入力したら、エラーが消えました。
オフセット関数の初歩的な所で引っかかっていました。
ありがとうございました。

アクセサリーで画面を添付したかったのですが、色々試して見ましたが対応してないタイプで、写真画像でご不便お掛けして恐縮です。

前回教えて頂いたG列までのコピーで大丈夫です。ありがとうございました。受注日G列までオートマチックで、HからQまでは個別で入力が必要な箇所です。
ただ格納容器はA列の番号毎の容器になるので、毎回A列とRは同じにしたいのです。出来ればオートマチックを希望していますので、ご教授お願いします。

今後、もっとvbaを勉強して行きたいと思うのでが、おすすめの教科書などありましたら、こちらも教えた頂けますでしょうか？

お礼日時：2017/03/15 22:47

No.16 回答者： yokomaya 回答日時： 2017/03/15 02:27

＞数式にfalseを足しましたが、やはりエラーになってしまいました（泣）

見てないのでそれだけじゃいくらなんでも判らないですよ。
具体的にエラーが出る時、出ない時何が違うんですか？
i16に該当する行は管理表の何行目ですか？

ちょっと気になるのが名前定義で
OFFSET(管理表!A7,0,0,COUNTA(A:A)-1,18)
はあなたの式をそのまま使っただけなんですが
領域の縦の数はCOUNTA(A:A)-1でA列で数えるとしているのに
A30が空欄なのはどういうことですか？

この回答へのお礼

ありがとうございます。
計算式に一つづつ名前定義して、関数入れたら出来ました。
ここまで進められて、感謝の気持ちでいっぱいです。

別件でもう一つお願いしたいのです。
A列の番号123の時（重複してる番号あります。）R列に格納容器番号を1列分に入力した時に、同じコード123の入ったデータの行のR列に格納容器番号を自動でコピー出来る方法はありますでしょうか？

お礼日時：2017/03/15 21:19

No.15 回答者： yokomaya 回答日時： 2017/03/15 01:10

VLOOKUP(第４パラメータ要るのかな、多分）

=VLOOKUP(I16,管理表領域,2,FALSE)
でいけると思います。

SnippingTool
WINDOWSでスタートボタンからプログラムを選ぶ場所があって
（もしWindows8なら、ないです）そこにWindowsアクセサリっていう
フォルダみたいのがありその中って事ですが。

Win7以降なら、Windowsキー（旗みたいなやつ）＋Rを押して
そこに下記を打てば出ると思います。
%windir%\system32\SnippingTool.exe

この回答へのお礼

ありがとうございます。
ありがとうございます。
数式にfalseを足しましたが、やはりエラーになってしまいました（泣）
他に方法はありますでしょうか？

お礼日時：2017/03/15 01:39