[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ﾏｸﾛによるﾃﾞｰﾀ追加用行挿入での罫線』（あらくま）
次のような会計簿を作成しています。 以前作成した（数行にわたる入力行と最終行に合計行を用意した表をｼｰﾄに用意）ものと とは違って、入力行を追加しながら入力作業をしていくものを考えました。 この日計表をもとに費目毎に抽出作業をしながら、仕訳表を作成、印刷しようと考えています。 さらに、予算案や決算報告票にも連動させていこうとも考えています。
A B C D E F G H I
1 ○年度○○会計日計簿
2 領収書№ 日付 費目1 費目2 収支区別ｺｰﾄﾞ 摘要 収入金額 支出金額 差引残高
3 1/1 繰越 200 前年より繰越 100000 100000
↑ ↑
=IF(C3="","",VLOOKUP(C3,Sheet2!P14:Q22,2,FALSE)) =G3
4 1 2/3 需要費 100 か 50000 50000
↑
=IF(AND(G4="",H4=""),"",I3+G4-H4)
5 2 4/7 渉外費 100 み 20000 30000
6 5/9 会費 200 た 60000 90000
7 （合計行） 160000 70000 90000
↑ ↑ ↑
=SUM(G3:G6) =SUM(H3:H6) =G7-H7
日計簿の表（Sheet日計簿）がおおよそできあがったので、入力行を追加（挿入）するﾏｸﾛを 次のように作成しました。入力行の追加及びその行の入力規則や相対参照を使った式は上手 くｺﾋﾟｰされたのですが、上部横罫線と内側縦罫線の一部が上手くｺﾋﾟｰされませんでした。 ちなみに、追加行と合計行との間の罫線は二重罫線としておきたいです。 下記、ﾏｸﾛ記述をどのように訂正すれば希望どおりの動作となるのでしょうか。 どなたか、ご教授願えませんでしょうか。
Sub insertLine()
Dim lastRow As Long '最終行格納変数
lastRow = Range("C" & Rows.Count).End(xlUp).Row 'C列入力セル最終行(X)獲得
Rows(lastRow + 1).Insert Shift:=xlDown 'C列入力セル最終行の後に1行挿入
Rows(lastRow).Copy '前出X行を取込
Rows(lastRow + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats '挿入行に複写
Application.CutCopyMode = False '解除
Range("A" & lastRow + 1, "D" & lastRow + 1).ClearContents '挿入行の入力データ削除
Range("F" & lastRow + 1, "H" & lastRow + 1).ClearContents '挿入行の入力データ削除
End Sub
[Excel2003,WindowsXP]
私がやるなら、下記のようにします。 マクロを使わない案です。 3行目から99行目までは書式設定、入力規則、計算式を入力済みにしておく。 100行目は合計行とし、合計行の書式設定、計算式を入力しておく。 3行目から入力を順次行う。 必要に応じてオートフィルタを設定し、I列で「空白以外のセル」を抽出する。 抽出した状態で印刷などをする。 あまり広範囲の書式などを用意するとオートフィルタ時に時間がかかったり、 エクセルが落ちたりするので、データがあまり多いとお勧めできませんが。 (みやほりん)(-_∂)b
ゆっくり書いていたら、みやほりんさんから書き込みがあったのですが、そのままアップさせていただきます。
こんにちは。かみちゃん です。
> この日計表をもとに費目毎に抽出作業をしながら、仕訳表を作成、印刷しようと考えています。 > さらに、予算案や決算報告票にも連動させていこうとも考えています。
私も同じような仕事をしています。
> 追加行と合計行との間の罫線は二重罫線としておきたいです。
最終行の下部（xlEdgeBottom）の罫線を二重にするのではなく、 合計行の上部（xlEdgeTop）の罫線を二重にしてみてはいかがですか？
また、マクロ実行時のC列の最終行をコピーし、 その次の行に「コピーしたセルの挿入」をしてもいいと思います。
なお、以下のコードは、そのような手順にして、罫線を引きなおしています。 ただし、二重罫線を引く行（最終行の下部ではなく、合計行の上部）を変えれば罫線を引きなおす必要はないようです。
Sub insertLine2() Dim lastRow As Long '最終行格納変数
lastRow = Range("C" & Rows.Count).End(xlUp).Row 'C列入力セル最終行(X)獲得
Rows(lastRow).Copy 'C列入力セル最終行をコピーして
Rows(lastRow + 1).Insert Shift:=xlDown '次の行にコピーした行の挿入
With Rows(lastRow + 1).Resize(, 9) 'コピー先の行のA列～I列までの罫線を引きなおし
.Borders(xlEdgeTop).LineStyle = xlDot
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With
Rows(lastRow + 1).Range("A1:D1,F1:H1").ClearContents '挿入行の入力データ削除
End Sub
(かみちゃん) 2009/08/08 16:15
みやほりんさん、かみちゃんさん、ありがとうございました。
かみちゃんさんのﾏｸﾛですが、大変勉強になりました。 ところが、問題発生？発見？ これは、小生のものでも発生していたのですが。 それは、行挿入後の合計行のG列ｾﾙ及びH列ｾﾙの数式が変化しないのです。 相対参照を考えていたので、=SUM(G3:G6)が=SUM(G3:G7)、=SUM(H3:H6)が =SUM(H3:H7)のように変化すると思っていたのですが。 行を挿入すると同時に計算対象範囲終了ｾﾙの行が1増加するようにする 方法はないものでしょうか。 ちなみに、I列ｾﾙの式、=G7-H7はちゃんと=G8-H8と1増加した形になって います。 （あらくま）
こんにちは。かみちゃん です。
> 行挿入後の合計行のG列ｾﾙ及びH列ｾﾙの数式が変化しないのです
以下のコードを最後に追加するといいかと思います。
Rows(lastRow + 2).Range("G1:H1").FormulaR1C1 = "=SUM(R3C:R[-1]C)"
(かみちゃん) 2009/08/08 21:48
最終行をコピーして 【同じ行】に挿入貼り付けをし 下側のデータ部分を削除 と言う考え方をすれば良いと思います。
かみちゃんさんが書いておられますが ＞最終行の下部ではなく、合計行の上部 のみ二重線にしておけば、罫線を引きなおす必要がありません。
最初にご提示のサンプルで言うと 6行目までの範囲は上下に一本罫線を引く 7行目だけ選択し、範囲の上側に二重線を引く
うっかり6行目の下側に二重線が引かれてたら困るから やっぱり罫線はマクロで引きなおしたい ってなら、入れておけばよいと思いますが。
(HANA)
こんにちは。かみちゃん です。
To,HANAさん
私が勘違いしているのかもしれないので、教えていただきたいのですが、
> 最終行をコピーして > 【同じ行】に挿入貼り付けをし > 下側のデータ部分を削除 > と言う考え方をすれば良いと思います
これは、あらくまさんが提示されたシートレイアウトでは、C列の最終行である6行目をコピーして、 6行目に「コピーしたセルの挿入」をするということでしょうか？
そうすると、I6セルの数式は =IF(AND(G6="",H6=""),"",I5+G6-H6) であるものが、 =IF(AND(G6="",H6=""),"",I4+G6-H6) となり、挿入後のI7セルの数式が =IF(AND(G7="",H7=""),"",I5+G7-H7) となり、ずれる思いますが、違いますか？
もっとも、I列の数式を再設定すればいいのですが・・・
以下のようなコードで検証しています。
Sub insertLine4() Dim lastRow As Long '最終行格納変数
lastRow = Range("C" & Rows.Count).End(xlUp).Row 'C列入力セル最終行(X)獲得
Rows(lastRow).Copy 'C列入力セル最終行をコピーして
Rows(lastRow).Insert Shift:=xlDown '次の行にコピーした行の挿入
Rows(lastRow + 1).Range("A1:D1,F1:H1").ClearContents '挿入行の入力データ削除
End Sub
HANAさんのことですから、サンプルファイルなどを作られて、何かしら検証した結果をおっしゃっているのだと思いますので、 どういう手順なのかが知りたいです。
(かみちゃん) 2009/08/08 22:19
＞I6セルの数式は ＞・・・・ ＞ずれる思いますが、違いますか？
あっ、失礼しました。ずれますね。 I列の式は（I4） =IF(AND(G4="",H4=""),"",SUM($G$3:G4)-SUM($H$3:H4)) の様な式を使わないといけませんね。
(HANA)
こんにちは。かみちゃん です。
To,HANAさん
> I列の式は（I4） > =IF(AND(G4="",H4=""),"",SUM($G$3:G4)-SUM($H$3:H4)) > の様な式を使わないといけませんね。
ありがとうございます。 なるほど、そのような数式にすれば、ＯＫですね。 ちょっとした発想の転換ですね。
To,あらくまさん
I4セルの =IF(AND(G4="",H4=""),"",I3+G4-H4) の数式を =IF(AND(G4="",H4=""),"",SUM($G$3:G4)-SUM($H$3:H4)) に変更できるようであれば、HANAさんの提案もひとつだと思います。 この数式、一見わかりにくいかもしれませんが、結局は同じことをしているわけなので。
それができれば、 2009/08/08 22:19 で提示した insertLine4 でできると思います。
(かみちゃん) 2009/08/08 22:46
To,かみちゃんさん
ありがとうございます。 ついでと言っては・・・ですが insertLine4のコメントを再考していただけると良いのですが。 （私の感覚とはずれています。） 現在の状態で良い と言う事なら 取り立てて言うほどの問題でもないのですが。
(HANA)
こんにちは。かみちゃん です。
To,HANAさん
> insertLine4のコメントを再考していただけると良いのですが。
再考というより、コードとコメント文、合っていませんね。修正していませんでした。 あまり深追いは勘弁していただきいのですが、修正もれなので自分なりに修正してみます。
Sub insertLine4_1() Dim lastRow As Long '最終行格納変数
lastRow = Range("C" & Rows.Count).End(xlUp).Row 'C列入力セル最終行(X)獲得
Rows(lastRow).Copy 'C列入力セル最終行をコピーして
Rows(lastRow).Insert Shift:=xlDown '最終行に「コピーしたセルの挿入」
Rows(lastRow + 1).Range("A1:D1,F1:H1").ClearContents '最終行の次の行の値を消去
End Sub
HANAさんの感覚とまだずれているようでしたら、適当に修正してください。 コードは、問題ないはずなので、取り立てて言うほどのことはないと思っていますので。
ただ、よく考えると、以下のようなコードのほうがすっきりしているかもしれません。
Sub insertLine4_2()
With Range("C" & Rows.Count).End(xlUp).EntireRow 'C列入力セル最終行全体
.Copy 'コピー
.Insert Shift:=xlDown '同じ行に「コピーしたセルの挿入」
.Range("A1:D1,F1:H1").ClearContents '最終行の次の行の値を消去
End With
End Sub
(かみちゃん) 2009/08/08 23:30
コメントは、基本的に使う人がわかればよいと思います。 私が書いたコードに、私の思いと違うコメントが付けられていたり 質問者さんが全く関係ないコメントをコードにつけておられたら 修正しようと思いますが、insertLine4はそう言ったものではないので それで良いと思っておられるのであれば何かを言うつもりはありません。
前回のものは、「コードとコメント文、合っていませんね。」 というレベルの問題に感じましたので、再考依頼をしたまでです。 あらくまさんが、悩まれるといけませんので。
(HANA)
こんにちは。かみちゃん です。
> 前回のものは、「コードとコメント文、合っていませんね。」 > というレベルの問題に感じましたので、再考依頼をしたまでです。
たまに、HANAさんのコメントには悩まされるときがあるのですが、
コードとコメント文が合っていないのでは？
というご指摘だったのでしょうか？
> それで良いと思っておられるのであれば
という言い方が「何か言いたいことがあるのか」と気になるところですので、 他に意図しているところがあれば、教えていただきたいですが、 そうでないならば、ご指摘ありがとうございました。
(かみちゃん) 2009/08/08 23:50
＞たまに、HANAさんのコメントには悩まされるときがあるのですが、 済みません。 コードとコメント文が合っていないと思いました。 でも、コメントなんて 何が書いてあってもいい様なものですから 私の感覚と違うだけで「これで良いんだよ！！」って場合は 有ると思いますので、あの様な書き方になりました。
かみちゃんさんが作成されたコードに、 かみちゃんさんがコメントを付けられたのですから これ以上引っ張る必要はないと思います。 ＞あまり深追いは勘弁していただきいのですが でしょう？
(HANA)
かみちゃんさん、HANAさん、ありがとうございました。 希望どおりの動作が実現できました。 お二人の助言、やりとりでﾏｸﾛのおもしろさ？奥深さをあらためて 感じました。 同じ結果を得るための方策がいくつも・・・ それぞれの違いに目をやることで、ﾏｸﾛに対する理解を深めること ができることでしょう。 頑張ってみます。 (あらくま)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.