見積書の表があります。
A1は欄外の空白セル、B1は内容欄、C1は数量欄、D1は単価欄、E1は金額欄です。
B2からE2は値を記入する欄としE2には数量*単価の関数を埋め込みます。B3からE3までも形はそれと同じにし、E4が合計の値を記入する欄で関数は=SUM(E2:E3)です。その上で第3行を「表示しない」とします。
必要なだけの行を随時追加してゆく為に、苦労して次のような動作をするマクロ記録を作りました。
A4にフォームのボタンを置き、A4セルをクリックした後にそのボタンをクリックすれば非表示になっている直ぐ上の行を表示させてコピーし、その場に表示行として新規挿入します。
つまりE4は繰り下がりE5になりますが、自動的に関数は=SUM(E2:E4)となります。
そして新しく第4行を非表示としてやります。この繰り返しです。
記録されたマクロを見ると下のようになっています。
Sub 増行()
'
' 増行 Macro
' マクロ記録日 : ユーザー名 :
'
'
ActiveCell.Select
ActiveCell.Offset(-1, 0).Range("A1:E3").Select
Selection.EntireRow.Hidden = False
ActiveCell.Offset(0, 0).Range("A1:E1").Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Range("A1:E1").Select
Selection.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
このマクロはちゃんと機能するのですが、問題はこのシートの値を記入する場所以外を
保護しようとすれば「実行時エラー'1004' RangeクラスのInsertメソッドが失敗しました」
と表示されます。
どうすればこれを避けて保護する事が出来るでしょうか。
よろしくお願いします。
なお私は超初心者なので自分でVBを記述したり意味を読み取る事はほとんど出来ません。
No.3ベストアンサー
- 回答日時:
#1です。
空行を挿入して、関数があるセルだけコピーすれば希望になると思います。
簡単に説明も入れておきましたので参考にして下さい。
Sub Test1()
Dim myRange As Range
'アクティブシートに対して
With ActiveSheet
'シート保護を解除
.Unprotect
'セル E65536 を選択してからCtrlを押しながら↑キーを押下
'で選択されるセルを変数 myRangeにセット(ようするにE列最終行)
Set myRange = .Range("E65536").End(xlUp)
'変数 myRange が含まれる行を選んで1行挿入と同じ意味
myRange.EntireRow.Insert
'変数 myRange から2つ上のセルをコピーして、変数 myRange から
'1つ上のセルに転送する
myRange.Offset(-2, 0).Copy Destination:=myRange.Offset(-1, 0)
'変数 myRange に SUM関数を再セット
myRange.Value = "=SUM(E2:" & myRange.Offset(-1, 0).Address(0, 0) & ")"
'シートを保護
.Protect
End With
End Sub
No.2
- 回答日時:
#1です、出張中でしたので回答が遅くなりましたが、、、
> その行の合計欄が満たされた事をきっかけにしてシートの保護
> を回復してやるという手順なると思いますが
おっしゃる意味が良く解りません、、、
セルの書式設定で関数が入っているセルだけロックして、入力欄はロックを外して
シートを保護すれば良いだけに思います。
(例で言えば、E列だけセルをロックしてシート保護を掛ける)
こちらの解釈でサンプルを書きました。条件は下記の通りです。
B2:D2が入力用の欄(セルのロックはしない)
E2は関数 =C2*D2 を入力(セルをロック)
E3は関数 =SUM(E2:E2) を入力(セルをロック)
シートを 123 と言うパスワードで保護する
質問時の3行目(非表示行)を無くした状態で動くようにしたつもりです。
アクティブセルの位置も関係ありません。
Sub Test()
Dim myRange As Range
With ActiveSheet
.Unprotect "123"
Set myRange = .Range("E65536").End(xlUp).Offset(-1, 0)
myRange.EntireRow.Copy
myRange.EntireRow.Insert
myRange.Offset(1, 0).Value = "=SUM(E2:" & myRange.Address(0, 0) & ")"
.Protect "123"
End With
End Sub
この回答への補足
二度目のご回答ありがとうございました。
ご回答にあるサンプル表を作り、頂いた記述をコピペして実行して見ました。
アクティブセルはどこにあっても関係ありませんね、すごいです。一瞬びっくりした=SUM(E2:E2)の変化態様も解りました。
しかし問題が一つあって疑問の解決に至っておりません。
私が非表示行を置いた意図をしっかりご説明しなかったのが悪かったのですが;
B2からD2までのセルに書き込むとします。例えば内容欄のB2=鉛筆、数量欄のC2=12、単価欄のD2=20と打ち込めば金額欄E2には240と表示されます。そこで次行の見積りを記入する為に空行(関数を打ち込んだセルを含む)を生成させようとA3にあるボタンをクリックすると、打ち込んだばかりの第2行が第3行にコピペされてしまい、それらのセルの値を削除しながら新規の値を打ち込むことになってしまいます。
実は非表示行(記入不可能な)を用意したのはこの問題を回避する為だったのです。
私には頂いた記述を改変して追加記述する力が未だありませんので、「関数を埋め込んだセルはロックされたままで、セルの値が記入されていない新しい空白行を合計行の直ぐ上に生成する」にはどうすればよいか、をSub~End Subの全体記述でお教え頂けると嬉しいです。シートのパスワードは不要です。
なお、お教え頂いた5行目の("E65536")は何を意味するのでしょうか。
実際に作りたい見積書はもうちょっと項目欄の数が多いので、この意味を知る事はポイントかなと思います。
何度も本当に申訳けありませんが、どうぞよろしくお願いいたします。
No.1
- 回答日時:
シートを保護しているって事ならばエラーは当然ですよね。
マクロ記録でシート保護と解除を記録して、一番最初の行と
一番最後の行にコピペしてやれば良いのでは?
この回答への補足
ご回答有難うございます。
保護と解除をマクロに記録するのは良いアイデアですね、気付きませんでした。但しその時には増やした行への書き込みを終え、その行の合計欄が満たされた事をきっかけにしてシートの保護を回復してやるという手順になると思いますが・・・VBを独力で考える力は今の私にありません。
1)関数を埋め込んだセルは常に改変不可にして置きたい
2)関数を埋め込んだセルを含む行は随時増やしたい
この二つを同時に満たす事は出来るのでしょうか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) データのある範囲を選択するVBAについて 2 2022/09/03 00:20
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) ②Excel 簡単にシートコピーしたら前日の残高と日付を変更させたい→マクロの記録でエラーが出ます 8 2022/07/16 20:40
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) Excelのマクロについて 2 2022/06/14 03:38
- Excel(エクセル) 並べ替え、ソートの構文がわからない。 お世話になります。VBA超初心者です。 エクセルでワークシート 2 2023/06/28 21:00
- Visual Basic(VBA) マクロで最終行を取得してコピーしたい 3 2022/04/06 19:07
- Visual Basic(VBA) 2つのシートの任意のセルの番号が一致したら、一致した行をコピーする VBA 2 2023/06/19 20:48
- Excel(エクセル) Excelでnullになるような式のセルをマクロで空白行と認識させるにはどうすればいいですか? 3 2023/03/13 13:42
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/03/07 14:05
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【Excel VBA】指定行以降をクリ...
-
ExcelVBAを使って、値...
-
Excelで指定した日付から過去の...
-
特定のセルが空白だったら、そ...
-
EXCELで変数をペーストしたい
-
【Excel】指定したセルの名前で...
-
ExcelのVBAで数字と文字列をマ...
-
i=cells(Rows.Count, 1)とi=cel...
-
screenupdatingが機能しなくて...
-
Excel VBA、 別ブックの最終行...
-
Excel vbaで特定の文字以外が入...
-
【VBA】指定したセルと同じ値で...
-
EXCEL VBA 画面のロックについて
-
実行時エラー438 オブジェクト...
-
台形面積計算式の表示等
-
DataGridViewの各セル幅を自由...
-
セル色なしの行一括削除
-
Excle VBA Findメソッドについて
-
TODAY()で設定したセルの日付...
-
VBA実行後に元のセルに戻りたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ExcelVBAを使って、値...
-
特定のセルが空白だったら、そ...
-
i=cells(Rows.Count, 1)とi=cel...
-
【Excel VBA】指定行以降をクリ...
-
Excelで指定した日付から過去の...
-
【Excel】指定したセルの名前で...
-
Excel vbaで特定の文字以外が入...
-
特定の文字を条件に行挿入とそ...
-
TODAY()で設定したセルの日付...
-
screenupdatingが機能しなくて...
-
Excelのプルダウンで2列分の情...
-
EXCELで変数をペーストしたい
-
連続する複数のセル値がすべて0...
-
Excel VBA、 別ブックの最終行...
-
VBAを使用した時間管理
-
エクセルVBAでコピーして順...
-
セル色なしの行一括削除
-
【EXCEL VBA】Range("A:A").Fi...
-
VBA コピーして次の値まで貼り...
-
VBA初心者です。結合セルを保持...
おすすめ情報