• 締切済み

エクセル２００７　行を挿入すると式の一部がずれる

エクセル２００７の行挿入の件で困っています。 行の挿入をすると式がずれてしまいます。 式はＨの列の下記の式が入っています。 インターネットで検索をしてＶＢＡに追加をして行挿入しても 式が反映されるようになりましたが行挿入すると式がずれて しまいます。 どなたかお力をお貸しください。 H4=IF(B4="","",H3+D4-F4) H5=IF(B5="","",H4+D5-F5) H6=IF(B6="","",H5+D6-F6) ↓ H4=IF(B4="","",H3+D4-F4) H5=IF(B5="","",H4+D5-F5) H6=IF(B6="","",H4+D6-F6)←H4になって、H5にならない。 H7=IF(B7="","",H6+D7-F7) ＶＢＡで設定した項目 ------------------------------------------------------ Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Rem 挿入行に数式のみコピー Rem 変数宣言 Dim c As Range Rem 対象範囲のセル数チェック If Target.Columns.Count < 256 Then Exit Sub Rem 対象範囲の列数をチェック If Target.Rows.Count > 1 Then Exit Sub Rem 対象範囲の各セルに対しループ For Each c In Target Rem 列番号が２及び４のとき一つ上の行の内容を複写 If c.Column = 2 Or c.Column = 4 Then c.Value = c.Offset(-1, 0).Value Rem 上の行の内容が数式っぽいときはその内容を複写 If c.Offset(-1, 0).Formula Like "=*" Then c.Offset(-1, 0).Resize(2, 1).Formula = c.Offset(-1, 0).Formula End If Next c End Sub ------------------------------------------------------

みんなの回答

  • chie65535
  • ベストアンサー率43% (8522/19371)
回答No.2

やりたいのは「行挿入して、空白になった行に、自動的に式を入れたい」と言う事ですね？ H4=IF(B4="","",H3+D4-F4) H5=IF(B5="","",H4+D5-F5) H6=IF(B6="","",H5+D6-F6) となっている時に、４行目と５行目の間に１行挿入すると H4=IF(B4="","",H3+D4-F4) 空行空行空行空行空行空行 H6=IF(B6="","",H4+D5-F5) H7=IF(B7="","",H6+D7-F7) と言う状態になります。 これは「列挿入や行挿入をしても、セル参照の依存関係は変化しない」ようになっているからです。 H4を参照している式は、何をどう弄り回しても「元のH4だったセルを参照する」のです。 これを回避して「物理的に１行上を見に行く」には、式そのものを変えないといけません。 式そのものを変えて H4=IF(B4="","",OFFSET(H4,-1,0)+D4-F4) H5=IF(B5="","",OFFSET(H5,-1,0)+D5-F5) H6=IF(B6="","",OFFSET(H6,-1,0)+D6-F6) とすれば、行挿入しても H4=IF(B4="","",OFFSET(H4,-1,0)+D4-F4) 空行空行空行空行空行空行空行空行空行 H6=IF(B6="","",OFFSET(H6,-1,0)+D6-F6) H7=IF(B7="","",OFFSET(H7,-1,0)+D7-F7) となります。 この時、OFFSET(H6,-1,0)は「挿入された空行のセル」を参照します。H4は参照しません。 なお、マクロは単純に Private Sub Worksheet_Change(ByVal Target As Range) Rem 挿入行に数式のみコピー Rem 変数宣言 Dim c As Range Rem 対象範囲のセル数チェック If Target.Columns.Count < 256 Then Exit Sub Rem 対象範囲の列数をチェック If Target.Rows.Count > 1 Then Exit Sub Rem 先頭行だったら無視 If Target.Row = 1 Then Exit Sub Rem 対象範囲の各セルに対しループ For Each c In Target If IsEmpty(c) Then c.Formula = c.Offset(-1, 0).Formula Next c End Sub で構いません。 式じゃない値がセルに入っている場合、FormulaプロパティはValueプロパティと同じ動作をします。 つまり「式だろうが何だろうが、１行上のFormulaをコピーしてくれば良い」のです。

  • Cupper-2
  • ベストアンサー率29% (1342/4565)
回答No.1

それ、根本的に間違ってないかい。 行挿入しても式が成り立つように関数式を作れば問題は解決すると思うんだ。 INDIRECT関数が見た目で分かりやすいと思うのでこれで説明してみます。 INDIRECT関数は文字列で与えられた引数を参照するセルに置き換える関数です。 具体的には 　　H4=IF(B4="","",H3+D4-F4) 　　　↓ 　　H4=IF(INDIRECT("B4")="","",INDIRECT("H3")+INDIRECT("D4")-INDIRECT("F4")) こうなります。 この関数式はコピーをしたときに参照するセルが変わりません。 　　　え？どういうこと？意味ないじゃん。それ。 …と思われるかもしれませんが、 ダブルクォーテーションで囲ったセルを示す文字列を、ちょっと変えるだけでうまく動作するようになります。 ROW関数はご存じですか。 指定したセルが何行目にあるのかを返してくれる関数です。 この関数の引数を指定せずに使用すると、関数式が入力された行番号が返ってくるんです。 　　H4=ROW() とすると、４が返ってくると言うことです。 …コレ、使えますよね？ 　　H4=IF(INDIRECT("B"&ROW())="","",INDIRECT("H"&ROW()-1)+INDIRECT("D"&ROW())-INDIRECT("F"&ROW())) こうすればOK。 ポイントは２つ目の "H3" を返すために "H"&ROW()-1 としているところ。 ちょっと見づらい関数になりましたが、これでVBAを使わなくてもよくなります。 行挿入したときに数式が反映されなければ、数式を反映させたいセルを選択して 　　Ctrl + D を押してください。 上のセルの内容をそっくりそのままコピーしてくれます。 ※ INDIRECT関数以外にもOFFSET関数でも同様なことができますが、 　　元の関数式とちょっとかけ離れた式になるので、理解が難しいとか…。 　　でもそっちのほうがスッキリするんですけどね。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • エクセルのマクロで上のセルの数式を相対参照でコピーしたい（フィルみたいに）

    エクセル2002で以下のようなシートがあります。 　|　A　|　B　|　C　|　D --------------------------- 1　|　　1|　10|　100|　=C1-1 --------------------------- 2　|　　 　|　 　　|　 　　|　 ここで、B2に文字が入力されると、A1とC1とD1をコピー、B2が消されるとA2とC2とD2を消去するマクロを書きました。 現在以下のように書いていますが、これでは入力位置がB2だろうがB3だろうかB20だろうが、D2と同じ数式になってしまいます。B5に入力されたならD5の数式はC5-1にしたいのですが、このような入力をするにはどうすればよいでしょうか。 ちなみにD列の数式は本当はもっと複雑です（この数式ならTarget.Offset(0, -4).Value = Int(Target.Offset(-1, -4).Value + 1でもたぶんいい・・・はず・・・） オートフィルを使えば！と思いましたが、Target・・・では使い方がわかりません。 Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column <> 2 Then Exit Sub 　　//B列以外への文字入力はマクロ停止（のつもり） If Target.Value <> "" Then Target.Offset(0, -1).Value = Int(Target.Offset(-1, -1).Value + 1) 　　//A1に+1したものをコピー Target.Offset(0, 1).Value = Target.Offset(-1, 1).Value 　　//C2にC1をコピー Target.Offset(0, 2).Formula = Target.Offset(-1, 2).Formula　　//D2にD1の数式をコピー◆ここが問題！ Else Range(Target.Offset(0, -1), Target.Offset(0, 4)).ClearContents End If Application.EnableEvents = True End Sub

  • エクセル　マクロを利用して繰り返して入力する方法

    マクロでA1B1C1D1E1F1セルに入力しF1入力後A2B2C2D2E2F2と 下方向へ繰り返し800行くらいまで入力し、それとB列はスキップしたいのですが下記の方法で別々のシートでは うまくいくのですが同じシ-ト内ではエラ－になってしまいます 次の行への移動 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 7 Then Cells(Target.Row + 1, 1).Select End If End Sub B列のスキップ Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("G1") <> "" Then If Target.Column = 2 Then Target.Offset(0, 1).Select End If End If End Sub どなたかご存じの方教えて下さい

  • エクセルVBAで表から行の削除

    添付画像のような表があります。 表はB列の名前でソートされています。 D列の比率をみて、100でないものは、必ず同じ名前で複数行にわかれ合計で100になります。この例では名前CとEとHがそうです。 同じ名前が複数行にわかれている場合、最大の比率の行を残し、他の行（例では、埼玉、栃木、長野、新潟の行）を削除したいのです。 複数行にわかれるのが名前CやEのように2行なら、以下のコードで出来ました。 しかし、めったにはありませんが名前Hのような3行以上に分かれるものには対応できません。 どうすればよいでしょうか？ Sub test01() 　　Dim c As Range 　　Dim Rng As Range 　　Set Rng = Range(Cells(2, "D"), Cells(Rows.Count, "D").End(xlUp)) 　　For Each c In Rng '2地区の分担の場合、分担比率高い方を残す。（3地区以上は未対応）2012/08/29 　　　　If c.Value <> 100 And c.Offset(1).Value <> 100 Then 　　　　　　If c.Offset(, -2).Value = c.Offset(1, -2).Value Then 　　　　　　　　If c.Value >= c.Offset(1).Value Then 　　　　　　　　　　c.Offset(1).Value = False 　　　　　　　　Else 　　　　　　　　　　c.Value = False 　　　　　　　　End If 　　　　　　End If 　　　　End If 　　Next 　　If Application.WorksheetFunction.CountIf(Rng, False) > 0 Then 　　　　Rng.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete 　　End If End Sub

  • VBA 特定文字入力による 行のカット 挿入

    はじめまして、VBA初心者です。よろしくお願いします。 下記のようなデータがあります。 　　　A　　　B　　　　　C　　　D　　 E　　　　F 1　 発注日　納入日　　名称　　数量　単価　　請求書C 2　1月15日　1月20日　バルブ　　1　 1500　 C（セル色がオレンジ） 3　1月19日　1月22日　リレー　　2　 5200　 C（セル色がオレンジ） 4　1月21日　　　　 　 ねじ　　　2 　100 5　1月31日　　　　　 ワッシャ　5 200　　　 6　2月13日　　　　　 砥石　　　1 　14800　　　 請求書CのF列セルにC文字を入力すると、 以下のイベントが起きるようにしたいです。 1.C入力セルの色がピンク色に変わる 2.C入力セルの行をカットする 3.対象行以上の上段行でC文字が入力されている行の下に挿入する 例えば、F5にC入力すると下記になるようにしたいです。 　　　A　　　B　　　　　C　　　D　　 E　　　　F 1　 発注日　納入日　　名称　　数量　単価　　請求書C 2　1月15日　1月20日　バルブ　　1　 1500　 C（セル色がオレンジ） 3　1月19日　1月22日　リレー　　2　 5200　 C（セル色がオレンジ） 4　1月31日　2月10日 ワッシャ　5 200　 C（セル色がオレンジ） 5　1月21日　　　　 　 ねじ　　　2 　100 6　2月13日　　　　　 砥石　　　1 　14800　　　 当方でも、下記までコード作成し、エラー無く動くのですが、 Cが既に入ってる上段行の下の行に入力すると （上記F5セルにCを入力すると）1行目にカット行が挿入されてしまいます。 Private Sub Worksheet_Change(ByVal Target As Range) 　　With Target 　　If .Row < 4 Then Exit Sub 　　If .Column <> 10 Then Exit Sub 　　 　　If .Value <> "" Then 　　.EntireRow.Cut 　　.Offset(0, 0).Activate 　　　　 　　.End(xlUp).Offset(1).Activate 　　.EntireRow.Insert Shift:=xlDown 　　 　　End If 　　End With 　　 End Sub また、F列セルのC文字をDELETEするとエラーがでます（例えば、F2:F4セル選択で削除）。 他に上記コードでエラーになりうる可能性がありますでしょうか？ 大変申し訳ありませんが、 みなさんのご教授を頂きたく、よろしくお願いします。

  • マクロでのタイトル行の変更

    いつもお世話になります。 添付の画像で　タイトル　行で 上側で　　氏名　登録日　No　は下記のようなマクロが入っています。 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Error If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If Target.Offset(0, -1).Value = "" Then Exit Sub If Target.Value <> "" Then Target.Offset(0, 1).Value = Application.Max(Range("C:C")) + 1 End If Error: End Sub 添付の下のように　No　登録日　氏名　のように位置を変更は上のマクロのどの部分を 変更すればいいかご教示願えませんか。 宜しく御願いします。 色々と試したのですが分からなくなりました。

  • エクセルで文頭に”’”を挿入したい。（VBA)

    エクセルのVBAで、D列の、文字が入っているところすべてに、先頭にアポストロフィーを挿入したいと思っています。 ネットで探っていて、下記のものをみつけたので、 Sub ApostroPut() For Each currentcell In Selection 'Prevents inserting apostrophes in blank cells. If currentcell.Formula <> "" Then currentcell.Formula = "'" & currentcell.Formula End If Next End Sub これを真似て、下記のようにつくったら、型が一致しません、というエラーが出ました。 下記のものは、自分の中では一応、D1から100までのうち、値があるものに、アポストロフィーが付く、（ということを期待していました）。 Sub ApostroPut() If Range("D1", "D100").Formula <> "" Then Range("D1", "D100").Formula = "'" & Range("D1", "D100").Formula End If End Sub 初心者なので、くだらない間違いなのだろうと思いますが、どなたかわかる方、デバックをお手伝いいただけないでしょうか。何卒宜しくお願いいたします。

  • excel BVでTarget.Offset

    勤務表を作成しています。 勤務「Ｃ」の翌日は「休」にするところまでは出来たのですが、「Ｃ」が連続する日は「休」も連続させたいのです。例）３日と４日が「Ｃ」の時は、５日、６日は連休にしたいのですが、作った下のマクロでは７日も「休」になってしまいます。何が間違ってるんでしょうか？。 If Target.Count > 1 Then Exit Subの意味も良く解っていませんが…。 どなたか教えてください。 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Value = "Ｃ" Then Target.Offset(0, 1).Value = ("休") End If If Target.Offset(0, -2).Value = "Ｃ" Then Target.Offset(0, 1).Value = ("休") End If End Sub

  • 関数が含まれたセルの行をマクロで挿入する

    セルD2には、IF関数が含まれている、以下の表があります。2行目に行をマクロで挿入して大きな表を作成する予定です。 　　　　　　　A　　　　　　　B　　　　　　　C　　　　　　Ｄ　　　　　 １　　　　納入数　　　使用m数　　　納入m　　　使用m 2　　　　　　　　　　　　　　　　　　　　　　　　　　　〔=if（B2>1,C2,""）〕 マクロは、以下のようにして、2行目に行を挿入したのですが、IF文が含まれたものが 挿入できません。どのようなマクロ文にしたらよいかわからず困っています。別シートからコピーする 方法も考えられますが、同じシート内で収めたいと考えています。宜しくお願いします。 Sub 行挿入() ' ' 行挿入 Macro ' ' Range("a2:ｄ2").Insert copyorigin:=xlFormatFromRightOrBelow End Sub

  • 挿入した行のC列に式を挿入

    Yahoo知恵袋からの回答の引用で Q エクセル マクロでデータごとに1行挿入するマクロを教えてください。 A～AW列にデータが入っていて、同じ品番ごとに1行挿入するマクロを教えてください。 A Sub insRow() Dim i As Long For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then Cells(i, 1).EntireRow.Insert (xlShiftDown) End If Next i End Sub このようなものがありますが、この最後のinsertにあたって、挿入されて新しく加わる全ての行のC列に式を挿入する方法(例えば、=A13等）を教えて頂けないでしょうか。

  • エクセルのコード表示についてですが。。

    Private Sub Worksheet_Change(ByVal Target As Range) (1)If Target.Column <> 4 Then Exit Sub Target.Offset(0, -3) = Now()　　　⇒特定のセルに日時自動表示 (2)If Target.Column <> 4 Then Exit Sub 　Target.Offset(0, 1) = "DUMMY"　　⇒特定のセルにDUMMYと自動表示 (3)If Target.Column = 4 Then 　Target.Offset(0, -2) = "1" 　Else　　　　　　　　　　　　　⇒特定のセルに１と自動表示　　 (4)If Target.Column = 35 Then 　Target.Offset(0, -2) = "2"　　⇒特定のセルに２と自動表示 　End If 　End If (5)If Target.Value = "T" Or Target.Value = "t" Then 　Target.Value = "田中"　　　　⇒Tと入力すると田中と変換して表示 　ElseIf Target.Value = "H" Or Target.Value = "h" Then 　Target.Value = "林"　　　　　⇒ｈと入力すると林と変換して表示 　End If 　End Sub 上のようなコードを入力すると(3)と(5)が機能しません。。なぜでしょうか？？コードの表示がまずいのでしょうか？？

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する