[[20170204150113]] 『データを蓄積したい。』(初心者) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『データを蓄積したい。』(初心者)

初めて質問させていただきます。何分、初心者でありながら、やりたい!ことはかなりありますので、どうかよろしくお願いします。

シート1に、日毎に記入する洋式を作って、そこに入力して、更新みたいなボタンを押すと、シート2に蓄積されたい。そして、シート1でキーとなるもの?(日付とか)を入力すると、過去のデータを呼ぶ出すことができて、そのシート1を紙のデータとして印刷して保管する。
シート1に入力するデータ数(セル数)はかなりあります。

こんな感じなのですが、プロみたいな知識を持ってある方がたくさんおられると聞き、投稿させていただきます。よろしくお願いします。

< 使用 Excel:Excel2013、使用 OS:Windows7 >


これではだめでしょうか
シート2しか使いません。

データの入力はフォームを利用
http://www4.synapse.ne.jp/yone/excel2013/excel2013_input_form.html

過去データの抽出はオートフィルタ
http://www4.synapse.ne.jp/yone/excel2013/excel2013_filter.html

(マナ) 2017/02/04(土) 15:31


早速の回答ありがとうございます。
できれば、シート1に印刷可能な様式に入力したいのですが、かなり複雑になりますか?
(初心者) 2017/02/04(土) 15:36

今まで使っている様式は、月ごとにシート分けしていて、一つのシートには1カ月分の様式が日数分最大31個同じ様式があるわけです。
12シート×31個、あまりに使いにくくて、投稿した次第です。
その様式の内容はざっくりと、名前があって、それに対して18列程度の項目があり、それに数字を入れる。あとは、それを集計する表がある感じです。その名前を入れる行は日によって違い、5人の場合もあれば、12人の場合もあり。なので、名前を入れる行は20行ぐらいは必要です。
(初心者) 2017/02/04(土) 15:44

 せめて サンプルであっても シート1のレイアウト と シート2のレイアウトを具体的に提示してください。
 あくまで『例』で結構ですから。 そこまで(レイアウト例作成まで)回答者にやらせるのは、いささか。

 ところで、VBA(あるいはエクセル)は、どれぐらいわかっていますか?

 印刷なんてのは、マクロがどうこうではなく操作で印刷すればしまいなのに、印刷したい という要望を出されてますね?
 クイックアクセスツールバーに印刷ボタンを配置しておけば、もっと楽ちんですね。

 ボタンを押すと という文言もありますね。
 ボタンをおして、マクロを実行させる というのは、もちろんできますが、わざわざ質問に入れること自体が
 ??です。(つい最近も、そういった質問者さんがおられましたけど)

 念のためボタンを配置してマクロ登録する方法、

[[20170202203223]] 『「最終行にVBAで合計を入れたい(複数列)」[柳田]=x(hotspa)

 あたりを参照してください。

(β) 2017/02/04(土) 15:50


マクロは全く分かりません。これからな感じです。

印刷したいというのは、そのシート自体の様式を印刷したいという意味ですので、それ自体をマクロ?とかを使ってやるという意味ではありません。

レイアウトはのちほどおくりますね。
(初心者) 2017/02/04(土) 15:56


月ごとにシートを分けないで、全データをシート2に蓄積。
毎日「今日」のデータのみオートフィルターで抽出し、それを印刷。
過去データについても、「指定の期間」のみを抽出することで簡単にできます。

(マナ) 2017/02/04(土) 16:05


そうなんですか!
それは最初に回答いただいたやり方ですか?
(初心者) 2017/02/04(土) 16:07

そうです。

1つ訂正します。「オートフィルター」ではなく「フィルター」です。(同じ意味ですが)

(マナ) 2017/02/04(土) 16:12


決まった形の様式は、別シートに作らなければいけませんか?
(初心者) 2017/02/04(土) 16:13

印刷するのは、シート2というのが前提です。
別にシートは使いません。

(マナ) 2017/02/04(土) 16:22


あくまで、所定の様式があります。決められた様式です。
それを紙に出して決裁や保管します。
なので、決められた様式でないといけません。
(初心者) 2017/02/04(土) 16:25

では、だめです。
ワードの「差し込み印刷」も考えていましたが、
様式がエクセルでできているのなら、マクロを使うことになると思います。

βさんのコメント通り、レイアウトを明確にしていただかないと先に進みませんね。

(マナ) 2017/02/04(土) 16:32


後ほど送らせていただきます。有難うございます。
(初心者) 2017/02/04(土) 16:35

 こんばんわ。

 >あくまで、所定の様式があります。決められた様式です。 
 >それを紙に出して決裁や保管します。 
 >なので、決められた様式でないといけません。 

 入力とデータの管理はシート1で行って、A列にユニークなID番号を入力しておいて(単なる連番でも良いです)
 シート2に所定の様式を作成して、VLOOKUP関数でA列のIDをキーにして読み込むと言う方法もあります。

 マクロでも同じで、印刷したい行を求めて、その行のデータを転記すると言う方法になります。
 可変行の特定部分だけは無理ですが転記はマクロ記録で出来ます。

 数式・マクロどちらの方法でも印刷フォーマットのレイアウトが分かれば、比較的簡単に作成できると思います。

(sy) 2017/02/04(土) 18:01


所定様式は、体裁を整えるため、B列からAW列の多くの列を使用しています。列幅は1.25にしています。

B7=日付
X7=天気
AM7=担当者名

J11,J12,J13=人数 J14=J11〜J13の合計
R11,R12,R13=件数 R14=R11〜R13の合計

AH11,AH12,AH13,AH14=件数
AP11,AP12,AP13,AP14=件数

E7=特記事項(E17〜AV17結合セル かなりの文字数が入ります)

E23=名前
P23..AV23=該当箇所に件数(2列ずつ結合させ、17セル)N23=P23〜AV23の17セルの合計
これが20行は必要です。しかし、名前が入る分だけ罫線を引くという形に出来ればベストです。
21行目が各列の合計になります。

わかりにくいですかね。すいません。
(初心者) 2017/02/04(土) 22:01


 ワープロ仕様の結合セルだらけの書式みたいですね。
 まぁ最終フォーマットだから問題ないと言えば問題ないですけど。。。

 まず初心者さんが(初心者と言う名前はよく使われるので、出来れば特定できるユニークな名前に変更してほしいですけど)
 マクロか数式かどちらで処理したいのかを明確にして下さい。

 後これはデータベース仕様にするなら、どんな方法を選択しても共通になりますが、
 データ蓄積用のシート1は、必要データを横一列に記載して、1行1レコードにする必要があります。
 そうしないと質問者・回答者ともに、とんでもない苦労をする事になりますので必須と思って下さい。

 またこれは必須では無いですが、A列にユニークなIDを入れれば、マクロ・数式関係なく検索が楽になりますが可能ですか?
 (連番でも良いですが、日付やLotNoなどを組み合わせた特定しやすいIDが望ましいです。例:20170204X123-1など)

 まずはマクロか数式かどちらを選ぶか決めて下さい。

(sy) 2017/02/04(土) 22:26


体裁を考えて結合だらけになっているようで、申し訳ありません。
整理させてください。
シート1が所定の様式(実際に日々の入力をするもの)で、シート2が蓄積のための横一列のシートということですかね?

蓄積用は横一列で全く大丈夫です。
ユニークなIDということですが、これは日誌で一日一枚なので日付は一つしかありませんが、日付では無理でしょうか?できれば、日付を検索のカギにしたいと考えております。
それと、マクロか数式かということですが、私自身マクロは全く知識がなしなので、それでもやれるならば、数式でお願いできますでしょうか。数式もわかるわけではありませんが。

名前ですが、初心者を愚か者に変更したいと考えております。ユニークでしょうか。よろしくお願いいたします。

(初心者) 2017/02/04(土) 22:46


 >>名前ですが、初心者を愚か者に変更したいと考えております。

 ユニークかもしれませんが、ユニークだったら何でもいいということではないですよね。
 (愚か者というHN,別の意味でユニークですけどね)

 ちゃんとした(何がちゃんとしたものかの議論はあるかもしれませんが)HNにしたほうがいいですよ。

(β) 2017/02/04(土) 23:03


 名前に関しては「愚か者」さんはあまり聞かないので大丈夫かも知れませんが、
 お呼びする時何か申し訳なく思ってしまいそうな、こちらが罪悪感が沸く名前ですね。

 1行1レコードならデータを蓄積する分には問題無いです。

 >日誌で一日一枚なので日付は一つしかありません
 重複しないなら日付で大丈夫ですよ。
 それなら別途列を用意しなくても大丈夫です。
 (日付がA列にある方が検索はしやすいですが、まぁどこでも良いです)

 >シート1が所定の様式(実際に日々の入力をするもの)
 入力は上の方でマナさんが提示されている入力フォームを使う事を想定していましたが、印刷帳票のシートから転記させたいんですか?
 その場合はマクロ限定になります。
 (数式が消えてしまうのと、最終行に追記する操作はマクロで無いと出来ません)
 数式で出来る事は、蓄積されたデータから印刷時に、日付で検索して所定様式に表示させる事です。

(sy) 2017/02/04(土) 23:14


まず、名前の件はすいません。どうでもいいことで手間取らせてしまって。
愚か者は止めて、トランプにしたいと思います。

出来れば、実際の所定の様式(印刷帳票)から入力をさせたいと考えております。
となれば、数式ではなく、マクロ?になるとのことですね。
よろしくお願いいたします。
(トランプ) 2017/02/04(土) 23:22


 まずVBEの起動方法などの基本的なのは、ご自身でネットで調べて下さい。
 図解入りで説明してくれているサイトは沢山あります。
 Sub〜End Subまでの記述方法の基礎くらいまではご自身で勉強して下さい。

 それと実際に作るのはトランプさんご自身ですよ。
 私はその手助けする形で進めていきます。
 ただし平日は仕事が遅いので遅い時間しかin出来ませんが。
 まぁマクロに詳しい人はここは沢山いるので、誰か答えてくれるので問題ないと思います。

 もちろんめんどくさいから答えだけくれと言うなら言って頂ければ、
 私はそう言うのは後で何度もやり取りが発生したりする事が多いのでめんどくさいので辞退しますが、
 書いてくれる人はいてると思いますし、別に悪い事では無いので気にしないで下さい。

 それで良いですか?

(sy) 2017/02/04(土) 23:52


出来れば、全く無知なところからなので、
答えをまずはいただければうれしいです。
これを基に他のものに派生させながら学んでいきたいと思います。
勝手申し上げますが、よろしいでしょうか。
(トランプ) 2017/02/04(土) 23:58

 そうですか。

 でしたらコードは他の方に任せるとして、仕様の部分だけもう少し明確にしないと誰もコードを書けないので、
 その辺の話を少し。

 単純な事なんですけど、所定様式のセル番地とデータベースの並びは違うので、
 どのセルの値をどの列に転記するかをリスト化して提示して頂けますか。

 セル番地どうしの関係さえ分かれば、転記自体は簡単です。

 後、同じ日付のデータを修正する時などはどのようにしたいのかですが、データベースで直接編集するのか、
 一旦所定様式に読み込んで修正するのか、などですね。

 簡単に改竄されては困るかどうかで、どれくらいまで防止策を施すかも変わりますね。

 転記のタイミングはボタンなどで行なうのか、印刷されたタイミングで自動で行うのかとか色々あります。

 読込も同じですね

(sy) 2017/02/05(日) 00:16


どのセルの値をどの列に転記するかをリスト化?
ごめんなさい。この意味が・・・申し訳ありません。

同じ日付が出た場合の修正は、所定様式に読み込んでの修正がよいような気がするのですが。あくまで、データベースは蓄積専用で、そのほかはすべて所定様式でできたらと考えております。

転記のタイミング?というのは、次の日を入力するタイミング?ということでそうか?
それならば、ボタンの方がよいような気がします。

不明確な答えばかりでごめんなさい。よろしくお願いいたします。
(トランプ) 2017/02/05(日) 00:24


 リスト化と言うのはセル番地の関係性を誰が見ても分かるようにして頂きたいと言う事です。
 以下のような事です。

 項目名   所定様式  データベース
  日付         B7           A列
  天気     X7           B列

 項目名は最悪なくても良いですが、上記のようなどのセルの値をどの列に転記すれば良いのかを分かるようにリスト化して頂きたいです。
 これが無ければ誰もコードを書けません。

 ボタンでと言う事なら、
 転記:データベースに転記した後、シートを初期化する
 読込:入力された日付を元にデータを読込む

 普通はこんな所でしょうか。

(sy) 2017/02/05(日) 00:42


syさんの指示どおり、リスト化してみました。
データベースの列は、A列はあけて、B列からとしています。
以下42行までとしていますが、本当は後10行追加したいです。しかし、そうなると1ページにはおさまらなくなってしまいます。その際は、項目名(相談1〜17及び合計)が入る20行目から22行目を印刷タイトルにして、2ページ目を印刷できるような設定に出来ればと思います。
どうぞお願いいたします。

項目名   所定様式   データベース

  日付          B7                           B列
  天気      X7                           C列
  記載者       AM7                           D列
 相談 1       J11                           E列
       2       J12                           F列
       3       J13                           G列
    合計       J14(J11〜J12合計)             H列
  件数 1       R11                           I列
       2       R12                           J列
       3       R13                           K列
    合計       R14(R11〜R13合計)             L列
  手段 1      AH11                           M列
       2      AH12                           N列
       3      AH13                           O列
       4      AH14                           P列
  対処 1      AP11                           Q列
       2      AP12                           R列
       3      AP13                           S列
       4      AP14                           T列
  特記         E17(文章文字数多い)           U列
               E23                           V列
  相談 1       N23                           W列
       2       P23                           X列
       3       R23                           Y列
       4       T23                           Z列
       5       V23                          AA列
       6       X23                          AB列
       7       Z23                          AC列
       8      AB23                          AD列
       9      AD23                          AE列
       10     AF23                          AF列
       11     AH23                          AG列
       12     AJ23                          AH列
       13     AL23                          AI列
       14     AN23                          AJ列
       15     AP23                          AK列
       16     AR23                          AL列
       17     AT23                          AM列
  合計        AV23(E23〜AT23合計)           AN列
           (以下42行まで同様)               AO列〜BF列
  合計     (43行は上記列の合計)             BG列
(トランプ) 2017/02/05(日) 01:38

追伸
天気と記載者は、セルにリストを作成できればと思います。
(トランプ) 2017/02/05(日) 01:39

追追伸

所定様式 E23 の項目名は、氏名です。抜けてばかりですいません。
(トランプ) 2017/02/05(日) 01:42


おはようございます。
今見直していて誤りに気づいたのですが、下から1、2行のデーターベースの列に誤りがありました。
これは、あくまで24行目の列です。25行目〜42行目も同様に列が増えていく感じですよね。これをさらに10行追加すれば、さらに列が増えていくということですね。
(トランプ) 2017/02/05(日) 07:44

24行目以降は、氏名E23〜合計AV23の繰り返しになるわけですが、これを42行又はプラス52行となるとすごい列数になると想像するんですが、これを氏名が入ったとこまでにして、その次の行に合計というふうには出来ないでしょうか。つまり、氏名が5人の場合もあれば、25人の場合もあるということで、氏名の数によって行数を変えるみたいな。マクロがわからず言いたいことばかり言ってると思います。すいません。
(トランプ) 2017/02/05(日) 08:18

 おはようございます。

 リストありがとうございます。
 合計などは所定様式の方に計算式を入れていれば、読込んだ時に勝手に計算してくれるので、
 データベースに登録する必要は無いですね。

 方法としては仮の名前ですけど転記用シートにアップして頂いたリストを以下のように記載して、
 マクロでそれを読込んで転記します。
 こうしておけばリストを編集するだけで、データの増減などにも自由に対応できるようになります。
 リストの項目を追加したり減らしたりして試してみて下さい。

        A        B           C  
  1  項目名  所定様式  データベース   
  2  日付    B7        B              
  3  天気    X7        C              
  4  記載者  AM7       D              
  5  相談1   J11       E              
  6  相談2   J12       F              
  7  相談3   J13       G              
  8  件数1   R11       H              
  9  件数2   R12       I              
 10  件数3   R13       J              
 11  手段1   AH11      K              
 12  手段2   AH12      L              
 13  手段3   AH13      M              
 14  手段4   AH14      N              
 15  対処1   AP11      O              
 16  対処2   AP12      P              
 17  対処3   AP13      Q              
 18  対処4   AP14      R              
 19  特記    E17       S              
 20  氏名    E23       T              
 21  相談1   N23       U              
 22  相談2   P23       V              
 23  相談3   R23       W              
 24  相談4   T23       X              
 25  相談5   V23       Y              
 26  相談6   X23       Z              
 27  相談7   Z23       AA             
 28  相談8   AB23      AB             
 29  相談9   AD23      AC             
 30  相談10  AF23      AD             
 31  相談11  AH23      AE             
 32  相談12  AJ23      AF             
 33  相談13  AL23      AG             
 34  相談14  AN23      AH             
 35  相談15  AP23      AI             
 36  相談16  AR23      AJ             
 37  相談17  AT23      AK             

 コードは以下で、標準モジュールを追加して貼り付けて下さい。
 後はボタンを2つ用意して、マクロを登録して下さい。
 Posting_Input がDBに登録
 Posting_Output がDBから読込です。

 Const sh1Name As String = "入出力"
 Const sh2Name As String = "DB"
 Const sh3Name As String = "転記用"
 これがシート名ですので実際のシート名に変更して下さい。

 ここから以下を貼付け

 Option Explicit

 Const sh1Name As String = "入出力"
 Const sh2Name As String = "DB"
 Const sh3Name As String = "転記用"

 Sub Posting_Input()
 '入力後のデータをDBに登録
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim row1 As Long
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh2 = Sheets(sh2Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データの存在チェック
    If sh1.Range("B7").Value = "" Then
        MsgBox "日付が未入力なのでDBに登録できません。"
        Exit Sub
    End If
    If WorksheetFunction.CountIf(sh2.Range("B:B"), sh1.Range("B7")) Then
        If MsgBox("すでにDBに登録されています。" & vbLf & "上書きしますか?", vbYesNo) = vbYes Then
            row1 = WorksheetFunction.Match(sh1.Range("B7"), sh2.Range("B:B"), 0)
        Else
            Exit Sub
        End If
    Else
        row1 = sh2.Range("B" & Rows.Count).End(xlUp).Row + 1
    End If

    'データの転記
    For i = 2 To UBound(v, 1)
        sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value
    Next i

 End Sub

 Sub Posting_Output()
 'DBのデータを読込
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim row1 As Long
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh2 = Sheets(sh2Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データの存在チェック
    If sh1.Range("B7").Value = "" Then
        MsgBox "日付が未入力なので読込できません。"
        Exit Sub
    End If
    If WorksheetFunction.CountIf(sh2.Range("B:B"), sh1.Range("B7")) Then
        row1 = WorksheetFunction.Match(sh1.Range("B7"), sh2.Range("B:B"), 0)
    Else
        MsgBox "該当の日付のデータがありません。"
        Exit Sub
    End If

    'データの転記
    For i = 2 To UBound(v, 1)
        sh1.Range(v(i, 2)).Value = sh2.Cells(row1, v(i, 3)).Value
    Next i

 End Sub

(sy) 2017/02/05(日) 09:19


 所定様式に現在表示中のデータの消去を忘れてました。
 以下を追加して「クリア」とでも書いたボタンに登録して下さい。

 Sub Data_Clear()
 '表示中データのクリア
    Dim sh1 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データのクリア
    For i = 2 To UBound(v, 1)
        sh1.Range(v(i, 2)).MergeArea.ClearContents
    Next i

 End Sub

(sy) 2017/02/05(日) 09:39


おはようございます。
すごいです。ただそう感じております。

早速ご指示のとおりしておりますが、シートは全部で3つ必要になるわけですよね。
 入出力(=所定様式)←実際に印刷するもの
 DB(上記で入力したものを蓄積するもの)
 転記用(これは?)
上記名前のシートを作成して、入出力シートに上記コードを標準モジュールに張り付けて、同シートに3つのボタン(登録、読込、クリア)を作成しました。
DBのシートには、該当の列に項目名を記入するのですよね?これは、行は1行目でも3行目でもよいのでしょうか?また、転記用シートは、まっさらでよいのでしょうか?
的外れな質問かもしれませんが、よろしくお願いいたします。

(トランプ) 2017/02/05(日) 10:51


入出力シートにボタンを挿入するため、行を2行追加してしまいましたが、日付のB7はB9に変更してみたのですが、そのほかに修正する必要がありますか?
(トランプ) 2017/02/05(日) 11:04

 はい。シートは3つになります。

 DBシートはまっさらの状態だと2行目から追記されます。
 B列で最終行を判定しています。
 なので例えばB4に日付と項目名を入力していれば、5行目から追記されます。

 転記用シートは、A列に項目名、B列に所定様式のセル、C列にDBの列を以下のように記載して下さい。
 ここに記載されている行を2行目から最終行まで読み込んで転記するセルを特定しています。
 行と行は間を開けないようにして下さい。
 (2行目に日付で、3行目は無くて、4行目に天気とかにしてしまうと、日付しか読み込めません)
 取り敢えずアップして頂いたリストは、合計を省いて全部ありますが、追加したい項目などがあれば38行目以降に追加して下さい。

        A        B           C  
  1  項目名  所定様式  データベース   
  2  日付    B7        B              
  3  天気    X7        C              
  4  記載者  AM7       D              
  5  相談1   J11       E              
  6  相談2   J12       F              
  7  相談3   J13       G              
  8  件数1   R11       H              
  9  件数2   R12       I              
 10  件数3   R13       J              
 11  手段1   AH11      K              
 12  手段2   AH12      L              
 13  手段3   AH13      M              
 14  手段4   AH14      N              
 15  対処1   AP11      O              
 16  対処2   AP12      P              
 17  対処3   AP13      Q              
 18  対処4   AP14      R              
 19  特記    E17       S              
 20  氏名    E23       T              
 21  相談1   N23       U              
 22  相談2   P23       V              
 23  相談3   R23       W              
 24  相談4   T23       X              
 25  相談5   V23       Y              
 26  相談6   X23       Z              
 27  相談7   Z23       AA             
 28  相談8   AB23      AB             
 29  相談9   AD23      AC             
 30  相談10  AF23      AD             
 31  相談11  AH23      AE             
 32  相談12  AJ23      AF             
 33  相談13  AL23      AG             
 34  相談14  AN23      AH             
 35  相談15  AP23      AI             
 36  相談16  AR23      AJ             
 37  相談17  AT23      AK      

(sy) 2017/02/05(日) 11:13


 >入出力シートにボタンを挿入するため、行を2行追加してしまいましたが、日付のB7はB9に変更してみたのですが、
 >そのほかに修正する必要がありますか?

 転記用シートのリストで自由に変更可能です。
 こちらでは画面が見えないので、B列のセル番地を色々変更して試してみて下さい。

(sy) 2017/02/05(日) 11:16


DBシートに登録されました!すごい!
ただ、日付が登録されません。「平成29年02月05日(日)」という形で入出力シートに入力しているのですが、DBシートに登録されません。そのため、次の日付の登録ができない状況です。
(トランプ) 2017/02/05(日) 11:34

入出力シートとDBシートの日付の書式を一緒にしてみましたが、これもうまくいかず、登録しても日付だけは記録されません。。。
(トランプ) 2017/02/05(日) 12:39

 そっか、日付のセルを変更していたんでしたね。

 コード内で sh1.Range("B7") と書いてる記述が数か所あると思いますが、全ての"B7"をv(1,2)に以下のように変更して下さい。
 sh1.Range(v(1,2))

 これで転記用シートの日付セル番地を参照する要因なります。

 データベース側のB列は変更しないと思うのでそのままにしておきます。

(sy) 2017/02/05(日) 12:57


 記述中では、B7はsh1にしかなかったので、VBEの編集→置換で 置換前 B7 置換後 v(1,2) 全て置換が簡単ですね。

(sy) 2017/02/05(日) 13:02


すいません。

間違えました。
v(2,2) でした。

(sy) 2017/02/05(日) 13:04


修正したところ、以下の箇所が「コンパイルエラー 構文エラー」が出てしまいます。

If WorksheetFunction.CountIf(sh2.Range("B:B"), sh1.Range(v(2,2)) Then
(トランプ) 2017/02/05(日) 13:06


 最後 ) が1つ足りませんね

(sy) 2017/02/05(日) 13:08


 こうですよ。

 If WorksheetFunction.CountIf(sh2.Range("B:B"), sh1.Range(v(2, 2))) Then
                                                                ↑ )は3つです

(sy) 2017/02/05(日) 13:11


上記のように)を3つに修正して、「コンパイルエラー構文エラー」が出現しなくなったため、入出力シートに日付を入力して、登録ボタンを押すと、やはり日付がDBシートに記録されません・・・
(トランプ) 2017/02/05(日) 13:15

 どうなってるんだろ?

 確認ですけど、
 入出力シートは結合セルですか?
 結合セルなら、結合の左上のセルがB9ですか?

 転記用シートのB2セルにB9としていますか?

 後うまくいかなかった時のデータは残ってると不具合の原因になるので、データベースから消してから再度実行実行して下さい。

(sy) 2017/02/05(日) 13:22


B2セルなんですね。私はB1セルにB9としていました。ということは、1行目は空欄ですか?
ただ、B2セルとしたら、日付は登録されましたが、氏名が入ってきません。
(トランプ) 2017/02/05(日) 13:26

それから、実際に入力して登録してみると、以下の項目のデータがDBに全く登録されません。
転記用シートも確認しましたが、間違いないのですが・・・
もちろん、ボタンを追加して行が2行ずれてますので、例えば氏名であればE25という形で行数も修正しています。しかし、以下は記録されません。

20 氏名 E23 T

 21  相談1   N23       U              
 22  相談2   P23       V              
 23  相談3   R23       W              
 24  相談4   T23       X              
 25  相談5   V23       Y              
 26  相談6   X23       Z              
 27  相談7   Z23       AA             
 28  相談8   AB23      AB             
 29  相談9   AD23      AC             
 30  相談10  AF23      AD             
 31  相談11  AH23      AE             
 32  相談12  AJ23      AF             
 33  相談13  AL23      AG             
 34  相談14  AN23      AH             
 35  相談15  AP23      AI             
 36  相談16  AR23      AJ             
 37  相談17  AT23      AK 
(トランプ) 2017/02/05(日) 13:37

(トランプ) 2017/02/05(日) 13:26に対しての返信です。

 >ここに記載されている行を2行目から最終行まで読み込んで転記するセルを特定しています。
 ちゃんと2行目から読み込むと説明していますよ。

 2回、図入りで説明してるので、ここは非常に重要な部分ですので、しっかり読んで下さい。
 私が上で説明してる通りにリストを作成して下さい。

 画面を表示できないので以下のような表現を用いています。

 ↓この1234などは行番号を表しています。

        A        B           C         ←このABCは列を表しています。
  1  項目名  所定様式  データベース   
  2  日付    B9        B              
  3  天気    X7        C              
  4  記載者  AM7       D            

 上記で言うと1行目は項目ラベルです。
 2行目が日付で、B2にセル番地、C2に列を記載しています。
 3行目以降も同じ要領で行を開けずに記載して下さい。

(sy) 2017/02/05(日) 13:40


 確認ですけど、
 転記シートに19行目は存在しますか?
 何度も書いてるように行に間があってはいけませんよ?
 その辺は大丈夫ですか?

(sy) 2017/02/05(日) 13:43


はい、19行目存在しております。行に空白ありません。
syさんのご指導のとおりの要領で記載しているのですが、なぜか氏名以下が記録されないのです。
それ以前については、きちんと記録されています。

(トランプ) 2017/02/05(日) 13:47


 転記出来ないセルは全て結合セルと思いますが、結合セルの場合一番左上に当たるセルしか認識できません。
 そのセルを選択した時に名前ボックスに意図したセル番地が表示されていますか?
 名前ボックスが分からなければ「EXCEL 名前ボックス」で検索すると山ほどHITするので調べて下さい。

(sy) 2017/02/05(日) 14:01


syさん、氏名以下を20行追加していたのですが、転記用のデーターベースに誤りがあったため修正したところ、氏名以下の登録がしっかりと出来ました!私の誤りでした。ありがとうございます。
もう少し、すべての箇所に入力してみて試してみます。
(トランプ) 2017/02/05(日) 14:05

すごいです。ありがとうございます。
入出力シートは、複数の人間で入力することを考えると、このシートに保護をかけて、関数等が入った箇所がありますので、この部分の改ざんなどを防止したいと思い、保護をかけてみましたが、保護を解除するよう求めるメッセージが出ました。この問題について、解決する方法はあるでしょうか?
(トランプ) 2017/02/05(日) 14:55

 今外出しましたので返事は遅くなります。

 一つ確認ですけど、計算式以外のセルは手入力は出来るようになってますか?

(sy) 2017/02/05(日) 15:03


 方法は2つ。

 ・ブックを開いた時点で(ThisWorkbookモジュールの Workbook_Open で)該当のシートに対して
  そのシート.Protect  ・・・(注)・・・・,UseInterFaceOnly:=True
  このコードを記述しておく。

  これで、操作者に対しては保護、マクロに対しては『限りなく非保護に近い』状態になり
  マクロからの書き込み等、『ほとんどの』操作が可能になりなすので、既存コードはそのまま
  使うことができます。

  でも、万能ではありません。マクロ処理の内容によっては、エラーになってしまうこともあります。

 ・マクロ処理の前に そのシート.Unprotect 、マクロ処理後、そのシート.Protect  ・・・(注)・・・・
  マクロ処理中は、確実に、非保護シートになっていますので、何でもできます。

 ・・・(注)・・・・

 シート保護時、ユーザーに許可する操作として、いろいろ設定していたとします。
 Unprotect では、すべてが外れるわけですが、Protect 時、あくまで、Protect の引数として指定した許可条件のみになります。
 したがって、自分がセットしていたはずの許可条件を、明示的に Protectの引数として再指定する必要があります。
 シート保護操作で、各種許可をセットして保護する、その操作をマクロ記録すればそのコードが生成されます。

 あるいは、保護解除前に、このシートに設定されていたはずの許可条件を、そのまま引き継いでセットすることも可能です。
 ご希望なら、そのコードをアップします。

(β) 2017/02/05(日) 15:09


この保護の問題で考えておりましたら、自分の中での問題が見えてきました。
というのは、以下のJ13とR13には関数を入れています。件数を算出してくるものです。ただ、登録や読込やクリアすると、その関数は消えてしまっているんですよね。この項目はマクロに含めなければと思われるかもしれないんですが、月間の集計等を出す上でこれらは必要なので、関数を維持しつつマクロとしてデータベースに記録させてたいのです。また、氏名入力行である25行目から44行目を集計した結果を45行目に入れているのですが、これも同様に月間集計を出す上で、データベースに含ませているのですが、関数が消えてしまい、集計をマクロに入れてあげることが出来ないのですよね。
これを入れてあげるためにはどのような操作が必要になるでしょうか。

        A        B           C  
  1  項目名  所定様式  データベース   
 5  相談1   J13       E              
  8  件数1   R13       H              

 37  相談17  AT23      AK      
(トランプ) 2017/02/05(日) 15:50

βさん、ありがとうございます。
保護については、直接手入力する箇所以外は保護をかけたいと思います。
(トランプ) 2017/02/05(日) 17:08

 セルの保護のかけ方は分かりますか?

 手入力のセルはロックを外しておかないと、入力そのものが出来なくなります。
 私のマクロはロックのかかってないセルへの転記は出来るはずですが?
 間違ってロックをかけてはいけないセルにロックがかかってる筈ですので確認して下さい。

 まさか以下のセルをデータベースに登録してるんですか?
 >J13とR13には関数を入れています。
 上でも説明してるように数式のセルは読込時に自動で計算してくれるのでデータベースに登録する必要がありません。

 >登録や読込やクリアすると、その関数は消えてしまっているんですよね。
 当たり前です。
 なぜ出来ると思ったんですか?

 >月間の集計等を出す上でこれらは必要
 月の合計ですよね?
 J13やR13がどのセルの合計を出してるかは分かりませんが、当然データベースにも参照元のデータは蓄積されてる筈です。
 ならその参照元データをSUMIFSで期間中の分だけ合計すれば良いだけの事ですよね。
 数式のセルをデータベースに残す意味が全く分かりません?
 何が問題なんでしょうか?

(sy) 2017/02/05(日) 17:59


月間の実績を出す場合です。
一か月の日毎の実績を出す必要があるのですが、データベースの20行ある数値を17の項目別に集計する場合、どのような方法が適当でしょうか?アドレス願います。
(トランプ) 2017/02/05(日) 18:29

 なるほど、すいません。
 ちょっとSUMIFSでは出来ないかも知れませんね。

 まず月間の合計は数式で、一番元の値から集計は可能です。
 ただしSUMIFS等での集計は1つの列の集計になるので、複数列はその列数分プラスしないといけないので、17列もあると非効率ですね。

 数式を提示する事は出来ますが、その為にはJ13とR13が参照してる元データは、データベースの方ではどの列からどの列になるんですか?
 元データの列は連続してますか?
 それとも飛び飛びですか?

(sy) 2017/02/05(日) 18:45


syさん、すいませんだなんて、それは私の言葉です。こんなに親身になって頂き感謝しかありません。本当に有難うございます。

ご質問の件は、以下のとおりです。何とかデータベースに残したいです。
J13=IF(COUNTA(E25:L44)=0,"",COUNTA(E25:L44)-SUM(J14:Q15))
R13=IF(AV45=0,"",AV45-SUM(R14:Y15))
J16=SUM(J13:Q15)
R16=SUM(R13:Y15)

以下は、20行の17列の項目を集計した結果です。これも何とかデータベースへ残したいものです。
よい方策をお願いいたします。
N45=SUM(N25:O44)



AT45=SUM(AT25:AU44)
(トランプ) 2017/02/05(日) 19:48


syさん、以下のJ13、R13以外の月ごとの累計の出し方も教えていただけると有難いです。
月間の実績は、年と月を選択すると、その月の日ごと項目ごとの数をデータベースからもってくるようにしたいのと、以下の集計においても月ごとの集計をデータベースからもってこれたらと思います。

  5  相談1   J13       E              
  6  相談2   J14       F              
  7  相談3   J15       G              
  8  件数1   R13       H              
  9  件数2   R14       I              
 10  件数3   R15       J              
 11  手段1   AH13      K              
 12  手段2   AH14      L              
 13  手段3   AH15      M              
 14  手段4   AH16      N              
 15  対処1   AP13      O              
 16  対処2   AP14      P              
 17  対処3   AP15      Q              
 18  対処4   AP16      R              

(トランプ) 2017/02/05(日) 20:04


 >データベースの方ではどの列からどの列になるんですか?
 この質問に答えて下さい。
 私にはトランプさんのExcelシートは見えません。
 なので見えない人にも分かるような説明をして頂けませんか。
 初めに断ってるように正直見えないシートを、説明もなしに想像しながら何度も同じ事のやり取りはしたくないです。
 仮にデータベースの2行目に目的の式を入れるとどうなりますか?

 所定様式のセル番地で話をされても、私にはデータベースがどうなってるかが分からないので、数式を組み立てようがありません。

 以下の(他にもあるなら全ての)式をデータベースの列で表現して下さい。
 例えばE25:L44とは、データベース側ではどの列になるかと言う事が分からないと、数式の組み立てようがありません。
 特に結合セルだらけなので、全く読めないです。

 >J13=IF(COUNTA(E25:L44)=0,"",COUNTA(E25:L44)-SUM(J14:Q15)) 
 >R13=IF(AV45=0,"",AV45-SUM(R14:Y15)) 
 >J16=SUM(J13:Q15) 
 >R16=SUM(R13:Y15) 

 取り敢えず2行目のデータを使ってで良いので、全ての数式を提示してもらってからですね。
 後平日は返事は遅くなります。

(sy) 2017/02/05(日) 21:32


データベースの方ではどの列からどの列になるんですか? 以下のとおりです。あと、20行の列ごとの合計、データベースに入れていて式が消えているといった分も列番号が必要ですよね。これは、かなり時間がかかりそうなので、のちほど送ります。

J13=IF(COUNTA(E25:L44)=0,"",COUNTA(E25:L44)-SUM(J14:Q15))

 E25:L44→T,AL,BD,BV,CN,DF,DX,EP,FH,FZ,GR,HJ,IB,IT,JL,KD,KV,LN,MF,MX
 J14:Q15→F,G

R13=IF(AV45=0,"",AV45-SUM(R14:Y15))

 AV45→これは所定様式に関数を入れている総合計の件数です。(データベースには入ってません)
 R14:Y15→I,J

J16=SUM(J13:Q15)

 J13:Q15→E,F,G

R16=SUM(R13:Y15)

 R13:Q15→H,I,J

(トランプ) 2017/02/05(日) 22:23


20行の列ごとの合計の分です。横におさまりきれなかったので二段書きしています。
1から相談1→17が相談17の順になります。
大変な量になりますね。すみません。syさん、どうぞよろしくお願いいたします。

1=SUM(N25:O44)
2=SUM(P25:Q44)
3=SUM(R25:S44)
4=SUM(T25:U44)
5=SUM(V25:W44)
6=SUM(X25:Y44)
7=SUM(Z25:AA44)
8=SUM(AB25:AC44)
9=SUM(AD25:AE44)
10=SUM(AF25:AG44)
11=SUM(AH25:AI44)
12=SUM(AJ25:AK44)
13=SUM(AL25:AM44)
14=SUM(AN25:AO44)
15=SUM(AP25:AQ44)
16=SUM(AR25:AS44)
17=SUM(AT25:AU44)

1 U AM BE BW CO DG DY EQ FI GA
2 V AN BF BX CP DH DZ ER FJ GB
3 W AO BG BY CQ DI EA ES FK GC
4 X AP BH BZ CR DJ EB ET FL GD
5 Y AQ BI CA CS DK EC EU FM GE
6 Z AR BJ CB CT DL ED EV FN GF
7 AA AS BK CC CU DM EE EW FO GG
8 AB AT BL CD CV DN EF EX FP GH
9 AC AU BM CE CW DO EG EY FQ GI
10 AD AV BN CF CX DP EH EZ FR GJ
11 AE AW BO CG CY DQ EI FA FS GK
12 AF AX BP CH CZ DR EJ FB FT GL
13 AG AY BQ CI DA DS EK FC FU GM
14 AH AZ BR CJ DB DT EL FD FV GN
15 AI BA BS CK DC DU EM FE FW GO
16 AJ BB BT CL DD DV EN FF FX GP
17 AK BC BU CM DE DW EO FG FY GQ

1 GS HK IC IU JM KE KW LO MG MY
2 GT HL ID IV JN KF KX LP MH MZ
3 GU HM IE IW JO KG KY LQ MI NA
4 GV HN IF IX JP KH KZ LR MJ NB
5 GW HO IG IY JQ KI LA LS MK NC
6 GX HP IH IZ JR KJ LB LT ML ND
7 GY HQ II JA JS KK LC LU MM NE
8 GZ HR IJ JB JT KL LD LV MN NF
9 HA HS IK JC JU KM LE LW MO NG
10 HB HT IL JD JV KN LF LX MP NH
11 HC HU IM JE JW KO LG LY MQ NI
12 HD HV IN JF JX KP LH LZ MR NJ
13 HE HW IO JG JY KQ LI MA MS NK
14 HF HX IP JH JZ KR LJ MB MT NL
15 HG HY IQ JI KA KS LK MC MU NM
16 HH HZ IR JJ KB KT LL MD MV NN
17 HI IA IS JK KC KU LM ME MW NO

(トランプ) 2017/02/05(日) 22:37


 ちょっと数式を考える前に非常に気になる事が、、、

 このNO列って、、、、380項目くらいあるんですか!?
 これって所定書式は、いったい何ページに跨ってるんですか?

 とても1〜2ページで表示できるデータ量とは思えないです!

 列幅は1.25しか無いんですよね?
 そんなんでどうやってこれほどのデータを表示してるんですか?

 もしかして結合セルに含まれるすべてのセルを転記してるって事は無いですよね?
 結合セルは左上セルだけで良いですよ。

 なんか飛んでも無く変な事をしてるような感じがするんですけど?

 もう一度確認しますけどB列〜NO列までのデータは、本当に全て別々のセルに入力されたデータを転記してるんですか?
 手入力で380項目も毎日入力してるんですか?

(sy) 2017/02/06(月) 00:45


おはようございます。syさん、ありがとうございます。

私の作っている所定書式は、1ページにおさまっているんですが、syさんのご指摘どおり、ちょっとおかしな作り?になっているようです。指摘されて気づきました。すいません。
というのは、1ページの中には上下の表があり、上の表は下の表を集計した表みたいなもので、下の表はその明細みたいなものだと思います。その2つの表の横幅をきれいに同じにするために、B列〜AW列まで48列を使っています。体裁だけを重視して、非常に扱いずらいものになってしまっているわけですね。
データベースのB列〜NO列までのデータは、上記のセルを転記していますが、例えば氏名を入力する行は、17項目あって、該当する箇所に数字を入力するというものです。逆に言うと、氏名を入力して、該当項目に数か所数字を入力すると他の項目は空欄です。ただ、氏名によって、どの項目に数字を入れるかはまちまちなので、17項目をデータベースに転記しています。なので、毎日、380項目すべてに入力しているわけではなく、特に氏名を入力する20行は、空欄の箇所がほとんどということになります。
この20行を集計した結果を21行目に計算させて、なおかつ、データベースにも転記できたらと思っております。

大変扱いずらいものとなってしまっておりますが、どうかよろしくお願いいたします。
(トランプ) 2017/02/06(月) 07:34


 結合セルは大した問題ではありません。
 その為の転記用シートです。
 私からは画面が見えないので、セルの特定がイメージ出来ないと言う欠点はありますが、
 転記用シートで、私が見えなくもトランプさんがご自身で関連付け出来ますので。

 ただ関数に関しては、レイアウトを少し甘く考えていました。
 私の予想を超えてました。
 下の明細はマークシート式の試験問題のような作りになってるんですか?
 最終出力だけなら何の問題もありませんが、データの入力画面としてみれば、相当変な作り方をしてるみたいですね。

 私の職場は化学系なので、結果集計などが多いので殆ど見ないですけど、文系や事務関係の方ですか?
 ワープロ思考の人が多いのかな?
 そう言えば取引先の商社から送ってくる書類もそんな感じです。

 >氏名を入力する行は、17項目あって
 17行?

 >特に氏名を入力する20行は、
 20行???

 う-ん、、、17項目は列方向の事かな?
 以下のようなレイアウトになってるのでしょうか?

        項目1 項目2 〜 項目16 項目17 
 氏名1
 氏名2
  〜
 氏名19
 氏名20

 この氏名は日によって、2人分入力したり、5人分で合ったり、1人だけの日もあると言う事ですか?
 常に1日1人なら20行にしなくても、氏名で検索できるから意味の無い行数になるので、たぶん上記のような使い方なんでしょうね。

 >1 U AM BE BW CO DG DY EQ FI GA
 こう言う飛び飛びの列では数式を組むのに不向きなので、以下のような連続した列になるようDB転記すれば簡単な数式で済みますが、
 1 U V W X Y Z AA AB AC AD

 ただこんな事を書きましたが、今から1つ1つ説明しながら、DBのレイアウトを変えてもらって数式を考えるのは、
 文字だけの会話ではお互いの意図を伝えたり読み取ったりは相当めんどくさいので、私の気力が持ちそうにないので、
 ここまでセル番地など提示して頂いて、お手数おかけしてて申し訳無いんですが、
 転記用シートで指定したセルは、DBへの転記のみの一方通行が出来るようにコードを修正しました。

 ややこしいので前のコードは全て消して、丸ごと以下のに修正して下さい。
 転記用シートのD1に「一方通行」とでも項目ラベルを追加して、A〜C列に数式の入ったセルも追加して頂いて、
 D列に Const OneWay As String = "〇" で指定したのと同じ文字を入れて下さい。、
 (〇を好きな文字に変更して頂いて結構です。文字の両側は""で括って下さい。)
 D列に同じ文字が入っているセルはDBには転記しても、所定書式には読込ませんので数式が消える事はありません。
 もちろんロックしてシート保護をかけて頂いても結構です。

  Option Explicit

 Const sh1Name As String = "入出力"
 Const sh2Name As String = "DB"
 Const sh3Name As String = "転記用"
 Const OneWay As String = "〇"

 Sub Posting_Input()
 '入力後のデータをDBに登録
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim row1 As Long
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh2 = Sheets(sh2Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データの存在チェック
    If sh1.Range(v(2, 2)).Value = "" Then
        MsgBox "日付が未入力なのでDBに登録できません。"
        Exit Sub
    End If
    If WorksheetFunction.CountIf(sh2.Range("B:B"), sh1.Range(v(2, 2))) Then
        If MsgBox("すでにDBに登録されています。" & vbLf & "上書きしますか?", vbYesNo) = vbYes Then
            row1 = WorksheetFunction.Match(sh1.Range(v(2, 2)), sh2.Range("B:B"), 0)
        Else
            Exit Sub
        End If
    Else
        row1 = sh2.Range("B" & Rows.Count).End(xlUp).Row + 1
    End If

    'データの転記
    For i = 2 To UBound(v, 1)
        sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value
    Next i

 End Sub

 Sub Posting_Output()
 'DBのデータを読込
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim row1 As Long
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh2 = Sheets(sh2Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データの存在チェック
    If sh1.Range(v(2, 2)).Value = "" Then
        MsgBox "日付が未入力なので読込できません。"
        Exit Sub
    End If
    If WorksheetFunction.CountIf(sh2.Range("B:B"), sh1.Range(v(2, 2))) Then
        row1 = WorksheetFunction.Match(sh1.Range(v(2, 2)), sh2.Range("B:B"), 0)
    Else
        MsgBox "該当の日付のデータがありません。"
        Exit Sub
    End If

    'データの転記
    For i = 2 To UBound(v, 1)
        If v(i, 4) <> OneWay Then
            sh1.Range(v(i, 2)).Value = sh2.Cells(row1, v(i, 3)).Value
        End If
    Next i

 End Sub

 Sub Data_Clear()
 '表示中データのクリア
    Dim sh1 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データのクリア
    For i = 2 To UBound(v, 1)
        sh1.Range(v(i, 2)).MergeArea.ClearContents
    Next i

 End Sub

(sy) 2017/02/06(月) 23:10


まず、ご返事を見るより先に、遅くに、週の初めのお疲れの中に、ありがとうこざいます。
(トランプ) 2017/02/06(月) 23:14

syさん、本当にありがとうございます。
syさんは、化学系ですか。どおりですごいです。
私はおっしゃるとおり、文系です。
取り急ぎ、途中までの回答です。

以下のようなレイアウトになってるのでしょうか?
そのとおりです。

        項目1 項目2 〜 項目16 項目17 
 氏名1
 氏名2
  〜
 氏名19
 氏名20

 この氏名は日によって、2人分入力したり、5人分で合ったり、1人だけの日もあると言う事ですか?
はい、日によってまちまちです。
(トランプ) 2017/02/06(月) 23:21

凄いです!!
ご指示どおりにコードを修正して、任意の文字を関数部分に付与して、テスト入力したところ、DBには関数部分の集計がきちんと記録されて、かつ、入出力シートの21行目の関数はそのままで消えていません!!
ただただ、凄い!!の言葉しかありません。

私とは次元が違う方が多くここにはいらっしゃるんだと感心しております。

もう少し、聞きたいことがありますので、後ほどここに投稿しておきますので、お時間があるときに目をとおしていただけると有難いです。

本当に有難うございます。
(トランプ) 2017/02/06(月) 23:43


syさん、すいません。

先ほどのテスト入力を一旦クリアして、読込みしたところ、関数が消えてしまいました・・・

(トランプ) 2017/02/06(月) 23:48


 あっ!
 うっかりしてました。。。

 クリアのコードを数式セルを外すのを忘れていました。

 申し訳ありませんm(_)m

 以下クリアコードの修正版です。

 Sub Data_Clear()
 '表示中データのクリア
    Dim sh1 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データのクリア
    For i = 2 To UBound(v, 1)
        If v(i, 4) <> OneWay Then
            sh1.Range(v(i, 2)).MergeArea.ClearContents
        End If
    Next i

 End Sub

(sy) 2017/02/06(月) 23:56


すごーいです!!!!!
登録、読込、クリア見事に入出力シートの関数部分を残しながら、データベースにはしっかりと関数で算出した集計を記録しています。
syさん、本当にありがとうございます。

もう少し、御教授いただきたいことがありますので、明日質問させていただきます。
うれしくて眠れなさそうです。
ありがとうございます。
(トランプ) 2017/02/07(火) 00:12


ありがとうございます。

今度は、シートに保護をかけたいと思っております。
入出力シートはもちろん、DBを基に「月間集計」と「年間集計」というシートも作りました。
入出力シートの手入力する部分のみ保護解除して、シートの保護をかけてみたところ、登録や読込み、クリアする際にエラー表示されてしまいます。
これは、少し前にβさんからご提案いただいようにマクロが必要とのことだと思います。
さらなるご指導をよろしくお願いいたします。
(トランプ) 2017/02/07(火) 18:39


 今帰ってる途中ですが、先にちょっと確認ですけど保護をかけてるシートは入出力シートだけですか?

 転記用シートは保護をかけるとロックを外していてもエラーになります。

(sy) 2017/02/07(火) 20:35


 後エラーが出た時にメッセージは何て書いてますか?

(sy) 2017/02/07(火) 20:41


syさん、どうもありがとうございます。

早速です。
入出力、月間集計、年間集計の3つに保護をかけてみて、エラーを確認しました。
入出力シートで登録する分にはエラーは出ません。
クリアすると、「実行時エラー1004」がでます。変更するには保護を解除してくださいというものです。
読込みもクリアと同様のエラーです。
(トランプ) 2017/02/07(火) 21:57


 まず初めに、
 >これは、少し前にβさんからご提案いただいようにマクロが必要とのことだと思います。
 今の時点では何が原因かは全く分かりません。
 初めから思い込みで行動すると、原因究明から遠ざかる可能性がありますので、決めつけないで下さい。
 仮にその処置を施して上手くいったとしても、本当の原因を理解して解決したのか、
 たまたま結果オーライだったのかでは、次に似たような問題があった時に、結果オーライの場合、
 結局また自分で解決できずに同じような質問ばかりする、めんどくさい質問者になってしまいますよ。

 エラーメッセージは以下では無いですか?

 変更しようとしているセルやグラフは保護されているシート上にあります。
 変更するには、[校閲] タブの [シート保護の解除] をクリックします (パスワードの入力が必要な場合もあります)。

 上記メッセージと同じ文面でしたら、以下のコードを実行して下さい。
 転記用シートのF列に文字が表示されたら、その行のB列のセル番地と同じ、入出力シートのセルのロックを外し忘れています。

 上記メッセージと違っていたり、F列に文字が表示されなかったら、別に原因があるのでまた別のコードをアップします。

 Sub chk_sum()
    Dim sh1 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim i As Long

    Set sh1 = Sheets(sh1Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    For i = 2 To UBound(v, 1)
        If v(i, 4) <> OneWay Then
            If sh1.Range(v(i, 2)).Locked = True Then
                sh3.Cells(i, "F").Value = "ロックされています"
            End If
        End If
    Next i

 End Sub

(sy) 2017/02/07(火) 22:17


エラーメッセージはまさにsyさんのおっしゃるものでした。
また、転記用シートのF列には文字は出ませんでしたので、
コードを実行したいと思いますが、これは、どこに記録すればよいでしょうか?
(トランプ) 2017/02/07(火) 22:27

 他のコードと同じ標準モジュールに貼付けて実行して下さい。
 実行後にF列に「ロックされています」の文字が表示されないか確認して教えて下さい。

(sy) 2017/02/07(火) 22:31


実行時エラーが表示されました。1004です。
先ほど、syさんが言われたエラーメッセージです。
(トランプ) 2017/02/07(火) 22:35

 え!?

 Sub chk_sum() これがエラーになったんですか!!!???

 因みにどの行で止まってるか、デバッグをクリックしてVBE画面の黄色になってる行を、
 コピペでここに貼付けていただけませんか。

(sy) 2017/02/07(火) 22:41


v = sh3.Range("A1").CurrentRegion.Value
この行が黄色になっています。
(トランプ) 2017/02/07(火) 22:45

 >転記用シートは保護をかけるとロックを外していてもエラーになります。

 もしかしてこれ(上記)に該当しますか?

 でも、DBに記録する時はエラーは出ないんですよね?

 >入出力、月間集計、年間集計の3つに保護をかけてみて、エラーを確認しました。 
 >入出力シートで登録する分にはエラーは出ません。

 転記用シートに保護をかけると、全てのコードでエラーになるんですが?

(sy) 2017/02/07(火) 22:50


 エラーメッセージはもしかして以下じゃ無いですか?

 保護されたシートに対して、このコマンドは使用できません。このコマンドを使用するには、
 まずシートの保護を解除してください ([校閲] タブの [変更] グループにある [シート保護の解除] 
 をクリックします)。パスワードの入力を要求されることもあります。

(sy) 2017/02/07(火) 22:54


はい、そのとおりです。
現在は、すべてのシートに保護をかけている状態です。
(トランプ) 2017/02/07(火) 22:59

すみません。
転記用シートに保護をかけると、おっしゃるように登録、読込、クリアすべてでエラーがでます。
実行時エラー1004です。
(トランプ) 2017/02/07(火) 23:04

転記用シートもDBシートも保護を外して、入出力のみ保護をかけると、
読込、クリアでエラーがでます。登録はできます。
デバックは以下のとおりです。
読込→sh1.Range(v(i, 2)).Value = sh2.Cells(row1, v(i, 3)).Value
クリア→sh1.Range(v(i, 2)).MergeArea.ClearContents
(トランプ) 2017/02/07(火) 23:15

 えっと、、、

 >入出力、月間集計、年間集計の3つに保護をかけてみて、エラーを確認しました。 
 >入出力シートで登録する分にはエラーは出ません。
 >(トランプ) 2017/02/07(火) 21:57

 この検証した時は転記用シートに保護は当然していませんよね?
 してたら登録でもエラーになるので。

 なのに何で私がそのすぐ後(sy) 2017/02/07(火) 22:17に Sub chk_sum() 
 の実施をお願いした時には、違う設定になってるんですか?

 一つ一つ可能性の高い処から検証しています。

 会話の途中で見えない所で条件をコロコロ変えられると、何が原因か分からなくなるので、
 こちらがお願いした事だけを実行して頂けませんか?

 それともこちらが見えないのを良いことに揶揄われてるのかな?

 もしそう言うつもりでしたら、私もそんな不愉快な事に付き合うほど暇でもないし、仕事も忙しいから疲れてます。
 トランプさんがこのままでは困るだろうと思うから、何とかしようと頑張ってます。
 裏でコロコロ設定を変えて漫才のように同じ事を繰り返させられるなら、これで私は降りさせて頂きます。

(sy) 2017/02/07(火) 23:21


いいえ、そういうつもりではありません。
失礼しました。
また明日に、再度指示された内容で検証をして返事をさせていただきます。
どうぞよろしくお願いします。
(トランプ) 2017/02/07(火) 23:24

 もしかしたら私からはこれが最後になるかも知れませんが、
 (まぁ正しく以下の通りにすればこれで解決すると思ってますけど)
 以下の設定で検証して下さい。

 1、転記用シートだけは保護を解除して下さい。(他のシートはどちらでも良いです)
 2、(sy) 2017/02/07(火) 22:17にアップした Sub chk_sum() のコードを、
    他のコードと同じ標準モジュールに貼付けて、実行して下さい。
 3、転記用シートのF列に文字が表示されるか確認して下さい。

 文字が表示されたら、原因は入出力シートのそのセル番地のロックを外し忘れているだけです。

 もう一つは、転記用シートにも保護をかけたいんですか?
 こちらは確かに保護をかけるとエラーになります。
 保護をかけたいのなら、全てのコードの
    v = sh3.Range("A1").CurrentRegion.Value
 を
    v = sh3.Range("A1", sh3.Range("B" & Rows.Count).End(xlUp).Offset(, 2)).Value
 に変えて下さい。

 今日の会話の内容からして、他にもある可能性は0ではありませんが、おそらくこの2件で解決すると思います。

(sy) 2017/02/07(火) 23:55


おはようございます。

syさんからご指示いただいたとおりに、1〜3を確認・実行しました。
すると、F列に文字が表示されたため、そのセル番地のロックを確認したところ、おっしゃるとおりロックを外し忘れていました。このセルは手入力箇所なのにロックをしてしまっていました。
その後、ロックを解除して、入出力シートに保護をかけて、登録、読込、クリアしてもエラーは表示されませんでした。転記用は非表示するつもりですので、保護をかけるつもりはありません。

おかげさまで、希望どおりのものを作成することができました。
今回、syさんに教えていただいことを自分なりに勉強して、他の様式に活かしていきたいと思います。
ありがとうございました。
(トランプ) 2017/02/08(水) 07:26


syさん、もう一点教えてください。
現在のDBの他にもう一つDB2なるシートを追加することは可能でしょうか?
その場合、マクロの操作が必要になるのでしょうか?
よろしくお願いします。
(トランプ) 2017/02/08(水) 08:18

syさん、今一度ご教授願いたいことがあります。

(sy) 2017/02/07(火) 23:55に転記用シートに保護をかける場合の対処法をご教授頂きましたが、
DBシートに保護をかける場合の対処法もご教授頂ければ有難いです。
一度、DBシートに保護をかけてみたところ、以下のデバックが表示されました。
 sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value
この部分の修正が必要になるということだと思います。

どうぞよろしくお願いいたします。
(トランプ) 2017/02/09(木) 17:23


保護を解除しないと、手作業でもマクロでもセルを操作できませんよ?
保護の仕方、解除の仕方は、マクロの自動記録を使えば簡単に命令が判ります。試してみてください。

それと、DB2シートを作るのは勝手ですが、それはどのように使用するのでしょう? ご自身でコーディングすれば、何だってできますよ。
自分でやる気があるかどうかだけの問題です。「自分ではできないから作ってもらう」ではありません。自分でできるまでやるのです。
(???) 2017/02/09(木) 17:39


確かにそうなんですが。
よろしくお願い申し上げます。
(トランプ) 2017/02/09(木) 19:38

様々な方々にお教えいただいているのですが、
DBシートに保護をかけたいのですが、うまくいきません。
どうぞよろしくお願いします。
(トランプ) 2017/03/04(土) 12:56

 何がしたいんですか?

 DBシートに計算式を入れて集計とかしてるんですか?
 なら計算式のセルのみロックをかけて、それ以外の全てのセルのロックを外せば良いだけでは?

 それともDBシートは全ての値を編集不可にしたいんですか?
 全て編集不可にしたいなら、シートを非表示にした方が良いのでは?

 ???さんから、
 >保護の仕方、解除の仕方は、マクロの自動記録を使えば簡単に命令が判ります。
 と、これ以上ないくらい的確なアドバイスがあるのに、それに対しての反応が無いですけど、
 試されてないんですか?

 作成依頼だけで自分で努力しないと、大半の人は相手にしてくれないですよ。
 私も初めに断っています。

 まずは???さんの方法を試して、ここに作りかけでも良いのでコードをアップされてはどうでしょう。
 そうすれば沢山の方からアドバイスを頂けると思います。

 それと以前にも何も分からない内から決めつけないで下さいと申しましたが、
 >これは、少し前にβさんからご提案いただいようにマクロが必要とのことだと思います。
 この時もそうですけど、以下のような
 >sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value 
 >この部分の修正が必要になるということだと思います。 
 まるで初めから私のコードに問題があるみたいな発言は不愉快です。
 1つ目は単なるトランプさんのロック忘れの凡ミスですし、2つ目も仕様の後出しが根本原因です。
 ご自身の質問の仕方や回答者の回答を無視したりして解決出来ないのを、
 回答者のコードのせいにするのは止めて下さい。

(sy) 2017/03/04(土) 14:17


DBシートに保護をかけて、編集不可にして、あくまで、入出力シートで登録したデータがきちんと蓄積されていることを確認するためです。
私も含め初心者なので、こんなためにDBシートを表示させておきたいのです。
すみません。
(トランプ) 2017/03/04(土) 14:25

すみません。
そんなご回答いただいく方にはとても感謝しております。
申し訳ありません。
(トランプ) 2017/03/04(土) 14:28

 >DBシートに保護をかけて、編集不可にして、あくまで、入出力シートで登録したデータがきちんと
 >蓄積されていることを確認するためです。

 ならば先ほども書いたように、???さんの
 >保護の仕方、解除の仕方は、マクロの自動記録を使えば簡単に命令が判ります。
 これを試されたんですか?
 何も反応が無いですよと申し上げています。

 やってみて出来なかったのか、言葉の意味すら分からないのか、途中までは出来てるけど分からない部分がある
 など出来ない理由は様々ですが、出来ないならどう言う理由で出来ない、分からない部分は何処かを聞けば、皆さん答えてくれます。

 反応が無いのは試しもしていない(努力する気は全くない)としか受け取れません。
 努力に対しては微力ながらお手伝いしようと言う思いはありますが、
 努力が全く見られない時(今がそうです)は、答える気にはなりません。

(sy) 2017/03/04(土) 14:48


みなさんに不愉快な思いをさせてしまい、申し訳ありません。

???さんのご指導どおり、マクロの記録で以下のようなマクロを記録しました。
これは、DBシートに保護をかけるマクロになるのだと思います。
これは、???さんがご指導いただいた時点で、試してはおりました。しかしながら、これをどのようにsyさんにご指導いただいた構文の中に組み込めばよいのか、しかも、DBシートに保護をかけると、syさんが最初からおっしゃるように、入出力シートで登録等ができなくなってしまいます。自分なりにはインターネットで調べてみたりしましたが、私の理解の範囲での情報を得ることが出来ず、そのままとしてしまい、今に至っております。
すべて私の姿勢に問題があることはご指摘のとおり承知しております。知識のない私には、どのように進めればよいのかすらわからない状況で放置してしまっていました。ただ、これまでいろいろと教えていただいたことには本当に感謝しかありません。syさんから教えていただいたコードにけちをつけるなど、そんなことは毛頭ありませんし、人のアイデアにけちをつけるような私のレベルでもありません。

ただ、私が最後にやりたいと思っていることは、DBシートを含むすべてのシートに保護をかけて、ブックにも保護をかけても、入出力シートで登録、読込、クリアができる仕様に到達できればと思っております。
不愉快な思いをさせてしまいましたこと、深くお詫び申し上げます。

Sub Macro1()
'
' Macro1 Macro
'

'

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
(トランプ) 2017/03/04(土) 15:29

Sub Macro1()
'
' Macro1 Macro
'

'

    ActiveSheet.Unprotect
End Sub

 マクロ記録を使って、保護の解除の操作をすれば、上記のような記録が取れる筈です。

 保護の操作だけして出来ないと判断して、解除の操作を記録しなかったんですね。
 自分だけで判断して、決めつけた結果がこれです。

 トランプさんが記録した保護の方のコードが以下になりますけど、
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 2つを組み合わせれば解除してから保護すると言う流れになりますね。
    ActiveSheet.Unprotect
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 ならこの間に書かれたコードは解除された状態で実行されると言う事が分かると思います。
    ActiveSheet.Unprotect
    途中のコード(上で解除されてから実行して、実行後に下で保護される)
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 後一つ、ActiveSheetの部分、このままでは現在アクティブなシートが対象になってしまいますので、
 DBシートが表示されていなければエラーになります。

 そこでDBシートに確実に操作できるように、シートの指定をしてあげます。
 そのシートの指定はDBシートは私のコードではsh2になります。
 それらを手当してやれば以下のようになります。

    sh2.Unprotect
    途中のコード
    sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

(sy) 2017/03/04(土) 17:54


syさん、ありがとうございます。
まずもってのやり方みたいものが少しわかったような気がします。
そうすると、以下のコードを

  sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

この保護のマクロ部分に入れてあげる形なのでしょうか?

 Sub chk_sum()
    Dim sh1 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim i As Long

    Set sh1 = Sheets(sh1Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    For i = 2 To UBound(v, 1)
        If v(i, 4) <> OneWay Then
            If sh1.Range(v(i, 2)).Locked = True Then
                sh3.Cells(i, "F").Value = "ロックされています"
            End If
        End If
    Next i

 End Sub
(トランプ) 2017/03/04(土) 18:10

 Sub chk_sum()
 このコードは、エラーになる原因のロックされたセルを発見する為だけのコードです。
 もう必要ありません。
 しかも入力用シートのセルを検索しているのでDBシートとなんの関係もありません。

 DBにデータを記録する時にエラーになるんじゃ無いんですか?
 Sub Posting_Input() このコードでエラーになってるんじゃ無いんですか?
 ならその時のコードに手当すれば良いと言う事が分かりますね。

 後はまずご自身で実際に手当してみて、上手く行かなければ、その時点でのコードを掲載して
 再質問するようにして下さい。
 そうじゃないと現在の状況を把握するのに、また無駄な時間を取られてしまいます。

(sy) 2017/03/04(土) 18:33


はい、ありがとうございます。

早速、ご指摘いただいた箇所 Sub Posting_Input() 部分の【】部分を
以下に置き換えてみました。
 sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

ところが、エラーそのものは出ないのですが、DBシートに書き込んでくれません。

 Sub Posting_Input()
 '入力後のデータをデータベースに登録
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim row1 As Long
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh2 = Sheets(sh2Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データの存在チェック
    If sh1.Range(v(2, 2)).Value = "" Then
        MsgBox "日付が未入力なのでデータベースに登録できません。"
        Exit Sub
    End If
    If WorksheetFunction.CountIf(sh2.Range("A:A"), sh1.Range(v(2, 2))) Then
        If MsgBox("すでにデータベースに登録されています。" & vbLf & "上書きしますか?", vbYesNo) = vbYes Then
            row1 = WorksheetFunction.Match(sh1.Range(v(2, 2)), sh2.Range("A:A"), 0)
        Else
            Exit Sub
        End If
    Else
        row1 = sh2.Range("B" & Rows.Count).End(xlUp).Row + 1
    End If

    'データの転記
    For i = 2 To UBound(v, 1)
        【sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value】
    Next i

 End Sub
(トランプ) 2017/03/04(土) 19:10

 本当に私のコメントを読んでるんですか?

    sh2.Unprotect
    途中のコード
    sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 現在のコードを書き換えるような説明になってますか?
 しかも何で以下の保護解除コードは無視されるんですか?
    sh2.Unprotect

 エラーになるのは保護がかかった状態で転記するからですよね?
 なら解除した後に実行すればエラーにならないし、最後にまた保護すれば良いと言う事も分かりますよね?

 まずこの文面が分かるかどうかお答えください。
 トランプさんがどう言う解釈をしてるかトランプさんの言葉で説明して下さい。
 会話が成立していないと、何を説明しても無駄になりそうなので。

(sy) 2017/03/04(土) 21:04


はい、わかります。
しかし、ご指示どおりできておらず、しかも肝心なコードを抜かしていて申し訳ありません。
本当にすいません。
(トランプ) 2017/03/04(土) 22:07

 ご理解は頂けたと思いますので、もう一度コードの何処に追加したら良いかご自身で考えて手当してみて下さい。
 そして出来たコードを掲載して下さい。

(sy) 2017/03/04(土) 22:13


ありがとうございます。
以下の→【】部分でしょうか?
保護を外してから書き込んで再保護するという流れからこの場所かと考えたのですが、いかがでしょうか?
宜しくお願いします。

 Sub Posting_Input()
 '入力後のデータをデータベースに登録
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim row1 As Long
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh2 = Sheets(sh2Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データの存在チェック
    If sh1.Range(v(2, 2)).Value = "" Then
        MsgBox "日付が未入力なのでデータベースに登録できません。"
        Exit Sub

    End If
    If WorksheetFunction.CountIf(sh2.Range("A:A"), sh1.Range(v(2, 2))) Then
        If MsgBox("すでにデータベースに登録されています。" & vbLf & "上書きしますか?", vbYesNo) = vbYes Then
            row1 = WorksheetFunction.Match(sh1.Range(v(2, 2)), sh2.Range("A:A"), 0)
        Else
            Exit Sub
        End If
    Else
        row1 = sh2.Range("B" & Rows.Count).End(xlUp).Row + 1
    End If

    'データの転記
→   【sh2.Unprotect】

    For i = 2 To UBound(v, 1)
→      【sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True】
    Next i

 End Sub
(トランプ) 2017/03/04(土) 22:40

 すいません。

 私には無理と思いました。

 >現在のコードを書き換えるような説明になってますか?

 私がこう書いているにも関わらず、

 >   For i = 2 To UBound(v, 1)
 >→      【sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True】
 >   Next i

 これは書き換えでは無いんですか?

 あれだけ言ってるので私の意見を聞いてるつもりなんでしょうが、
 私の説明を一切無視してご自身の頭の中の考えだけが優先される方なんですね。
 もう会話を成立させる事が出来そうにないので、今回は答えを書きます。
 次に何か発生したら、お金を払って作ってもらって下さい。

  Sub Posting_Input()
 '入力後のデータをDBに登録
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim v As Variant
    Dim row1 As Long
    Dim i As Long

    '変数の設定
    Set sh1 = Sheets(sh1Name)
    Set sh2 = Sheets(sh2Name)
    Set sh3 = Sheets(sh3Name)
    v = sh3.Range("A1").CurrentRegion.Value

    'データの存在チェック
    If sh1.Range(v(2, 2)).Value = "" Then
        MsgBox "日付が未入力なのでDBに登録できません。"
        Exit Sub
    End If
    If WorksheetFunction.CountIf(sh2.Range("B:B"), sh1.Range(v(2, 2))) Then
        If MsgBox("すでにDBに登録されています。" & vbLf & "上書きしますか?", vbYesNo) = vbYes Then
            row1 = WorksheetFunction.Match(sh1.Range(v(2, 2)), sh2.Range("B:B"), 0)
        Else
            Exit Sub
        End If
    Else
        row1 = sh2.Range("B" & Rows.Count).End(xlUp).Row + 1
    End If

    'データの転記
    sh2.Unprotect
    For i = 2 To UBound(v, 1)
        sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value
    Next i
    sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 End Sub
(sy) 2017/03/05(日) 04:10

あれっ。昨晩確認したとき、最初こうしていませんでしたか?
エラーがでたので、書き換えてしまったのでしょうか。

 sh2.Unprotect   '★1
 For i = 2 To UBound(v, 1)
    sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value
    sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True   '★2
 Next i

なぜ、エラーになっちゃうのか。
せっかくなので理解してください。

以下が転記している部分というのは、
何となくでも、わかりますよね。

 For i = 2 To UBound(v, 1)
    sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value
 Next i

sh2のCells(row1, v(i, 3))の値を sh1のRange(v(i, 2))の値にする
つまり転記するという意味です。
転記したいセルは、複数あるのだから
For〜next構文で、繰り返し転記を行っているです。

で、シート保護解除(★1の部分です)と
シート保護(★2)のコードを追加したわけですが

★2が、For〜nextの中にあるので、せっかく解除したのに
1回転記しただけで、すぐにまた保護されてしまいます。
すると、2回めの転記でエラーになってしまうのです。

 1)シート保護解除
 2)1個目のセルを転記
  3)シート保護
4)2個目のセルを転記  ←ここでエラー
 5)シート保護
 6)3個目のセルを転記
 7)以下、同様に繰り返し

なので、シート保護のコード(★2)は

For〜nextで、すべてのセルを転記してから
実行するようにすればよかったのです。

 sh2.Unprotect   '★1
 For i = 2 To UBound(v, 1)
    sh2.Cells(row1, v(i, 3)).Value = sh1.Range(v(i, 2)).Value
 Next i
 sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True   '★2

文章にすると、こんな流れです。

 1)シート保護解除
 2)1個目のセルを転記
 3)2個目のセルを転記
 4)3個目のセルを転記
 5)以下、同様に繰り返し
 6)最後に、シート保護

新たな要望を出す前に、今のマクロの意味を理解することが先かもしれません。

(マナ) 2017/03/05(日) 12:07


ご指示通りの対応ができず、申し訳ありません。
また、マナさんもこのような私の理解の乏しい私にご教授いただき、ありがとうございます。
SYさん、本当にありがとうございました。

(トランプ) 2017/03/05(日) 15:18


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.