Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Pro : Microsoft 365)
コピーペースト
投稿日時: 22/09/02 23:00:28
投稿者: hidey_g

シート1に以下のように設定してあります。
 
  A    B   C   D   E   F
1 番号  東京  大阪  名古屋  福岡  沖縄
2 1
3 2
4 3
5 4
6 5
7 6
 
 
シート2には以下のように設定する予定です。
 
  A    B   C   D   E   F
1 番号  東京  大阪  名古屋  福岡  沖縄
2 5   10   20   20   30   50 
 
ここで、マクロを実行すると、シート2に入力されたデータが、シート1の同じ番号のところに値複写したいのです。
 
実行後のシート1は以下の通り。
 
  A    B   C   D   E   F
1 番号  東京  大阪  名古屋  福岡  沖縄
2 1
3 2
4 3
5 4
6 5   10   20   20   30   50
 
シート2はデータの入力フォームで、データの入力は番号順ではなくランダムの予定です。
また、同じ番号のデータを入力した場合は重複せず、後で入力したものが優先となります。
単純にコピーペーストではなく、ペーストする場所がある特定のデータによって場所が変わるところがよくわかりません。
入力するデータが順であれば、何となく考えられるのですが。。。。
 
まだマクロの作成の初心者で、もし教えていただいてもきちんと活用できるかはわかりませんが、ぜひご教授ください。

回答
投稿日時: 22/09/02 23:07:52
投稿者: WinArrow
投稿者のウェブサイトに移動

一般機能で
VLOOKUP関数を使えば、対応できますが、
どうしてもVBAでなければいけませんか?

回答
投稿日時: 22/09/02 23:12:17
投稿者: WinArrow
投稿者のウェブサイトに移動

>同じ番号のデータを入力した場合は重複せず、後で入力したものが優先となります。
この説明は、矛盾していませんか?
 
重複せず
ということは、「後で入力〜〜」という話は成り立たないような気がします。

回答
投稿日時: 22/09/03 07:02:54
投稿者: simple

横入失礼します。
 
コードは基本的に次のようなものが骨子になるでしょう。

Sub test()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim fromR As Long
    Dim toR As Long
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    fromR = 2
    toR = Application.Match(ws2.Cells(fromR, "A"), ws1.Columns("A"), 0)
    If IsError(toR) Then
        MsgBox "該当行がありません"
    Else
        ws2.Cells(fromR, "A").Resize(1, 6).Copy ws1.Cells(toR, "A")
    End If
End Sub
(繰り返しも考えて、あえて、Sheet2のデータがある行は変数にしています。)
 
ただし、重要なのはどのような仕組みにするかの検討です。
(1)
Sheet2には入力したデータをすべて残しておくべきです。
その都度コピーするので、一行だけ使えばよいなどと考えていると後悔することになります。
というのは、
入力が二回あり、前のものはSheet1にコピーしたあと、2回目データで上書きしてしまいましたが、
実は前のものが正しかった、などということはあり得るでしょう。
そのためにも、データは残しておいて、あとでトレースして処理が間違いないことを
他人にも説明できる仕組みにしておく必要があるでしょう。(トレーサビリティなどといいますかね)。
 
(2)
まとめて入力して、転記は一回行えばよいのでは?
(そのほうが転記漏れなどが起きることも防げるはずです。)
上から順次転記していけば、A列が同じものがあったとしても、それは自動的に
Sheet1上で上書きされ、後から来たものが生き残るはずです。
 
上記の点を考慮して検討してみてはいかがですか?
あと気を使うべきところは、Sheet1に無いデータが入ってきたときのエラー対応です。
コードでは注意喚起だけにしていますが、
Sheet1の続きに追記していくとか、色々と方策はあるでしょう。
応用ですからそちらで検討してください。

投稿日時: 22/09/03 07:31:51
投稿者: hidey_g

すいません。言葉足らずで上手く伝えられていませんでした。
想定としてはこういうケースを考えております。
 
番号は社員コードを想定しています。
各社員が、入力フォームから自分の社員コードを入力し、それぞれの売り上げを入力するようなことを考えています。
つまり、
 @新規入力は、社員コードと売上数量の入力
 A修正入力は、別シートで社員コードを入力すると既存のデータが表示され、そのフォームで修正➡保存するとシート1の一覧表に重ね書きされる。(後から入力されたデータが優先というのはこういうことです)
 
他人が他人のコードを使って入力できてしまうと言う事は、ありますが、まずはそこは考えていません。
 
なお、Lookup関数での入力だと、シート1で一覧表として反映し続けることができなさそうなので諦めました。(番号1を入力して一覧に反映、番号2を入力すると2の部分は反映するが、1の部分は消えてしまうということです)
 
申し訳ありません。

投稿日時: 22/09/03 07:42:05
投稿者: hidey_g

WinArrow 様
 人に元のを伝えることの難しさを痛感しております。
 
Lookup関数を利用できないことは前述のとおりです。管理者として社員の売り上げ管理を当人に入力させ、それを一覧表と言う形で管理したいという考えからのものです。入力者が複数存在すると言う事がポイントになります。人数が多くなった場合、その入力を一人でするのは正直大変なので、個人で入力してもらえたらと考えたのです。
 
重複に関しても前述のとおりです。
・入力も修正も同じフォームを使うと仮定します
 ・新規入力は、そのままデータを入力し保存すると一覧にデータがコピペされる。
 ・修正入力は、「呼び出し」ボタンなどを設定しそれを押すと一覧(シート1)から当該データがコピペされて表示されるので、そのまま修正し保存する(ここでデータは上書きされる)
 
単純にデータを一覧表の番号に関係なく下の行に追加していくことはできそうですが、そうなると同じ番号の重複データができてしまいます。ですので、重複データに関しては後から入力したものが優先と考えました。
 
いつも、適切なアドバイスをいただける貴殿のコメント、感謝します。
もう一度やりたいことを見直して考えてみたいと思います。

投稿日時: 22/09/03 07:46:25
投稿者: hidey_g

simple 様
 
早速のコード、ありがとうございます。
しかしながら、職場でしか確認できませんので月曜日に職場にて一度参考にさせていただきながら勉強させていただきたいと思います。
 
ご指摘いただいて点に尽きましては、熟慮していきたいと思います。
運用できればいいなとは思うのですが、操作になれていない社員も存在します。データのバックアップなども含めて、考えていかなくてはいけないことは多いようです。
 
もう一度、システム内容の精選と不測の事態等に対する対応を含めて考えていきたいと思います。
 
引き続き、よいお考えがありました、ご教授いただけたら幸いです。

回答
投稿日時: 22/09/03 13:59:29
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
なお、Lookup関数での入力だと、シート1で一覧表として反映し続けることができなさそうなので諦めました。(番号1を入力して一覧に反映、番号2を入力すると2の部分は反映するが、1の部分は消えてしまうということです)

 
なにか、勘違いしていませんか?
 
Lookup関数ではなくて、VLookup関数です。
 
VLookup関数を入力するセルは、
シート1のセルB2〜F2 ですよ。

投稿日時: 22/09/03 17:00:43
投稿者: hidey_g

WinArrow さんの引用:
引用:
なお、Lookup関数での入力だと、シート1で一覧表として反映し続けることができなさそうなので諦めました。(番号1を入力して一覧に反映、番号2を入力すると2の部分は反映するが、1の部分は消えてしまうということです)

 
なにか、勘違いしていませんか?
 
Lookup関数ではなくて、VLookup関数です。
 
VLookup関数を入力するセルは、
シート1のセルB2〜F2 ですよ。

 
再びすいません。
VLOOKUP関数の事を省略してLookupと書いていましたすいません。
 
さて、再度ご教授願いたいのですが、
 
例を出させていただきます。
 
例えばシート2でbPの人が、情報を入力します。すると、シート1のbPのところにデータは反映されます。
その後、このブックを保存します(現在bPのデータがある)
次にbQの人がこのブックを起動し、シート2で情報を入力する、するとシート1のbQのところにデータが反映され、bPの人のデータはシート2にないため、無くなってしまうのではないかと言う事です。
 
つまり、VLOOKUP関数でシート1に反映させ、なおかつ新しい別の人がデータを入力してもシート1のデータが消えないようにする方法は、私には思いつきません。
 
私の浅はかな考えでは、
シート2で社員が自分のデータを入力する
保存ボタンなどを押すと、シート1の該当場所に入力データが反映され保存される
もし、修正があるのであれば、シート2で呼び出しボタンなどを押すと、シート1から街頭のデータを呼び出す。
その後、シート2でデータを修正し、保存ボタンを押すとシート1の該当場所に上書きされる。
 
と言った感じです。
 
すいません、できそうになければ手作業でやりますのですいませんでした。

回答
投稿日時: 22/09/03 23:47:50
投稿者: WinArrow
投稿者のウェブサイトに移動

シート2は、入力表で1件しかデータを入力しないということですね?
  
それならば、おっしゃっていることは理解できます。
 
 
話は、若干それますが、
今回の処理は、
「シート1の然るべきところに、データを登録したい」
が基本的なシステム要求と思います。
 
●一般機能だけで実現させる方法。
「検索」機能を使って、「番号」をキーにして、該当行にカーソルを移動させれば、
そのまま、横にデータが入力可能です。
シート2に入力する手間と同じ手間になります。
既に登録されているデータも検証しながら修正可能です。
存在しない番号(キー)は、データ入力する前にわかります。
 
●シート2を入力用フォームとして使用する方法。
番号欄には、入力規則を設定する、参照リストは、シート1の番号列
シート2の番号選択と同時に、他の項目をシート1から転記します。
※Worksheet_Changeイベントを使います。
この時、シート1の行番号をメモリに記憶しておきます。
(データ入力後、シート2からシート1へ転記する時に使えば、検索不要になる)
 
 
 
●入力用フォームとしてユーザーフォームを使う。
番号は、コンボボクス
他の項目はテキストボックス
シート1登録用コマンドボタンを配置
終了用コマンドボタン配置
 
いろいろな方法があります。
検討してみるとよいでしょう。
 
 

回答
投稿日時: 22/09/04 12:00:06
投稿者: simple

訂正です。

    Dim toR As Long
としましたが、
    Dim toR As Variant
としてください。
また、値貼り付けであれば、PasteSpecialを使ったコードにしてください。
        ws2.Cells(fromR, "A").Resize(1, 6).Copy
        ws1.Cells(toR, "A").PasteSpecial Paste:=xlPasteValues
といったものです。
サンプルなので、確認して応用してみてください。

回答
投稿日時: 22/09/04 12:49:58
投稿者: simple

説明が後先になりましたが、追加説明をしておきます。
 
もともとの質問は、
> ペーストする場所がある特定のデータによって場所が変わるところがよくわかりません。
ということでした。
そこは、一言で言えば、「どこに貼り付けるかを検索してください」というのが答えです。
(1)
ワークシート関数にWorksheetFunctionを付けるか単にApplicationをつけることにより、
VBAの中でワークシート関数を使うことができます。
私案では、Match関数の利用を提案しました。
(なお、該当なしの場合、@WorksheetFunction.Matchだとエラーで止まりますが、
  AApplication.Matchだとエラーで止まることなく、エラー値が返されますので、
  こちらのほうが簡単にコード化できると思います。
  むろん @の場合でも、On Error で対応することも可能です。
  https://www.moug.net/tech/exvba/0100004.html
  https://www.moug.net/tech/exvba/0100035.html
  など、こちらのサイトの「即効クニック」の記事が参考になります。
(2)
もちろん、Findメソッドを使って検索することも可能です。
マクロ記録をとっても参考コードが得られますし、
ネットで検索すれば関連記事がたくさんヒットします。
 
周辺のシステムの作りに言及し過ぎたかもしれません。
それは質問者さんにおいて検討されればよいことだったかもしれません。
もちろん、参考にしていただけるだろうと思って発言はしていますけれども。

投稿日時: 22/09/08 19:01:17
投稿者: hidey_g

色々アドバイスありがとうございました。
ベースのコードを少しずつ修正して何とか希望の処理をすることができました。
これは、アレンジも可能だと思うんでこれから使っていきたいと思います。
こちらで相談できて本当に感謝です。