vba セルの行頭に連番を付加

質問

セルの行頭に連番を付加したいので下記のようなコードを作成しました。

B列をナンバリング用の仮セルとして使いましたが
仮セルを利用しない方法はありますか　？

Option Explicit

Sub セル連番付加()
Dim i As Long

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row

With Cells(i, "B")
.Value = i - 1
.NumberFormatLocal = "@"
.Value = Format(.Value, "00")
End With

Cells(i, "C") = Cells(i, "B") & " " & Cells(i, "A")
Next

End Sub

回答No.5

kkkkkm · ベストアンサー

> Dim Temp As Variant　とTemp()では無いので配列でしょうか？
> Temp(i - 1, 1)と2次元配列のように見えますが？

セルの値を一気に入れた場合()を省略しても2次元配列になります。
私が最初に参考にしたサイトでは()が無かったので、そのままの流れで()無しにしてます。
今ちょっと検索しても()無しで説明しているサイトが殆どでした。

()を使っても使わなくてもいいと記載があったのはこちらだけでした。
https://excel-ubara.com/excelvba1/EXCELVBA414.html

> 配列は、Option Baseで1を宣言しない限り配列の添え字の最小値は「0」からになるので
> A列の左端は、temp(0,1)となるの無いですか？

通常はそうなのですが、一気に入れた場合は1からになります。
同時に利用する他の配列があればそれは0からになります。
仕様だと思ってください。

お礼

解説ありがとうございます。

配列は、今まで利用することが自分的には無かったので知らないことばかりです。

教えていただいた事を元に解説を付けたコードが下記となります。

Sub セル連番付加配列_4()
'A列を配列に入れて処理する
'データ量が多い場合には、データを一気に多次元配列に入れて配列で処理して
'一気にセルに書き戻すと処理が早くなります。

Dim i As Long

Dim Temp As Variant
'セルの値を一気に入れた場合配列で()を省略しても2次元配列になります。
'セル範囲のValueを入れた配列の下限は1になります。
'LBoundが必ず1になる点に注意して下さい｡
'つまり
'配列は、Option Baseで1を宣言しない限り配列の添え字の最小値は「0」からですが
'配列に一気に入れた場合は1からになります。
'同時に利用する他の配列があればそれは0からになります。
'これは、EXCELの仕様だと思ってください。

If Range("A2") = "" Then
MsgBox "処理DATAが入力されていません。" & Chr(13) & "処理を中止します。！！"
End
Else
End If

'書き出しセル初期化
Range("B2:B100").Clear

Temp = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
'セル範囲をVariant型変数に入れる事で、配列を作成することができます。
'Temp(1 To Cells(Rows.Count, "A").End(xlUp),1)のような縦横の2次元配列

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Temp(i - 1, 1) = Format(i - 1, "00") & " " & Temp(i - 1, 1)

Next

Cells(2, "B").Resize(UBound(Temp), 1).Value = Temp
'これで、配列に格納したデータを一括でセルに出力する事が出来ます。
'UBoundを使うと配列の最大インデックス数を取得
' さらに「Resize」と組み合わせることで配列の範囲を自動調整
' 「Resize」+「UBound」で配列範囲を自動調整　Resize(UBound(Temp), 1)の型式は定番
End Sub

回答No.4

kkkkkm

> Cells(行番号, 列番号)
> と同じ考えで処理できます

説明が下手でした。
Cells(1,1)
はA1を示しますが
Temp(1, 1)
は読み込んだセルの左上端のセルの値になります。
今回の場合は
Cells(2, "A")
が左上端なので
A2の値
になります。

お礼

Sub セル連番付加配列_2()とSub セル連番付加配列_３()の違いが
説明でされると理解できました。

Sub セル連番付加配列_３()でのTempが（）付きで無いのを見をとしていました。
配列の方に目が言っていて普通の変数である事を
「上記の場合単に変数でもいいのではと思います。」と説明されていたのに
「上記」に引っ張られて上に書いていた「Sub セル連番付加配列_2」の事思っていたのが大きなミスになりました。

kkkkkmさんの「Sub セル連番付加配列2()」についてですが
（私のコードの「Sub セル連番付加配列_2()」と見た目が見ているので紛らわしいのですが....）

記載されているのは、
Dim Temp As Variant　とTemp()では無いので配列でしょうか？
Temp(i - 1, 1)と2次元配列のように見えますが？

又、

配列は、Option Baseで1を宣言しない限り配列の添え字の最小値は「0」からになるので
A列の左端は、temp(0,1)となるの無いですか？
range("A1")つまりcells(1,1)は見出し列で実際のDATAは、range("A2")つまりcells(2,1)
となりますが、添字の関係が今市見えてきません。

tempの配列
temp(0,1)
temp(1,1)
temp(2,1)

回答No.3

kkkkkm

> Preserveで「データの値を保持したまま配列の要素数を変更する」のはなぜですか？

配列にしたのにそれまでの値を保持せず、以後その配列を利用しないのでしたら、配列ではなく「単に変数でも」になると思います。質問で配列にとあったのでその後になにかしら利用する可能性を考えてデータを保持しています。

> Sub セル連番付加変数_3()について
>
> ここでは、Dimで最初の配列を宣言しただけで
> ReDimで配列を再宣言していませんそれでも正常にB列に出力されています。

配列ではなく普通の変数ですので。

> 最後の「A列を配列に入れて処理する」するマクロは
> 私には、理解が難しい内容です。

配列繋がりで記載しました。データ量が多い場合には、データを一気に多次元配列に入れて配列で処理して一気にセルに書き戻すと処理が早くなります。きっと。

Temp = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
で一気に読み込んで
Temp(行番号, 列番号)
となりますから
Cells(行番号, 列番号)
と同じ考えで処理できます。ただし、セルに対するプロパティやメソッド及び列番号に"A"などは使えません。

回答No.2

kkkkkm

> 配列に一時的に値を格納する事で処理する事

値を配列に格納するということで思いついたものを

Sub セル連番付加配列()
Dim i As Long
Dim Temp() As Variant

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
ReDim Preserve Temp(i - 1)
Temp(i - 1) = Format(i - 1, "00")
Cells(i, "C") = Temp(i - 1) & " " & Cells(i, "A")
Next

End Sub

上記の場合単に変数でもいいのではと思います。

Sub セル連番付加変数()
Dim i As Long
Dim Temp As Variant

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Temp = Format(i - 1, "00")
Cells(i, "C") = Temp & " " & Cells(i, "A")
Next

End Sub

A列を配列に入れて処理する

Sub セル連番付加配列2()
Dim i As Long
Dim Temp As Variant

Temp = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Temp(i - 1, 1) = Format(i - 1, "00") & " " & Temp(i - 1, 1)
Next
Cells(2, "C").Resize(UBound(Temp), 1).Value = Temp
End Sub

お礼

配列を利用したコードをありがとうございます。

教えてもらったコードを参考に以下のように一部修正しました。

Sub セル連番付加変数_2()について

ReDim Preserve Temp(i - 1) と配列の要素数を宣言していますが
ReDim Temp(i - 1) としてもB列の結果に変化は無いのですが
Preserveで「データの値を保持したまま配列の要素数を変更する」のはなぜですか？
（ReDim Preserve Temp(i - 1) では無くReDim Temp(i - 1)としないのはなぜですか？）

Sub セル連番付加変数_3()について

ここでは、Dimで最初の配列を宣言しただけで
ReDimで配列を再宣言していませんがそれでも正常にB列に出力されています。

ReDimを使用すべきかの判断がよく判りませんでした。

最後の「A列を配列に入れて処理する」するマクロは
私には、理解が難しい内容です。

Sub セル連番付加配列_2()
Dim i As Long
Dim Temp() As Variant '可変長配列の宣言 (宣言時には要素数は指定しない)

'ReDimステートメントを使って配列の要素数を変更した場合､
' 元のデータはすべて消えてしまいますので注意してください!

'また、データ型の変更が可能なのは,
' 可変長配列の宣言時にバリアント型で宣言した場合だけになります｡

'データの値を保持したまま配列の要素数を変更したい場合は,
' ReDim Preserveステートメントを使用します。

If Range("A2") = "" Then
MsgBox "処理DATAが入力されていません。" & Chr(13) & "処理を中止します。！！"
End
Else
End If

'書き出しセル初期化
Range("B2:B100").Clear

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
ReDim Preserve Temp(i - 1) 'ReDimで配列の要素数の上限を指定する
Temp(i - 1) = Format(i - 1, "00")
Cells(i, "B") = Temp(i - 1) & " " & Cells(i, "A")
Next

End Sub

'-----------------------------------------------------------------

Sub セル連番付加変数_3()
Dim i As Long
Dim Temp As Variant

If Range("A2") = "" Then
MsgBox "処理DATAが入力されていません。" & Chr(13) & "処理を中止します。！！"
End
Else
End If

'書き出しセル初期化
Range("B2:B100").Clear

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Temp = Format(i - 1, "00")
Cells(i, "B") = Temp & " " & Cells(i, "A")
Next

End Sub

回答No.1

kkkkkm

B列に入れずにそのままでいけると思います。

Sub セル連番付加()
Dim i As Long

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(i, "C") = Format(i - 1, "00") & " " & Cells(i, "A")
Next

End Sub

お礼

kkkkkmさん、ありがとうございます。
いただいたコードで処理できました。

今回は、仮セルを使わずに処理できましたが
出来ない場合を想定して
配列に一時的に値を格納する事で処理する事も出来ると思いますが
その場合はどのようなコードになりますか？

私が最初に例に上げたコードを元に教えていただければ嬉しいです。