[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『行変化に対応した数式をセルにマクロで挿入』（あらくま）
会計簿作成で困っています。
日計簿シートにデータ入力又は修正によるデータ行を削除するたびに処理対象 となる最終行が変化します。 この最終行情報をマクロによる数式入力に反映させる方法はあるでしょうか。 例えば、テスト事例として対象行が3行目から6行目の場合を想定して、下記 マクロを試してみたところ正常に動作しました。
Sub Macro1()
Sheets("執行状況").Range("E4").Formula = _
"=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6)"
Sheets("執行状況").Range("E6").Formula = _
"=SUMIF(日計簿!D3:D6,C6,日計簿!G3:G6)"
Sheets("執行状況").Range("E7").Formula = _
"=SUMIF(日計簿!D3:D6,C7,日計簿!H3:H6)"
Sheets("執行状況").Range("E8").Formula = _
"=SUMIF(日計簿!C3:C6,C8,日計簿!H3:H6)"
Sheets("執行状況").Range("E9").Formula = _
"=SUMIF(日計簿!C3:C6,C9,日計簿!H3:H6)"
Sheets("執行状況").Range("E11").Formula = _
"=SUMIF(日計簿!D3:D6,C11,日計簿!H3:H6)"
Sheets("執行状況").Range("E12").Formula = _
"=SUMIF(日計簿!D3:D6,C12,日計簿!H3:H6)"
Sheets("執行状況").Range("E13").Formula = _
"=SUMIF(日計簿!D3:D6,C13,日計簿!H3:H6)"
Sheets("執行状況").Range("E14").Formula = _
"=SUMIF(日計簿!D3:D6,C14,日計簿!H3:H6)"
Sheets("執行状況").Range("E15").Formula = _
"=SUMIF(日計簿!C3:C6,C15,日計簿!H3:H6)"
Sheets("執行状況").Range("E16").Formula = _
"=SUMIF(日計簿!C3:C6,C16,日計簿!H3:H6)"
Sheets("執行状況").Range("E17").Formula = _
"=SUMIF(日計簿!C3:C6,C17,日計簿!H3:H6)"
End Sub
そこで、ここに
Dim lastRow As Long
lastRow = Range("C" & Rows.Count).End(xlUp).Row などで
日計簿シートの対象セル最終行情報を取得し、C6、D6、G6、H6などを
"C" & lastRow、"D" & lastRow、"G" & lastRow、"H" & lastRowなどに
変更して対応することは可能でしょうか。
不可能ならよき対処方法をお教え願えませんでしょうか。
ちなみに各シートの様子は次の通りです。
シート"日計簿"
A B C D E F G H I
1 ○年度○○会計日計簿
2 領収書№ 日付 費目1 費目2 収支区別ｺｰﾄﾞ 摘要 収入金額 支出金額 差引残高
3 1/1 繰越 200 前年より繰越 100000 100000
4 1 2/3 需要費 100 か 50000 50000
5 2 4/7 渉外費 100 み 20000 30000
6 5/9 会費 200 た 60000 90000
<ここのところにマクロで入力用の行が挿入されます。>
7 （合計行） 160000 70000 90000
シート"執行状況"
A B C D E F
1
2 予算額 執行額 未執行額
3 繰越金 30000 30000 0
4 会費 250000 180000 7000
5 収入 雑収入
6 預金利息 200
7 払戻金 0
8 会議費 50000
9 需用費 100000
10 事業費
11 環境整備費 20000
～ 支出
16 雑費 15000
17 予備費 10000
↑
このE列にシート"日計簿"から項目ごとの合計額を
求め表示したい
[Excel2003,WindowsXP]
支出や収入の列が変化しないのなら、C:CやH:Hでの列単位で指定しても よいと思われます。 (みやほりん)(-_∂)b
みやほりんさん、ありがとうございました。 列単位指定ということができるのですね。 たいへん勉強になりました。 エクセル初心者、マクロ超初心者の思いつきである最終行の情報を挿入式に生かすことは 基本的にできないのでしょうか。 後学のためお教え願いませんか。 (あらくま)
＞ "C" & lastRow、"D" & lastRow、"G" & lastRow、"H" & lastRowなどに ＞変更して対応することは可能でしょうか。 可能です。
＞不可能ならよき対処方法をお教え願えませんでしょうか。 上手く行きませんでしたか？ でしたらその、上手く行かなかったコードを 書いてみられるのが良いと思います。
一応、別アプローチとして おそらく [[20090808152015]]『ﾏｸﾛによるﾃﾞｰﾀ追加用行挿入での罫線』（あらくま） の関連だと思いますので、この時に ＞＞【同じ行】に挿入貼り付けをし ＞＞下側のデータ部分を削除 とすれば、問題の数式も自動的に範囲は拡張されると思います。
(HANA)
HANAさん、たびたびお世話になり、恐縮です。 超初心者ですので、大変恥ずかしいのですが、お教えください。
標準モジュールに以下のものを貼り付け実行したところ、
Dim lastRow As Long
lastRow = Range("C" & Rows.Count).End(xlUp).Row
Sheets("執行状況").Range("E4").Formula = _
"=SUMIF(日計簿!C3:C & lastRow,C4,日計簿!G3:G & lastRow)"
以下同じような記述で処理
End Sub
次のところで
Sheets("執行状況").Range("E4").Formula = _
"=SUMIF(日計簿!C3:C & lastRow,C4,日計簿!G3:G & lastRow)"
「アプリケーション定義またはオブジェクト定義のエラーです。」という
メッセージが出ました。
記述に間違いがあると思われますが、さっぱり分かりません。
また、「lastRow = Range("C" & Rows.Count).End(xlUp).Row」をいきなり
記述してよいのかどうか、シートを指定する必要があるのではないかと考
えています。ここのところもご指導いただければ幸いです。
(あらくま)
>最終行の情報を挿入式に生かすことは >基本的にできないのでしょうか。 数式そのものの工夫で数式を変更するマクロを使わなくてもよい、と考えます。 =SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6) の式は ３行目を削除してしまうと#REF!になったりしますが、 =SUMIF(日計簿!C2:C7,C4,日計簿!G2:G7) と上下１行ずつ余分に範囲指定して おけば、マクロも必要ないのではないでしょうか。 (みやほりん)(-_∂)b
基本的には、みやほりさんと同じで マクロを使わなくても良いように数式を見直す 或いは、行を挿入するマクロを見直すのが良いと思います。
＞後学のためお教え願いませんか。 と言う事ですので、この言葉を信じてコメントします。 その点、お酌み取り頂ければと思います。
まず、どのシートがアクティブな状態で実行するのか 考える必要が有ると思います。
シート名を明記しなかった場合 アクティブシートが対象に成ります。
前スレの関連で、 日計簿シートがアクティブになっている状態で、行を追加した時 同時に（アクティブシートを切り換える事無く）執行状況シートの 数式を変更するのであれば シート名を書かなくても 日計簿シートの 最終行が取得されます。
どのシートがアクティブな状態で実行するか分からないけど 或いは、日計簿シート以外のシートがアクティブな状態で実行するけど 日計簿シートの最終行を取得したい と言う事なら シート名を書いておくのがよいと思います。 （シート名を書かない場合は、日計簿シートをアクティブにするコードが 最終行を取得する前までに必要になります。これでも希望する値が得られますが その様な事がしてあるコードはあまり良いコードとは言えません。）
数式に付いては、ゆっくり考えてみて下さい。
Sheets("執行状況").Range("E4").Formula = _
"=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6)"
下行は""で囲われているので「=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6)」
と言う【文字列】です。
この時「6」の部分を、変数「lastRow」にしたいのですよね。
少しマクロから離れてみます。 A1セルに「6」と入っていたら、B1セルに =C6 A1セルに「7」と入っていたら、B1セルに =C7 と成るような【文字列】を作りたかったら B1セルに ="=C"&A1 としますね。
では、「=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6)」と言う文字列（数式じゃないですよ！！）の 「6」部分がA1セルを参照して変化するような【文字列】を作ろうと思ったら B1セルにどの様な式を作れば良いか分かりますか？
(HANA)
To HANAさん 職場を離れていましたので、HANAさんのコメントを今みました。 お付き合いいただき、本当にありがとうございます。
シート名にかかる部分はよく分かりました。 今後に活かせる知識を得ることができました。
数式の方ですが、 「"=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6)"」ですから・・・
難しいです。文字列と数式の違いをしっかり理解していないためでしょうが。
安易すぎると笑われるのを覚悟して、 「"=SUMIF(日計簿!C3:"=C"&A1,C4,日計簿!G3:"=G"&A1)"」でどうでしょうか？ (あらくま)
To HANAさん、 前コメントの数式にかかる課題に対する解答はなかったことにしてください。
これでどうでしょうか。
"=SUMIF(日計簿!C3:C"&A1&",C4,日計簿!G3:G"&A1")"
(あらくま)
＞これでどうでしょうか。 先頭に「=」を付けてB1セルに貼りつけて確定しようとすると 「入力した数式は正しくありません。」と言われます。
そちらで、問題なく動く式を作成して 載せて下さい。
例えば、「今日の天気は晴れです」の[晴れ]の部分を A1セルを参照することにすると ="今日の天気は"&A1&"です" の様に "文字列" & セル番地 & "文字列" となりますよね？ ="今日の天気は"&A1"です" では「入力した数式は正しくありません。」と言われます。
もう一度、ご自身で作成された式をご覧ください。
（なお、コピペして確定だけすれば良くなる様に 先頭に = も付けた状態で投稿して頂ければと思います。）
(HANA)
T0 HANAさん、おはようございます。 お付き合いいただき、本当に感謝いたします。
何となく分かってきました。 先頭の=は「これは数式ですよ」というおまじない。 " "に囲まれた部分は文字列、& &に囲まれた部分は？？ (今の段階では参照先セル番地)ということでしょうか。
さて、課題ですが、エクセルを作動させて試してみました。 問題なく？動作したようなのですが・・・ これでどうでしょうか。
="=SUMIF(日計簿!C3:C"&A1&",C4,日計簿!G3:G"&A1&")"
(あらくま)
> " "に囲まれた部分は文字列、& &に囲まれた部分は？？
& & に囲まれるのではなく、繋げるという意味です。 A1セルに色々入れてみて以下のコードだけで試してみると、少しはわかるのではないでしょうか？
Sub Test()
MsgBox "=SUMIF(日計簿!C3:C" & A1 & ",C4,日計簿!G3:G" & A1 & ")"
End Sub
(とおりすがり)
＞& &に囲まれた部分は？？ VBAヘルプ、演算子の概要 （または Operator Summary）の「文字列連結演算子」 を参照してください。 (みやほりん)(-_∂)b
＞＞="=C"&A1 は、試して頂けていませんか？
「=C」と言う文字と、A1セルに入力されている文字が & で結合されて ＞＞A1セルに「6」と入っていたら、B1セルに =C6 ＞＞A1セルに「7」と入っていたら、B1セルに =C7 と言う文字列が、この数式を入れたセルに表示されますよね？
＞先頭の=は「これは数式ですよ」というおまじない。
そうです。ワークシート上で数式を入力する際、エクセルでは先頭に「=」をつけますね。
なお、マクロの場合 「=」は【入力】と言った意味が有ります。
Range("A1").Value = "今日の天気は晴れです"
とすれば、A1セルに「今日の天気は晴れです」と入力されます。
＞" "に囲まれた部分は文字列 そうです。
＞& &に囲まれた部分は？？ ＞(今の段階では参照先セル番地) 既にみなさんからコメントが有りますが「&」は 【前と後ろを繋げる】時に使用します。
A1セルの値が「6」だった場合 "=C"&A1 前 「=C」と言う文字列 後 「A1セルの値」（6） を繋げる。 ↓ "=C6"
"=SUMIF(日計簿!C3:C"&A1&",C4,日計簿!G3:G"&A1&")" で有れば、 ~1 ~2 ~3 ~4 前 "=SUMIF(日計簿!C3:C" 後 A1セルの値 を繋げて ↓ "=SUMIF(日計簿!C3:C6"
さらに 前 "=SUMIF(日計簿!C3:C6" 後 ",C4,日計簿!G3:G" を繋げて ↓ "=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G"
さらに 前 "=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G" 後 A1セルの値 を繋げて ↓ "=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6"
もう一つ 前 "=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6" 後 ")" を繋げて ↓ "=SUMIF(日計簿!C3:C6,C4,日計簿!G3:G6)"
これで、数式の文字列が完成します。
実際は、A1セルの値を参照するのではなく 変数「lastRow」の値を使用するのですから A1の部分が「lastRow」ですね。
Sheets("執行状況").Range("E4").Formula = _
執行状況シートのE1セルの数式（今回の.Formula→数式）として
【入力】（= 部分）するのは
さて、変数「lastRow」を使って、どの様な式を入れれば良いでしょう？
なお、ワークシート関数と違って、適当な所へスペースが要りますので 「ここぞ」と思う所にスペースを入れながら コードを作成してみて下さい。
(HANA)
HANAさん、なぜそんなにいつもねちっこいの？もしかして、粘着質な性格？ 一生懸命さと丁寧さは評価できるが、程度があるかと。 もう少しコンパクトなコメントを希望する。
(学校の一生徒)
To HANAさん できました！ これではないでしょうか。 エクセル上でテストしたところ、上手く動きました。
Sheets("執行状況").Range("E4").Formula = _
"=SUMIF(日計簿!C3:C"& lastRow &",C4,日計簿!G3:G"& lastRow &")"
>なお、ワークシート関数と違って、適当な所へスペースが要りますので >「ここぞ」と思う所にスペースを入れながら 根拠はなし、勘だけですが、&と&との間に入れるlastRowの前後に半角スペース を入れてみて試したところ、幾度となく挑戦してダメだったことがすーっと
"=SUMIF(日計簿!C3:C"& lastRow &",C4,日計簿!G3:G"& lastRow &")"
- - - -
-の部分に半角スペース
さて、どんな約束事があるのでしょうか。
セル値のときと違える理由（規則）を教えていただければ幸いです。
(あらくま)
出来たようで良かったです。
＞さて、どんな約束事があるのでしょうか。
VBAでは、「&」が文字をくっつける以外の意味も持つので その場合とを区別する為に必要なんだと思います。
載せて居られるコードを、VBEに貼り付けると
"=SUMIF(日計簿!C3:C"& lastRow &",C4,日計簿!G3:G"& lastRow &")"
~~ ~~ ~~ ~~
この４箇所に勝手にスペースが入って
"=SUMIF(日計簿!C3:C" & lastRow & ",C4,日計簿!G3:G" & lastRow & ")"
に成ります。
＞&と&との間に入れるlastRowの前後に半角スペース ではなく 文字と文字をくっつける&の前と後ろには、スペースが必要 と覚えておくのが良さそうに思います。
因みに、数式の絶対参照と相対参照に注意すると、以下の様に成ると思います。
"=SUMIF(日計簿!C$3:C$" & lastRow & ",C4,日計簿!G$3:G$" & lastRow & ")"
^ ^ ^ ^
行方向へコピーした時に、範囲の行数は固定ですから。
(=SUMIF(日計簿!C$3:C$6,C4,日計簿!G$3:G$6) こんな式って事。）
~~ ~~ ~~ ~~
E4セルに入れる数式をその様にしておくと 全体が以下の様にも書けます。
Sheets("執行状況").Range("E4,E6:E9,E11:E17").Formula = _
"=SUMIF(日計簿!C$3:C$" & lastRow & ",C4,日計簿!G$3:G$" & lastRow & ")"
E4セルに"=SUMIF(日計簿!C$3:C$" & lastRow & ",C4,日計簿!G$3:G$" & lastRow & ")"の式 それに続くセルE6:E9,E11:E17には、E4セルをコピーして貼り付けたのと 同じ結果の数式が入ることになります。
(HANA)
To HANAさん 本当にありがとうございました。 親切なお導きで、すこ～しですが、マクロ理解が深まりました。
今取組中の会計簿作成、次の段階に進むことができます。 (あらくま)
>VBAでは、「&」が文字をくっつける以外の意味も持つので 【参考】 [[20051119224811]] 『"”"と記述したい』（素人） とりあえず、文字列結合演算子としてアンパサンドを使用する場合は、 習慣的に半角スペースをその前後に入れておく習慣をつけておくとよいでしょう。 こんな場合がコンパイルエラーになります。 Sub test() Dim h12345 h12345 = "い" msgbox "あ" &h12345 End Sub 変数12345 に 文字列"い" を格納し、"あい" と言う文字列を 作ろうと思って&をスペースなしで入力すると、&h12345 という塊が １６進数を表すものと認識されてしまう。 (みやほりん)(-_∂)b
みやほりんさん、追加の助言ありがとうございました。 さらに理解が深まりました。 (あらくま)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.