B列に文字がはいったらA列に数字が入るマクロードを完成させたい

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim lastNum As Long
Dim lastRow As Long

If Target.Column <> 2 Then Exit Sub 'B列以外は処理しない

Application.EnableEvents = False

lastRow = Cells(Rows.Count, 2).End(xlUp).Row 'B列の最終行を取得

For Each cell In Range("B" & lastRow)
If cell.Value <> "" Then
lastNum = lastNum + 1
cell.Offset(0, -1).Value = "(" & lastNum & ")" 'A列に、順番に番号を設定する
Else
cell.Offset(0, -1).Value = "" 'B列が空白の場合、A列も空白にする
End If
Next cell

Application.EnableEvents = True
End Sub

このコードを実行すると数字が-1などのマイナス表記になってしまいます。
(1)と表示するにはどうしたらよいでしょうか?
セルの書式設定のユーザー定義で "("0")"は使用しない方法を探しています
どなたか教えてください。

No.4 回答者： goomania 回答日時： 2023/04/22 17:30

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim lastNum As Long
Dim lastRow As Long

If Target.Column <> 2 Then Exit Sub 'B列以外は処理しない

Application.EnableEvents = False 'コーディングの作法として正しいが本件マクロではなくてもよいのでは

lastRow = WorksheetFunction.Max(Cells(Rows.Count, 1).End(xlUp).Row, Cells(Rows.Count, 2).End(xlUp).Row)

For Each cell In Range("B1:B" & lastRow) 'オリジナルコードの"B1"を"B1:B"に修正
If cell.Value <> "" Then
lastNum = lastNum + 1
'cell.Offset(0, -1).NumberFormatLocal = "@" 'こちらを有効にして、次行をシングルコーテーションなしにしてもよい
cell.Offset(0, -1).Value = "'(" & lastNum & ")" 'A列に、順番に番号を設定する"
Else
cell.Offset(0, -1).Value = "" 'B列が空白の場合、A列も空白にする
End If
Next cell

Application.EnableEvents = True 'コーディングの作法として正しいが本件マクロではなくてもよいのでは
End Sub

ご質問者の課題は
>数字が-1などのマイナス表記になってしまいます。
とのことです。
大変失礼とは存じますが、ご質問者の提示されたコードでは以下の2点の考慮がもれていると思います。

１．B列のセルが空白の場合、隣のA列セルには番号を付与せず、次の空白以外の行に連番を付与するコードなのにもかかわらず、B列のセルに値の消去・セルの削除が発生した場合の考慮がない

２．EXCELの仕様では(1)は-1を意味する。これは欧米の財務諸表では一般にマイナス数値を()で囲むから(日本の財務諸表で-1を▲1と表記するのと同様)といわれているが、それに対する考慮がない

上記1、2を考慮して修正すると、上記のようなコードになると思います。

上記１で指摘したように、ご質問者のオリジナルコードではlastRowにB列の最終行位置を代入していますが、例えばB列5行目まで入力して、A列に(1)～(5)まで表示されたとして、その後B5セルの値を消去するとか、B4セルを削除して上側につめるとかしたらどうなるでしょうか?
ご想像のとおり、(5)だけが意味なく表示された状態になってしまいます。つまり、A列、B列の最終行数の大きい方をlastRowに代入しなくてはいけません。
上記２で指摘したように、()で囲んだ数値はマイナスを意味しますので、シングルコーテーションを付けて文字列にする必要があります。
あるいは、()で囲んでもマイナス数値と認識されなければよいわけですから、直前に
cell.Offset(0, -1).NumberFormatLocal = "@"
を記述して文字列にしてしまえば、
cell.Offset(0, -1).Value = "(" & lastNum & ")"
はそのまま、シングルコーテーションなしで記述するという手もあります。
さらに言えば()で囲むから上記２の問題が発生するわけですから、()の替わりに【】を使って
cell.Offset(0, -1).Value = "【" & lastNum & "】"
とするだけでマイナス表記を回避することは可能です。

No.3 回答者： fujillin 回答日時： 2023/04/21 10:36

こんにちは

横から失礼します。

全く別の発想になってしまいますが、以下のコードを標準モジュールに記入して、事前に一度だけ実行しておくという案ではダメでしょうか？

Sub hoge()
Const f1 = "=IF(B1="""","""",IF(ROW()=1,1,"
Const f2 = "MAX(OFFSET(A$1,,,ROW()-1))+1))"
Range("A1:A999").FormulaLocal = f1 & f2
End Sub

No.2 回答者： tatsumaru77 回答日時： 2023/04/21 09:00

以下のようにしてください。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim lastNum As Long
Dim lastRow As Long

If Target.Column <> 2 Then Exit Sub 'B列以外は処理しない

Application.EnableEvents = False

lastRow = Cells(Rows.Count, 2).End(xlUp).Row 'B列の最終行を取得

For Each cell In Range("B1:B" & lastRow)
If cell.Value <> "" Then
lastNum = lastNum + 1
cell.Offset(0, -1).Value = "'(" & lastNum & ")" 'A列に、順番に番号を設定する
Else
cell.Offset(0, -1).Value = "" 'B列が空白の場合、A列も空白にする
End If
Next cell

Application.EnableEvents = True
End Sub
----------------------------------
変えたのは
For Each cell In Range("B" & lastRow)を
For Each cell In Range("B1:B" & lastRow)

cell.Offset(0, -1).Value = "(" & lastNum & ")"を
cell.Offset(0, -1).Value = "'(" & lastNum & ")"
です。

No.1 回答者： break_time 回答日時： 2023/04/21 05:13

以下ChatGPTによる回答です。

正しいかどうかはご自身で検証してください。
以下のコードを使用して、(1)という形式でA列に番号を設定することができます。

vbnet
Copy code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim lastNum As Long
Dim lastRow As Long

If Target.Column <> 2 Then Exit Sub 'B列以外は処理しない

Application.EnableEvents = False

lastRow = Cells(Rows.Count, 2).End(xlUp).Row 'B列の最終行を取得

For Each cell In Range("B" & lastRow)
If cell.Value <> "" Then
lastNum = lastNum + 1
cell.Offset(0, -1).Value = "(" & Format(lastNum, "0") & ")" 'A列に、順番に番号を設定する
Else
cell.Offset(0, -1).Value = "" 'B列が空白の場合、A列も空白にする
End If
Next cell

Application.EnableEvents = True
End Sub
上記のコードで使用されているFormat関数の第2引数には、数値のフォーマットを指定する文字列を渡します。"0"は数字を表し、括弧内に表示される数字をゼロ埋めせずに表示します。したがって、"(1)"という形式でA列に番号を設定することができます。