Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
Split関数で抜き出した値の書式について
投稿日時: 17/10/01 15:23:37
投稿者: hmisato1123

先日「sheetのコピー時に連続した名前に変えたい 」のトピックで質問しました。その節はありがとうございました。どうにか理解はできました。ところが以前の質問より簡単なはずの問題で困っています。
よろしくお願いします。
以下の表があります。この表のB列にA列の _ の後の値を抽出しようとして以下の式を作りました。
Sub hairetu9()
    Dim i As Long, tmp As Variant
        For i = 2 To 10
          tmp = Split(Cells(i, 1), "_")
          Cells(i, 2) = Format(tmp(1), "00")
        Next i
End Sub
 
  A列  B列
  日付    
1 08_01
2 08_02
3 08_03
4 08_04
5 08_05
6 08_06
7 08_07
8 08_08
9 08_09
 
するとどうしても01、02にならず0,1のようになってしまいます。
この式のどこに問題があるでしょうか?ご教示いただけるとありがたいです。
 

回答
投稿日時: 17/10/01 15:27:43
投稿者: simple

B列の書式を予め 文字列 にしておいてはどうでしょうか。

回答
投稿日時: 17/10/01 15:50:38
投稿者: WinArrow
投稿者のウェブサイトに移動

幾つかの方法があります。
 
方法1
simpleさんレス・・・
B列をあらかじめ文字列にしておく
 
方法2
コードでB列を文字列を設定する
 
※基本的にループは必要ないので
ループ、SPLITを使わない方法
 
方法3
B列セルに数式を入力する
 
Cells(2,"B").Resize(10).Formula = "=RIGHT(A2,2)"
 
 
方法4:方法3の変形
Cells(2,"B").Resize(Cells(ROws.Count).End(xlUp).Row-1).Formula = "=RIGHT(A2,2)"
 
なお、RIGHT関数は、MID関数でもよい
 

回答
投稿日時: 17/10/01 16:00:12
投稿者: simple

私の発言の補足です。
書き込むものは"01"のような文字列です。そこまでは間違っていません。
 
これをセルに書き込むとき、Excel君が"気を利かす"わけです。
これは手作業で、標準設定のセルに 01と入力してリターンを押すと、
1 となるでしょう? これと同じことなんです。
 
だけど予め文字列書式に設定しておくと、
Excel君も意図を察知して 01 と受け入れてくれます。
これと同じ事です。

投稿日時: 17/10/01 17:20:59
投稿者: hmisato1123

simpleさん、WinArrowさん ありがとうございました。
両方やってみました。うまくいきました。
ただどうしても疑問が残ります。
format関数を使って、書式を"00"としてあるのに01と表示されないのがすっきりしません。

回答
投稿日時: 17/10/01 18:02:04
投稿者: sy

こんにちわ。
 

hmisato1123 さんの引用:
format関数を使って、書式を"00"としてあるのに01と表示されないのがすっきりしません。

それはExcelの仕様です。
セルの書式を標準や数値などにしていれば、先頭についた0は無視されます。
 
Format関数で変数内では文字にしていても、セルに代入する時は値で代入(書式は反映されない)しているので、キーボードから01などと入力するのと同じ扱いになるからです。
キーボードから入力する時に「ひらがな」や「全角英数」などにして全角や半角の文字として入力しても先頭の0は省略されて数値になるのと同じです。

回答
投稿日時: 17/10/01 19:11:45
投稿者: WinArrow
投稿者のウェブサイトに移動

覚えておいた方がよいと思うことをレスします。
 
表示形式:「標準」
これは、スタンダードという意味ではなく、
Excel君にお任せ
と覚えておいた方がよいでしょう。

回答
投稿日時: 17/10/01 19:12:28
投稿者: ピンク

こんばんは
セルに01と打ち込んでも数字と判断され1と表示されます。
01と表示するのならセルの書式を文字列にするか
もしくは01の頭にアポストロフィー「'」を付けると良いでしょう
Sub hairetu10()
    Dim i As Long
    For i = 2 To 10
        Cells(i, 2) = "'" & Split(Cells(i, 1), "_")(1)
    Next i
End Sub

回答
投稿日時: 17/10/01 19:36:36
投稿者: 菊りん0828

hmisato1123さんこんばんは。
皆さん、こんばんは。
横から失礼致します。
先ほどからずーっとROMしていました。
 
>ただどうしても疑問が残ります。
>format関数を使って、書式を"00"としてあるのに01と表示されないのがすっきりしません。
 
凄くこだわりを感じる素晴らしい一文なので、居ても立ってもいられずコメントします。
 
>これをセルに書き込むとき、Excel君が"気を利かす"わけです。
by simpleさん
>それはExcelの仕様です。
>セルの書式を標準や数値などにしていれば、先頭についた0は無視されます。
by syさん
>01と表示するのならセルの書式を文字列にするか
by ピンクさん
 
>幾つかの方法があります。
by WinArrowさん
 
私の主観なのですが、コード内で処理を完結されたいのでしょうか?
(事前にセル等の設定をしないで・・・)
 
simpleさんがおっしゃる「Excel君が気を利かせないようにする」
syさんがおっしゃる「仕様」
に関して抑止できるようなコードは私には書けません。
 
以上から、いささか妥協案になってしまうのかもしれませんが、
WinArrowさんが提案されている
>方法2
>コードでB列を文字列を設定する
 
に関しては如何でしょうか?
simpleさんも早々に
>B列の書式を予め 文字列 にしておいてはどうでしょうか。
と書き込まれています。
コードにするとこんな感じ?
----------------------------------------------------------
 
      For i = 2 To 10
         tmp = Split(Cells(i, 1).Value, "_")
         With Cells(i, 2)
            .NumberFormatLocal = "@" ←ココ
            .Value = Format(tmp(1), "00")
         End With
      Next i
----------------------------------------------------------
B列・・・という列単位ではなく、入力されるセルに対して事前に「文字列」の設定を行っています。
>01と表示するのならセルの書式を文字列にするか
ピンクさんにモロ被りです。
With 〜End With の箇所は少し書き換えました。
 
蛇足ではありますが、
 
>Cells(i, 1)
のような箇所は
 Cells(i, 1).Value 等、最後まで書いた方が良いと見たことがあります。
 もしよろしかったら、以後実践で気を配ってみて下さい。
 
見当違いでしたらすいません。

回答
投稿日時: 17/10/02 10:10:14
投稿者: WinArrow
投稿者のウェブサイトに移動

> .NumberFormatLocal = "@" ←ココ
  
コードハ、間違っていませんが、
  
simpleさん、私が言っている
予めB列に文字列を設定する
ということとは、異なります。
 
どうしても、ループで対処したのでしたら、
ループに入る前に
Columns("B").NumberFormatLocal = "@" ←ココ
 
と記述した方が無難です。

投稿日時: 17/10/02 16:08:30
投稿者: hmisato1123

simpleさん、syさん、WinArrowさん みなさん ありがとうございました。
ピンンクさん 早速やってみました。あ!そうか という感じでした。
菊りん0828 さん 気持ちを分かっていただけて嬉しかったです。早速やってみました。うまくいきました。
NumberFormatLocalは知りませんでした。研究します。
 
もう一度言わせてください。
みなさん ありがとうございました。