セルの書式設定を変えたのに反映されない

セルの書式を文字列にしてデータを入力している場合に、数値や数式を使っているセルの場合は標準の書式設定にして利用することがあります。

その場合、書式を文字列から標準などに変更するのですが、なぜか元の表示のままで数値や数式として認識されません。昔からこうなんで調べてませんがおそらくMicrosoftは「仕様です」と言い切っちゃってるんだと思います。

これを解決するにはセルの中にマウスカーソルを置いてEnterキーを押して、再度入力しなおしたような操作をする必要があります。ただ、これを1セルずつやるのはとても面倒です。

他の方法としては「区切り位置」機能を使う方法があります。1列ずつ選択して「データ」タブ→「データツール」→「区切り位置」をクリックし、表示されたダイアログの「完了」ボタンを押すと、変更後の書式で再表示されます。

ただ、この方法も列数が多いと面倒です。以下ではこれを自動化するマクロを紹介します。


コード

以下のコードは入力セル範囲の全ての列に対して「区切り位置」機能を使ったセルの再表示を行います。セルの書式を変えたところがなければ実行しても何も変わりません。

処理内容ですが、「区切り位置」機能をExcelで行うのと同じように実行しています。具体的には、入力セル範囲の列数だけ、列の選択を行って区切り位置機能の実行しています。

注意点として、「区切り位置」機能のTextToColumnsメソッドのDestinationプロパティの設定があります。マクロの記録機能を使うとDestinationプロパティにはActiveCellが指定されますが、それだと問題が起きます。

このプロパティは変更後の内容をどのセルに反映させるかを指定するのですが、行位置と列位置を入力セル範囲に合わせて指定しないと、実行後に違うところに値が設定されてしまいます。

例えば、C5セルからG17セルまで間で値を入力しているような場合、上4行と左2列が未入力の状態になります。上4行が未入力のため、DestinationプロパティにActiceCellを指定すると、実行後にセルが4つ上にずれることになります。以下の関数はそれを考慮して行位置と列位置をDestinationプロパティに指定するようにしています。

使い方

セルの書式を文字列から標準に変更して、上の関数を実行するだけです。

例えば、「123」と入力された文字列書式のセルの場合、左寄せで表示されていますが、数値書式に変えて上の関数を実行すると右寄せになり数値として認識できるようになります。