[[20151224113956]]　『文字列になってしまっている数値を数列に直したい』（Lila）　ページの最後に飛ぶ

『文字列になってしまっている数値を数列に直したい』（Lila）

いつもお世話になっております。
毎日データベースから落としてくるファイルで、数値部分が文字扱いになっており、その部分を数値に修正した上、表示形式を数値にする処理をVBAにて行いたく、以下のようなものを書いてみました。

Sub 文字列を数列()

    Dim ws As Worksheet

    Set ws = Worksheets(3)
    ws.Activate

        '文字列を数値に変換する
        Range("D:D").Value = Range("D:D").Value
        Range("K:K").Value = Range("K:K").Value
        Range("L:L").Value = Range("L:L").Value

        '表示形式を標準から数値にする
        Range("D:D").NumberFormatLocal = "0_ "
        Range("K:K").NumberFormatLocal = "0_ "
        Range("L:L").NumberFormatLocal = "0_ "

End Sub

1.問題なく走る場合（D列、K列、L列が数値になります）
2.動いてはいるが文字列のままの場合（表示形式も変わらず）
3.1行目に書いてあるタイトルがそれぞれの列のセル全てに入ってしまう場合（タイトル文字が入ってしまいますが、表示形式は数値になっている）

何度か走らせていると、上記の3つのことが起こるのですが、何処を修正してよいのか解りません･･･。
また、何故このような事が起こるのかが謎で･･･ご教示下さい･･･＞＜
よろしくお願い致します。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

こんにちは

    Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("K:K").TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("L:L").TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

のように区切り位置を実行するコードに変更してみて下さい。

データが無い列の場合エラーになりますので、エラートラップかデータ数判定のロジックを入れて下さい。

（ウッシ） 2015/12/24(木) 11:56

 事前に各シートへテーブル設定してからセルの書式を変更してはどうか
 テーブルの名前を 配列Arへ格納してしているのは For句で連続処理する為
 これでも問題あれば中止して下さい｡

 Sub セルの書式変更()
    Dim Ar

    'テーブル名を配列へ
    Ar = Array(, "myTbl_1", "myTbl_2", "myTbl_3")

     Range(Ar(1)).NumberFormatLocal = "0_ "
 '   Range(Ar(1)).NumberFormatLocal = "#,##0_ "     '桁区切りの時
 End Sub

 ※データ追加は新行へ。データ部の各列のセル書式が新行にも保持される事を利用。
 　尚、罫線は無視されるので注意。データのコピペ後にまとめて処理するとか(VBAで)
（SSS） 2015/12/24(木) 15:34

 D,K,L という事を忘れいました。下記へ変更して下さい。

 Sub セルの書式変更()
 Dim Ar, Rg As Range

   'テーブル名を配列へ
    Ar = Array(, "myTbl_1", "myTbl_2", "myTbl_3")
    With Range("mytbl_1")
       Set Rg = Union(.Columns("D"), .Columns("K"), .Columns("L"))
    End With

    Rg.NumberFormatLocal = "0_ "
   'Rg.NumberFormatLocal = "#,##0_ "     '桁区切りの時
 End Sub

 もっとも、何故変更した書式が変わってしまうのか調べる事が先決では。
 使いまわしにしても不自然ですよね。
 一旦書式をクリヤしてから再設定してはどうですか？

 Sub 書式標準へ()
     Range("mytbl_1").NumberFormatLocal = "G/標準"
 End Sub
（SSS） 2015/12/24(木) 16:39

ウッシさん、SSSさんありがとうございます。

ウッシさん
＞データが無い列の場合エラーになりますので、エラートラップかデータ数判定のロジックを入れて下さい。
D列は空白のないデータになりますが、K列、L列はむしろ空白の方が多いデータになるのでこちら必要ですね･･･。
回答頂いているのを見て、試す前にこれを追加せねばと検索していましたが良く判らず･･･
で、まだ検証できていません；
すみません･･･；

SSSさん
＞もっとも、何故変更した書式が変わってしまうのか調べる事が先決では。
＞使いまわしにしても不自然ですよね。
＞一旦書式をクリヤしてから再設定してはどうですか？
これは直したいデータの事ですよね？
このデータは毎日更新されるデータベースからエクセルに落としたものになるので、ファイル自体は毎回別のものになります。
ちなみに、落としたエクセルで列を選択して表示形式を変更しても文字列のままで、エラーから「このセルを数字にする」を選択しないと手動では数値になりません。
SSSさんが書き込んでくださったものを今見たのでまだ検証出来ていませんが、この後検証してみます！
ありがとうございます^^
（Lila） 2015/12/24(木) 16:51

こんにちは

その列に一つでもデータが入っていればエラーにはならないので、

Sub test()

    If WorksheetFunction.CountA(Columns("D:D")) > 0 Then
        Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
    End If
    If WorksheetFunction.CountA(Columns("K:K")) > 0 Then
        Columns("K:K").TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
    End If
    If WorksheetFunction.CountA(Columns("L:L")) > 0 Then
        Columns("L:L").TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
    End If
End Sub

とかで、大丈夫かと思います。

（ウッシ） 2015/12/24(木) 16:59

ウッシさん
＞その列に一つでもデータが入っていればエラーにはならないので、
なるほどです。
指定した列が全て空白でなければ大丈夫、ということですね！

頂いたコードを実行じてみました！
結果はばっちりでした！
ありがとうございます^^

＞ Dim ws As Worksheet
＞ Set ws = Worksheets(3)
＞ ws.Activate
このコードは頭に付けておいても大丈夫でしょうか？？

SSSさん
頂いたコードを検証してみました。
結果としては、下記のエラーが出ました。

実行エラー「1004」の'Range'メソッドは失敗しました：'_Global'オブジェクト

というエラーが発生し、
＞With Range("mytbl_1")
ここが黄色くなりました。
まだ何故エラーなのかエラー番号などの検索が出来ていませんが、調べてみます。

が、何故かデータは数値になっていました。
謎です･･･
（Lila） 2015/12/24(木) 17:21

 すみません。ここの所を訂正しようかと思っていたのですが
 ＞With Range("mytbl_1") 　
   ⇒　With Range(Ar(1))　です。　※Ar配列には"mytbl_1"あるがマクロ内で無い変数となるから

 >が、何故かデータは数値になっていました
  これは摩訶不思議？？？　処理前なのに…
 ===================================================================
 以下感じた事など
 >> 一旦書式をクリヤしてから再設定してはどうですか？ 
  >これは直したいデータの事ですよね？

 マクロを組込んであるブックへデータをコピペして使用しているものと思っていました。
 若干手順が違っていましたね。
 【質問】落とされてくるデータ複数のシートに渡ってくるのですか。それとも単一シートですか。
 　　　　この事はWorkSheets(3)と書かれているので尋ねました。

 何れにしても原本をテーブル設定する事で良いのでは思います。
 ※複数のシートであれば、テーブル設定もマクロ化すれば良い。

 そもそも原因は思うに、毎回の手順が統一されていないからではないかな
 そして手間をかける暇が無いのなら、ウッシさん方式が最善かと思います。

（SSS） 2015/12/24(木) 18:09

 こちらの環境（xl2013)で、領域に数値書式を設定　＋　領域にその値を数値書式変換した値の代入
 この２段構えのコード処理をしてやると、文字列数字が数値に変換されました。

 そちらの環境で試してみていただけませんか。

 Sub Test()
    With Worksheets(3)
        With .Range("D1", .Range("D" & Rows.Count).End(xlUp))
            .NumberFormatLocal = "0_ "
            .Value = Application.Text(.Value, "0_ ")
        End With
        With .Range("K1", .Range("K" & Rows.Count).End(xlUp))
            .NumberFormatLocal = "0_ "
            .Value = Application.Text(.Value, "0_ ")
        End With
        With .Range("L1", .Range("L" & Rows.Count).End(xlUp))
            .NumberFormatLocal = "0_ "
            .Value = Application.Text(.Value, "0_ ")
        End With
    End With
 End Sub

（β） 2015/12/24(木) 18:55

おはようございます。

SSSさん
訂正箇所了解です！訂正してみてもう一度やってみます。

＞【質問】落とされてくるデータ複数のシートに渡ってくるのですか。それとも単一シートですか。
＞　　　　この事はWorkSheets(3)と書かれているので尋ねました。
落とされてくるのは、単一シートなので、ここに毎回コピペして使用するか、それとも、集計用のブックに移動させてから（タブ移動）使用するかはまだ決めかねているようなので、一応WorkSheets(3)をつけて作成していました。

何故かは解らないですが、アクセスからエクスポートすると、数値が文字列になり、書式変更をしても文字列のまま（列の書式自体は数値になっているのに）になってしまうみたいです。

βさん
いつもありがとうございます＞＜
コード検証してみます！
（Lila） 2015/12/25(金) 08:30

SSSさん

＞With Range(Ar(1))
へ修正してみたのですが、やはり同じエラーが出て、上記部分が黄色くマークされてしまいました･･･

βさん
ありがとうございます！
問題なく数値数列になりました^o^
（Lila） 2015/12/25(金) 09:16

   'テーブル名を配列へ
    Ar = Array(, "myTbl_1", "myTbl_2", "myTbl_3")

    With Range(Ar(1)) 
    With Range("mytbl_1")
 チグハグでした。上記はこの場合同じ意味。

 エラー原因として考えられるのは、
 ・ActiveSheetのテーブル名が違っている。
   テーブルツール/デザインでテーブル名確認できる
 ・数式→名前の管理でこのテーブル名の範囲がブックになっていない。（ブック全体）

 今一度チェックを。

（SSS） 2015/12/25(金) 10:49

SSSさん
あ、最初にテーブル？という機能を使用して、D,K,L列を入れておかなければいけない、という事ですか？？
（Lila） 2015/12/25(金) 12:01

 そうです
  ・ActiveSheetのテーブル名が違っている。
    テーブルツール/デザインでテーブル名確認できる

 作成方法
 　１）落とし込んだデータ内で右クリック
 　２）メニュー内のクイック分析を選択　→テーブルを選択→テーブル　　これでテーブル作成完了
 　３）上段に　テーブルツール/デザイン　というタブが現れる。ここで左端にあるテーブル名で名前を付与

 この名前を　数式タブ→名前の管理　で付与した名前が存在する。この時にその範囲がブックになっているか確認。

 ＞ Dim ws As Worksheet 
 ＞ Set ws = Worksheets(3) 
 ＞ ws.Activate 
 このコードは頭に付けておいても大丈夫でしょうか？？ 
 VBA自体はアクティブなシートがどれか判断できない。だから作業シートを明示する必要がある。
 これで Range("A1") = xxx とした場合に紛れもなく ws.Range("A1") を指す事になる。
 アクティブシートがWorksheets(1)であっても上記記述でマクロの実行から Worksheets(3)をアクティブとなる。
 数式→名前の管理でこのテーブル名の範囲がブックあれば直接 Range("定義された名前")とコードを書く事も可能。

 この項の本来の意味を再確認する事も必要でしょう。

（SSS） 2015/12/25(金) 15:09

SSSさん

テーブル機能初めて触りました^^;

検証結果はばっちり動きました^o^

タブごとにテーブルにしているので、
＞ With Range("mytbl_数値")
でどのタブなのか指定している、という解釈で問題ないでしょうか？

（Lila） 2015/12/25(金) 15:50

 >タブごとにテーブルにしているので、 
 > ＞ With Range("mytbl_数値") 
 >でどのタブなのか指定している、という解釈で問題ないでしょうか？

 ちょっと言葉の意味が違っているようです。（タブ）
 何れにしろ、ここでは各シートに１個づつのデータ領域が有ってそれぞれをテーブルとしたようですね。
 そして、其々の名前を "mytbl_数値" とした事を前提に説明します。

 名前の管理で付与された名前は、単独セルもしくは範囲を意味します。
                       （名前）    （活用）      
 例）Range("A1")　　　　Name1   Range("Name1")    ※名前の実体は本来の範囲（この列の左端）
     Range("A1:D20")    Name2   Range("Name2")

 今回はテーブル名（範囲）を指す、つまりその名前を範囲の代名詞として当ている訳です。
（SSS） 2015/12/25(金) 16:47

SSSさん

ご親切に解説ありがとうございます！
大量データでの、テーブル機能のやり方はとても便利そうなので、今後も活用させていただきます！
ありがとうございました＾＾
（Lila） 2015/12/28(月) 08:45

コメント返信：

[ 一覧(最新更新順) ]

[[20151224113956]] 『文字列になってしまっている数値を数列に直したい』（Lila） ページの最後に飛ぶ

[[20151224113956]]　『文字列になってしまっている数値を数列に直したい』（Lila）　ページの最後に飛ぶ