マクロ 行挿入で数式も反映する方法

質問

マクロのフォームを使ってデータ入力・行の挿入を登録したのですが、
シートに入力した計算式が反映されません。

insertRow = Range("登録品目データ").Rows.Count
Range("登録品目データ").Rows(insertRow).Insert Shift:=xlDown

Range("登録品目データ").Cells(insertRow, 1) = ModelNameBox.Text
Range("登録品目データ").Cells(insertRow, 2) = PartNumberBox.Text
Range("登録品目データ").Cells(insertRow, 3) = PartNameBox.Text
Range("登録品目データ").Cells(insertRow, 4) = StockingPriceBox.Text
Range("登録品目データ").Cells(insertRow, 5) = PackingAmountBox.Text

Sheets("品目一覧").Protect

Unload AddCommodityDlg
End Sub

上記Box5に入力したデータを元に、VLOOKUP関数で別のシートの情報を
反映させる式を入力しているのですが、フォームを使って情報入力し、
最下行に挿入すると計算式が反映されません。

どうすればいいか教えて下さい。
宜しくお願いします。

回答No.6

Wendy02 · ベストアンサー

こんばんは。

>色々説明不足で申し訳ありません。

いいえ、もう、私は、その状況は把握しているつもりなのですが、こちらでは、もう一度、考え直してみました。

>また、名前-定義を使用せず、下記に変更しても反映されませんでした。
>=IF(F4="","",VLOOKUP(F4,梱包容器一覧!A3:E17,5,FALSE))

上記の書き方なら、名前－定義と同じなのです。なぜかというと、名前の定義の中身が、梱包容器一覧!A3:E17　となっていれば、同じ結果なのです。
私が言ったのは、範囲を列全体にするということです。例：「A:E」

しかし、しばらく考えてみましたが、やはり、マクロ側では、名前定義は、使わなくてもよいような気がします。必然性がありません。そして、その式自体は、間違った解は出ないはずなので、名前－定義の範囲を大きく取ればよいと思うのです。

数式とマクロは別物ですから、マクロ側は、Range("登録品目データ") としなくてもよいと思います。

実際、

A列
1
2
3
4
5
6
7
8
　　←(B).ここに入れる

たぶん、最終行というのは、この(B)になるのだろうと思います。このマクロには挿入がありませんから、範囲に余裕を持っていれば、まず間違いはないと思います。名前の定義の範囲を1000行程度でも範囲を広げたらどうですか。

ただし、データを入れる最後の行を探す方法は、

With Worksheets("登録品目データ")
i = .Range("A65536").End(xlUp).Offset(1)
　.Cells(i, 1).Value = ModelNameBox.Text
　.Cells(i, 2).Value = PartNumberBox.Text
　.Cells(i, 3).Value = PartNameBox.Text
　.Cells(i, 4).Value = StockingPriceBox.Text
　.Cells(i, 5).Value = PackingAmountBox.Text
End With

としたらどうでしょうか。

回答No.5

Wendy02

#4の補足

マクロとは別問題の話です。
ものすごく基本的なことですが、可能なら、VLOOKUP側で、名前-定義を使わなければよいのでしょうね。例えば、A:E の広域の範囲にしておけば、問題なく、検索値に反映します。

補足

ご回答ありがとうございます。

色々説明不足で申し訳ありません。
VLOOKUP関数は下記のように入力しています。
=IF(F4="","",VLOOKUP(F4,登録ケース一覧,5,FALSE))

ご指摘の通り、VLOOKUP関数で名前-定義を使用していますが、上記“登録ケース一覧”もデータを増やしていく為、
名前-定義はできれば使用したいのです。

また、名前-定義を使用せず、下記に変更しても反映されませんでした。
=IF(F4="","",VLOOKUP(F4,梱包容器一覧!A3:E17,5,FALSE))

どうすればいのでしょうか。。。

回答No.4

Wendy02

こんにちは。

質問内容では、推測の範囲は超えないですが、「登録品目データ」名前-定義登録をしているのだと思います。今、見てみましたが、想像する以上にややこしく、難しくなってしまっています。私の推測が正しければ、マクロ１年レベルの人では直せないと思います。こういうときは、自己流でせずに、決まりきったパターンを使えばよかったと思います。

>最下行に挿入すると計算式が反映されません。

今試してみましたが、数回繰り返すと、やはり名前－定義登録は、自動的に変更しないのでうまく反映してくれないようです。マクロでは、名前-定義登録とは相性があまりよくありませんが、数式で使っている以上は使わなくてはなりません。名前の再登録する必要があるようです。

VLOOKUP側で、その名前-定義を使っているようですね。

いろいろやってみましたが、今の状態では、最下行と、名前-定義との関係が、不安定な状態です。

マクロで行の挿入は、一体、どこに入れるのでしょうか？

もともと、データの最下行だったら、挿入は必要ないです。データの最下行の一つ手前なら、挿入が必要です。

A
1
2
3
4
5
6
7
　　←(A).ここに入れるのですか？
8
　　←(B).それとも、ここに入れるのですか？（ここは挿入--Insertではありません）

それから、
Range("登録品目データ").Cells(insertRow, 1) = ModelNameBox.Text

これは少し意味が違ってきます。

(B) に入れる場合　（今の所、(A)案は考えられません）

Sub Sample1()

'名前-登録の再定義（最初に、正しければ、この行は不要）
Application.Names("登録品目データ").RefersToLocal = _
　　　　 "=" & Range("A1").CurrentRegion.Address

insertrow = Range("登録品目データ").Rows.Count

With Range("登録品目データ").Rows(insertrow).Offset(1)
　.Cells(1, 1).Value = ModelNameBox.Text
　.Cells(1, 2).Value = PartNumberBox.Text
　.Cells(1, 3).Value = PartNameBox.Text
　.Cells(1, 4).Value = StockingPriceBox.Text
　.Cells(1, 5).Value = PackingAmountBox.Text
End With
'名前-登録の再定義
Application.Names("登録品目データ").RefersToLocal = _
　　　　 "=" & Range("A1:E" & insertrow + 1).Address
''Range("登録品目データ").Select　'確認用
End Sub

なお、TextBox のプロパティは、Text で取っても、ワークシートに貼り付けるときには、自動的に、相応しい型にキャストされますので、変換は必要ありません。ただし、入力ミスした場合は除きます。

回答No.3

keirika

データ型の問題かもしれません。

テキストボックスで入力した値は文字になります。

Val(PackingAmountBox.Text)などで数値に変換してみてはどうでしょう。

回答No.2

xls88

連続ですみません。

複数のセルに数式を反映する場合は
数式セルも含めて該当セル範囲を選択
数式セルで、F2キーを押して編集モードにする
Ctrl＋Enter、とキー操作して数式を他のセルにコピー

といった操作を「マクロの記録」してみてください。

補足

ご回答ありがとうございます。

(1)Ctrl+Dのマクロ　
　Selection.FillDown

(2)Ctrl+Enterのマクロ
　Selection.FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],登録ケース一覧,5,FALSE))"

上記マクロの頭に
　Range("登録品目データ").Cells(insertRow, 6) =
を付けて実行すると、(1)＝TURE　(2)＝FALSE　がセルに表示されました。
頭に付けるマクロがおかしいのでしょうか？

初歩的な質問になり申し訳ないのですが、
マクロ経験1ヶ月の新人の為、お許し下さい。

回答No.1

xls88

単純に、数式セルをコピーすればどうでしょうか。
あるいは
計算式を反映したいセルを選択して
Ctrl＋D
とキー操作すれば、1行上のセルにある計算式がフィルコピーされます。

上記の操作を「マクロの記録」すればどうでしょうか。
参考になるコードが得られると思います。