VBA

質問

VBAでCells（1,1）=1と書いて、
エクセルシートの行や列の挿入をすると、
1を書き込みたいセルの位置がずれるのですが、Cells（1,1）=1をCells（2,2）=1とかに
自動的書き換えることは出来ないのでしょうか？

回答No.1

keithin · ベストアンサー

例えば目標のA1セルに例えばTargetと名前を定義しておき
range("Target") = 1
とでもしておくと、対象セルに追従します。

＞Cells（1,1）=1をCells（2,2）=1とかに自動的書き換える

マクロの文面そのものが勝手に書き換わるなんて機能はありません。

お礼

ありがとうございました。
セルに名前を付けとけば良いのですね。
良いことを聞きました。

回答No.3

cj_mover

＃２、ｃｊです。お礼欄へのレスです。

> 使用する行と列に
> 行1、行2.....、列1、列2...
> と名前を付ければ、
> 行列を挿入しても追従するように出来るのでしょうか？

はい。
＃１で提示した
[初期設定_名前定義」、[実行8299761]では、
　　[初期行2] ～ [初期行21]
　　[初期列2] ～ [初期列15]
という名前にしています。
これを
　　[行2] ～ [行21]
　　[列2] ～ [列15]
になるように（"初期行"を"行"に置換する要領で）書き換えてみて、実行後、
[名前の管理]で確認してみれば、お解りになるかと。

ただ、どんな名前を選ぶか、は、どんな運用をするか、
に掛かってくると思いますし、
行列の挿入があると、[行2]が２行め以外を指すという妙、
というか一種の不整合で混乱してしまう場合もあるかも知れません？
ので、命名は慎重に決めたいですね。

という訳で、オマケ。

Sub 名前定義削除()
　　Dim oName As Name
　　For Each oName In ActiveSheet.Names
'　　For Each oName In ActiveWorkbook.Names
　　　　oName.Delete
　　Next
End Sub

回答No.2

cj_mover

こんにちは。お邪魔します。

既に必要な回答は得られているものと思いますし、
名前の定義を使うという意味では、＃１さんと同趣旨ですので、こちらは参考回答です。

Range の引数に指定する参照文字列に、スペース演算子を使って、
参照文字列をクエリっぽく使う例を紹介します。
まず、解説から。

　　Range("D:D")　　は、列　Ｄ
　　Range("5:5")　　は、行　５
と、ここまでは普通ですが、
ここで、スペース演算子を使うと、
　　Range("D:D 5:5")　　は、セル範囲　Ｄ５
になります。
要するに、
スペース演算子の前後で指定した２つのセル範囲、に共通するセル範囲を指定する、ということです。

　　Range("フィールド4")　　は、列　Ｄ
　　Range("レコード5")　　は、行　５
事前に設定した名前の定義が、上↑のようなセル参照になっているなら、スペース演算子を使うと、
　　Range("フィールド4 レコード5")　　は、セル範囲　Ｄ５
という風にセル範囲を指定することが可能です。

名前の定義は、行、列、の挿入後も、最初に設定した参照先を追いかけてくれます。

以下、新規のシートで、
[初期設定_名前定義]→[行列無作為挿入]→[実行8299761]の順に
実行し、試してみてください。

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Sub 初期設定_名前定義()　　'　　システムとして事前に、名前の定義を用意しておく
　　Dim i As Long

　　Cells.Clear
' ' とりあえず、元の番地が判るようにセル値を設定
　　With Range("A1:O21")
　　　　.Formula = "=""○""&CHAR(64+COLUMN())&ROW()"
　　　　.Value = .Value
　　　　.EntireColumn.AutoFit
　　End With
　　Range("B2:O21").Interior.ColorIndex = 36

' ' 名前の定義で、以下を設定
' '　　[初期行2] - [初期行21]
' '　　[初期列2] - [初期列15]
　　With ActiveSheet
　　　　For i = 2 To 21
　　　　　　.Names.Add Name:="初期行" & i, RefersToR1C1:="=R" & i
　　　　Next i
　　　　For i = 2 To 15
　　　　　　.Names.Add Name:="初期列" & i, RefersToR1C1:="=C" & i
　　　　Next i
　　End With
'　　MsgBox "初期設定_名前定義、完了"
End Sub

' ' ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

Sub 行列無作為挿入()　　'　　行、列、をランダムに挿入（想定：ユーザーによる編集）
　　Dim cn As Long
　　Dim i As Long
　　For i = 21 To 3 Step -1
　　　　If Int(Rnd * 7) = 1 Then
　　　　　　cn = cn + 1
　　　　　　Rows(i).Insert
　　　　　　With Rows(i).Resize(, 15)
　　　　　　　　.Value = "◇◇◇"
　　　　　　　　.Interior.ColorIndex = 39
　　　　　　End With
　　　　End If
　　Next i
　　For i = 15 To 3 Step -1
　　　　If Int(Rnd * 4) = 1 Then
　　　　　　Columns(i).Insert
　　　　　　With Columns(i).Resize(21 + cn)
　　　　　　　　.Value = "◇◇◇"
　　　　　　　　.Interior.ColorIndex = 35
　　　　　　End With
　　　　End If
　　Next i
'　　MsgBox "行列無作為挿入、完了"
End Sub

' ' ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

Sub 実行8299761()　　'　　初期設定時の番地に値を設定する
　　Dim i As Long
　　Dim j As Long
　　For i = 2 To 21
　　　　For j = 2 To 15
　　　　　　Range("初期行" & i & " " & "初期列" & j).Value = "元・" & Chr(64 + j) & i
　　　　Next j
　　Next i
End Sub

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

初期設定時の番地を正しくトレースできていることは確認できることと思います。
まぁ、でも、実際試してみると感じると思いますが、
サンプルのように一様に名前を定義してループして使うのは、
処理が遅く（原因はNameの呼び出しが遅いこと）、使いものにならないとは思います。
（これはあくまでも例示ですので。）

実用を意識するなら、
特定のデータを拾い上げるのに、行、列の挿入の影響を受けない、
という意味では、結構有用だったりします。
実用的な例でいえば、名簿テーブルで、
　　[佐藤1]さんの[メールアドレス]
　　[田中]さんの[メールアドレス]
を取得するには、
　　[佐藤1]、[田中]の行、[メールアドレス]の列
を名前の定義で事前に設定しておけば、
　　Range("佐藤1 メールアドレス").Value
　　Range("田中 メールアドレス").Value
のように取得できますから、いちいち探さなくとも
簡潔な記述で済ませられるようになったりします。

俄かにはピンと来ないと思いますので、お暇な時にでも試してみて、
何かの機会に応用できる場面を見つけられたらいいなぁ、という程度の参考回答です。

///
# すみません、この場をお借りします。
///
# 突然ですが、keithin 様、
# その節はお世話になり、ありがとうございました。
# ところで、新設されたカテゴリ「Excel」の方にもご参加頂けること、
# 勝手ながら期待致しておりまする。
///
# お邪魔しました。

お礼

ありがとうございます。
使用する行と列に
行1、行2.....、列1、列2...
と名前を付ければ、
行列を挿入しても追従するように出来るのでしょうか？