VBA 郵便番号にハイフンをいれたい

Question

A列にある7桁の数字(ハイフンが入っていたり入っていなかったりする)を
全てハイフンが入る"000-0000"に統一したいです。

Dim Rng1 As Range
    For Each Rng1 In Range("A1", Cells(Rows.Count, 1).End(xlUp))
    If Len(Rng1.Text) = 7 And IsNumeric(Rng1.Value) Then
        Rng1.Value = Format(Rng1.Value, "000-0000")
    End If
    Next

ネットにあるコードを使用しましたが、反映されません。
自分でもFormat関数を使用してコードを書きましたが、値が全て"000-0000"になってしまいます…

何か書き方が違うのでしょうか？

Comments

[itagagaki]

問題は再現できませんでした。
A列で7桁の数字のセルは、たとえば1234567なら123-4567になりました。
なので原因はわかりませんが、試しにA列の書式を予め標準か文字列にしてみてはどうでしょうか。

[hatena19]

提示されていない部分のコードで何かしている可能性が大。
０にしているとか。

[YT0014]

Rng1.Valueの変換前の値をDebug.Printなどでご確認いただきご提示ください。

[12356]

皆様、ご回答ありがとうございます。
データに「 ' 」が入り混じっていたのでそちらが原因かもしれません。

色々とご教示いただきありがとうございました。

[YT0014]

12356さん、その場合、IsNumeric()がfalseになるでしょうし、Len()の結果も異なるので、IFの中は実行されないと思うのですが。
当該セルで、Format()が実行されているのは確認されましたか？

[12356]

YT0014様、ご回答ありがとうございます。
上記コードでは実行されませんでした。

見直しをしてみます。

Answer 1

'//使用セルの最終行を取得
    Dim fin_line As Long, k As Long
    
    With ws.UsedRange
        fin_line = .Rows(.Rows.Count).row
    End With
 For k = 2 To fin_line

 If Len(Range("A" & k).Text) = 7 And IsNumeric(ws.Range("A" & k).Value) Then
   ws.Range("A" & k).Value = Format(ws.Range("A" & k), "000-0000")
   
  End If
Next

皆様のお手を煩わせました。
ご回答いただきありがとうございました。

Answer 2

ご提示いただいたコードと動作を見る限り、問題のセルの値は文字列になっているために、Format()では、"0000-000"を、書式外として、そのまま出力しているものと思われます。

追記：
申し訳ございません。上記記述は誤りで、文字列でも、問題ない事を確認いたしました。
動作は、仕様書だけでなく、実際の動作を確認するべきと、己を戒めます。

確認のため、VarType(Rng1.Value)の値を出力してみてください。
VarType 関数

修正方法ですが、Val()を使って、強制的に数値にすれば、Format()が使えるかと思います。
Val 関数

コーディングの際は、使用する関数などの仕様は正確に把握するようにしてください。

以下に今回の問題点を挙げておきます。参考になれば幸いです。

・IsNumeric()は、数値型であることを判定しているのではなく、数値として処理できることを判定しています。
つまり、数字の１だろうと、文字列の"1"だろうと、Trueと判定します。
IsNumeric 関数

・Format()は、与えられたデータの型により、動作が異なる関数です。
"0000-000"が郵便番号の書式として機能するのは、データが数値型の場合のみです。
Format 関数

Comments

[itagagaki]

セルの書式が文字列になっていても Format(Rng1.Value, "000-0000") は効きました。
（Excel 2013）

[hatena19]

私の Microsoft 365 のExcelでも、文字列で "000-0000" はききますね。

Debug.Print Format("0123456", "000-0000") ’012-3456

[YT0014]

ご指摘ありがとうございます。
手元のExcel2013でも、確認できました。
回答に誤りであったことを追記させていただき、削除を申請しましたが却下されました。

Answer 3

たんに、先頭３文字取り出してハイフン入れれば入れればいいだけなんでは。

全てハイフンが入る"000-0000"に統一したいです。

自分でもFormat関数を使用してコードを書きましたが、値が全て"000-0000"になってしまいます…

そうなってるってはなしなんでは