VBAでセルデータの転記（文字列）

質問

VBAでセルのデータを下記のように他のセルに持っていく場合、文字として入れてある数字（例えば先頭に０がつくようなもの）は、自動的に数値に変えられて０が消えてしまいます。
また、長いものは指数表示になってしまい、15桁を超えるものは後ろが０になってしまいます。
持っていく先のセルを文字列に設定しておけばいいのでしょうが、持っていく元データが全て文字列ではなく、数値の場合もあります。
元データが数値であれば数値として、文字列であれば文字列として持っていくにはどうすればいいのでしょうか？

Sub test()
Cells(3, 3).Value = Cells(1, 1).Value
End Sub

回答No.4

KenKen_SP · ベストアンサー

#3 maruru さま

＞文字列を表す接頭文字「'」は、PrefixCharacterプロパティで参照出来ます。
＞（参照だけで、設定は出来ません。）

おお。なるほど。知りませんでした(^^;)
勉強になりました。ありがとうございます。

では、merlionXX 様、蛇足かもしれませんが、

Sub Sample()

　　Dim rngOrg As Range
　　'元データ
　　Set rngOrg = ActiveCell 'Cells(3, 3)
　　'書込み先セル
　　With Cells(1, 1)
　　　　.NumberFormat = rngOrg.NumberFormat
　　　　If Len(rngOrg.PrefixCharacter) > 0 Then
　　　　　　'接頭文字あり(Prefixを追加して転記）
　　　　　　.Value = "'" & rngOrg.Value
　　　　Else
　　　　　　'接頭文字なし（そのまま転記）
　　　　　　.Value = rngOrg.Value
　　　　End If
　　End With

End Sub

こんな風に、PrefixCharacterプロパティで接頭辞の有無を調べ、あれば接頭辞を追加して値を転記するようにすれば、セル書式も生かされますね。

お礼

なんどもありがとうございました。
感謝いたします。

回答No.3

maruru01

こんにちは。maruru01です。

文字列を表す接頭文字「'」は、PrefixCharacterプロパティで参照出来ます。
（参照だけで、設定は出来ません。）
このプロパティは、接頭文字が付いていれば「'」を、付いていなければNullを返します。
したがって、文字列長で判別してやります。
No.2の方のコードを拝借して、こんな感じになります。

Sub Sample()

　　Dim rngOrg As Range
　　'元データ
　　Set rngOrg = ActiveCell 'Cells(3, 3)
　　'書込み先セル
　　With Cells(1, 1)
　　　　If Len(rngOrg.PrefixCharacter) > 0 Then
　　　　　　'接頭文字あり（文字列型にする）
　　　　　　.NumberFormat = "@"
　　　　Else
　　　　　　'接頭文字なし（元のデータ型をコピー）
　　　　　　.NumberFormat = rngOrg.NumberFormat
　　　　End If
　　　　'データ転記
　　　　.Value = rngOrg.Value
　　End With

End Sub

お礼

ありがとうございます。
こういうやりかたもあるのですね。
勉強になります。

回答No.2

KenKen_SP

#1です。

＞書式は「標準」でデータの頭にシングルクォーテーションを入れて文字列化した
＞数字の場合は書式の転記ではだめでした。

それなら、VarType関数でデータ型を調べ、かつ数値化可能なデータの場合、セル書式を文字列に、その他のデータ型はコピー元と同じ書式にしておくのはどうですか？
元データのシングルクォーテーションは無くなりますが。

Sub Sample()

　　Dim rngOrg As Range
　　'元データ
　　Set rngOrg = ActiveCell 'Cells(3, 3)
　　'書込み先セル
　　With Cells(1, 1)
　　　　'元データセルのデータ型が文字列かつ数値化可能であれば、
　　　　'転記先セルの表示形式を文字列に設定しておく
　　　　If VarType(rngOrg.Value) = 8 And _
　　　　　　IsNumeric(rngOrg.Value) Then
　　　　　　.NumberFormat = "@"
　　　　Else
　　　　　　'その他のデータ型ならそのまま
　　　　　　.NumberFormat = rngOrg.NumberFormat
　　　　End If
　　　　'データ転記
　　　　.Value = rngOrg.Value
　　End With

End Sub

お礼

ありがとうございました。
今度は大丈夫です。
助かりました。

回答No.1

KenKen_SP

こんにちは。

難しく考えず、転記前に元データセルの書式を調べておき、転記先セルにそれを予め設定してから値を転記すれば良いかと。

Sub Sample()

　　'元データ
　　Set rngOrg = Cells(4, 3)
　　'書込み先セル
　　With Cells(1, 1)
　　　　'元データのセルと同じ表示形式を設定しておく
　　　　.NumberFormat = rngOrg.NumberFormat
　　　　'データ転記
　　　　.Value = rngOrg.Value
　　End With

End Sub

補足

さっそくありがとうございます。

試してみましたが、以下の点で躓きました。

元データのセルが文字列となっていれば問題なしです。
しかし、書式は「標準」でデータの頭にシングルクォーテーションを入れて文字列化した数字の場合は書式の転記ではだめでした。

データ数がたくさんあり困っています。