マクロでLen関数の使い方がわかりません。

質問

マクロでLen関数の使い方がわかりません。

基幹システムのデーターベースをエクセル出力する機能があります。
その吐き出したエクセルファイルを使って
印刷したり、別のシステムにインポートしています。

このデーターの行数は吐き出すたび違います。
吐き出したエクセルのＤ列にあるデータですが
ここは必ず半角数字7桁固定長と決まっています。
7桁に満たないデータは0で埋めて7桁になっています。
基幹システム上でも7桁で表示されています。
例↓
0000002
0002222
0000000

ですがエクセルに吐き出されると桁数が変わってしまいます。
（なぜか0は空白にならず0と表示されています）
書式設定は[標準]でエラーマークが出ていて
（数値が文字列として保存されています）となっています。
例↓
2
2222
0

印刷時は後者、インポート時には前者である必要があるので
以下のようにＥ列は残したままＦ列に7桁表示を加えたいです。

　　　　　E　　　→ F
--------------------------
1　　　　　2 　→ 0000002
2　　1234567 →　1234567
3　　　 333　→　0000333　
4 　 22 → 0000022
5 　 4444 → 0004444
6 　 0 → 0000000

以下のマクロを作成しましたが動作しません。
Select Caseでという考え方がまずいのでしょうか？
（それ以前にLen関数の使い方もわかっていません。）
どうかよろしくお願いします。

Sub 七文字化()
行 = 2
Do
If Cells(行, 2).Value = "" Then Exit Do
n=Cells(行,2）
Select Case True
'6文字
Case Len(n) = 6
'先頭に0を追加
myStr = "0" & n
'5文字
Case Len(n) = 5
'先頭に0を追加
myStr = "00" & n
'4文字
Case Len(n) = 4
'先頭に0を追加
myStr = "000" & n
'3文字
Case Len(n) = 3
'先頭に0を追加
myStr = "0000" & n
'2文字
Case Len(n) = 2
'先頭に0を追加
myStr = "00000" & n
'1文字
Case Len(n) = 1
'先頭に0を追加
myStr = "000000" & n
'7文字ある時
Case Else
myStr = n
Cells(行, 3).Value = myStr
End Select
行 = 行 + 1
Loop
End Sub

回答No.8

hananoppo · ベストアンサー

ANo.4です。データの始まりはE1セルではなく、E2セルということでしょうか。
それなら、次のマクロでよいです。

Sub 七文字化()
Dim 行 As Long

For 行 = 2 To Range("E2").End(xlDown).Row
Cells(行, 6).NumberFormatLocal = "@"
Cells(行, 6).Value = Format(Cells(行, 5).Value, "0000000")
Next 行
End Sub

お礼

わざわざ、ありがとうございます。

はい、今回の記述で正しく動作しました。

説明が悪くて申し訳ありません。

補足

さしつかえなかったら教えてください。

データがあるまで繰り返すループではなく

例えばセルH1に
234
と入っていて同じようにセルH1はそのままで
セルB1に
000024
とするには

.Range("B1").NumberFormatLocal = "@"
.Range("B1").Value = Format(Range("H1").Value, "0000000")

でいいのでしょうか？
よろしくお願いします。

回答No.12

conanthe

ANo.3です。次の式を試してください。

Sub 七文字化()
行 = 2
Do Until Cells(行, 2) = ""
Cells(行, 3).NumberFormatLocal = "@"
Cells(行, 3) = Format(Cells(行, 2), "0######")
行 = 行 + 1
Loop
End Sub

お礼

わざわざすいません。

同じくコピペで試しました。

　’　は無く　７文字になりました。

どうもありがとうございます。

自分で作成したSelect Caseの問題部分修正版の他に
同じ結果になる物を皆さんから3種類も教えていただきました。

回答No.11

mu2011

NO9です。
続き質問はマナー違反です。別スレッドで質問を行って下さい。
因みに234⇒0000024は補足のコードでは実現できません。ワークシート関数を使用した方が良いと思う。

お礼

申し訳ありません。
今後は別スレッドで質問します。

それから質問をまた間違えました。

234→0000024

では無く【これでは今回の質問と全然違います。】

今回の質問と同じく
0をつけて７文字にするが繰り返しはしないで
H1に　234　で
B1に　0000234　と複写する

でした。すいませんでした。

補足に記述した

Sub 単一7文字化()

Range("B1").NumberFormatLocal = "@"
Value = String(7 - Len(Range("H1")), "0") & Range("H1")
Range("B1") = Value

End Sub

で正しく動きました。
どうもありがとうございました。

回答No.10

hananoppo

ANo.8です。補足の通りのコードでよいですよ。

お礼

申し訳ありません。
補足内容を間違えました。

例えばセルH1に
234
と入っていて同じようにセルH1はそのままで
セルB1に
000024
とするには

【これでは今回の質問と違ってしまいます。】

↓
セルＨ１に
234
セルB1に
0000234

でした。m(__)m

はい。正しく動きました。
ありがとうございます。

回答No.9

mu2011

NO5です。
>Ｅ２からＥ１０まである状態で行いました。
>Ｆ２からＦ１０まで思ったとうりに０がつきました。
>ですがＦ１に0000000がセットされました？。
>（Ｅ１にはデータがないのですが？？？）
　⇒FOR文の開始行を「2」にすることを失念していました。
　　For i = 2 To Cells(Rows.Count, "E").End(xlUp).Row

お礼

わざわざすいません。
今回の記述で正しく動きました。
ありがとうございました。

さしつかえなかったら教えてください。

データがあるまで繰り返すループではなく

例えばセルH1に
234
と入っていて同じようにセルH1はそのままで
セルB1に
0000024
とするには教えていただいた記述を参考にすると

Sub 単一7文字化()

Range("B1").NumberFormatLocal = "@"
Value = String(7 - Len(Range("H1")), "0") & Range("H1")
Range("B1") = Value

End Sub

でいいのでしょうか？よろしくお願いします。

回答No.7

layy

「0000000」と「2」
を連結し「00000002」の８桁
これを右から７桁とると
「0000002」

必要な最大桁を左側へ付与し右から抜き取る
文字結合とRIGHT関数

もとが６桁なら
７桁＋６桁から右７桁でできます。
ＣＡＳＥ文は使わなくても可能です。

ロジックが悪いかはデバッグすること。やり方わからない場合はこれのが最優先かと。

お礼

ありがとうございました。

回答No.6

imogasi

良くある質問例で、質問の表現が、内容に比べて長すぎて要領を得ません。
この質問内容なら、一言二言の質問で回答者には判ります。また対処法はLen()関数の問題ではない（表題が不適切）。
エクセルVBAの経験が十分で無くて、知っている限りで、自己流の冗長なものになってますので全般的な勉強が必要と思います。
Format関数の問題です。
コードの一例はF2からデータ最下行まで数値が入っているとして６桁の文字列にする例。
Sub test01()
Dim cl As Range
Dim rng As Range
Set rng = Range("F2", Range("F2").End(xlDown))
For Each cl In rng
MsgBox Format(cl, "000000")
cl.NumberFormat = "@"
cl.Value = Format(cl, "000000")
Next
End Sub
Len関数を使っても出来ますが
一例　G列の例
Sub test02()
d = Range("G65536").End(xlUp).Row
For i = 2 To d
Cells(i, "G").NumberFormat = "@"
Cells(i, "G").Value = String(6 - Len(Cells(i, "G")), "000000") & Cells(i, "G")
Next
End Sub
一旦不都合に設定されたものを設定しなおすのでない、やり方は質問を改めて質問してください。

お礼

ありがとうございました。

回答No.5

mu2011

一例です。
実現の仕方は色々とあると思いますが、文字数から差分を埋めるなら、７－文字数とすればＣＡＳＥ文は不要と思います。

Sub sample()
For i = 1 To Cells(Rows.Count, "E").End(xlUp).Row
With Cells(i, "F")
.NumberFormat = "@"
.Value = String(7 - Len(Cells(i, "E")), "0") & Cells(i, "E")
End With
Next
End Sub

お礼

すいません。
多分私が悪いのですが
教えていただいた記述をコピペしました。

データが
Ｅ２からＥ１０まである状態で行いました。

Ｆ２からＦ１０まで思ったとうりに０がつきました。

ですがＦ１に0000000がセットされました？。
（Ｅ１にはデータがないのですが？？？）

どうもありがとうございました。

回答No.4

hananoppo

こんな感じでどうでしょう。

Sub 七文字化()
Dim 行 As Long

For 行 = 1 To Range("E1").End(xlDown).Row
Cells(行, 6).NumberFormatLocal = "@"
Cells(行, 6).Value = Format(Cells(行, 5).Value, "0000000")
Next 行
End Sub

お礼

すいません。
多分私が悪いのですが

教えていただいた記述をコピペして行いました。

データがＥ１からＥ１０の状態でＦ列に複写の状態で
Ｅ１→Ｆ１に０が付いて出来ましたが
Ｆ２からＦ１０までは０がつきませんでした。
（セルが空白のままです。）

どうもありがとうございました。

補足

【訂正】

すいません。
多分私が悪いのですが

教えていただいた記述をコピペして行いました。

データが
　Ｅ１（間違い→Ｅ２）
からＥ１０の状態でＦ列に複写の状態で
　Ｅ１（→間違いＥ２）
　→Ｆ１（→間違いＦ２）
に０が付いて出来ましたが
Ｆ２からＦ１０までは０がつきませんでした。
（セルが空白のままです。）

どうもありがとうございました。

回答No.3

conanthe

次のコードを試してください。質問文内の例ではうまくいきました。

問題文内のコードを元に作りましたが、これだとB列のデータをC列に変換・書き込みします。E列・F列にする場合は、「2，3」を「5，6」に変えなければいけません。

Sub 七文字化()
行 = 2
Do Until Cells(行, 2) = ""
Cells(行, 3) = "'" & Format(Cells(行, 2), "0######")
行 = 行 + 1
Loop
End Sub

お礼

はい。
ダミーデータ
Ｂ列にデータ、Ｃ列に複写で

教えていただいた回答をコピペして
完璧に動作しました。

私の長い記述よりこちらの方がいいです。

ありがとうございました。

補足

すいません。
セル表示は
2→0000002になりました。

ですが実際のセルデータは
'0000002
なので
他のシステムへインポートしたら取り込めませんでした。

回答No.2

qualheart

参考までに完成系のコードです。

Sub 七文字化()
行 = 2
Do
If Cells(行, 5).Value = "" Then Exit Do
n = Cells(行, 5)
Select Case True
'6文字
Case Len(n) = 6
'先頭に0を追加
myStr = "0" & n
'5文字
Case Len(n) = 5
'先頭に0を追加
myStr = "00" & n
'4文字
Case Len(n) = 4
'先頭に0を追加
myStr = "000" & n
'3文字
Case Len(n) = 3
'先頭に0を追加
myStr = "0000" & n
'2文字
Case Len(n) = 2
'先頭に0を追加
myStr = "00000" & n
'1文字
Case Len(n) = 1
'先頭に0を追加
myStr = "000000" & n
'7文字ある時
Case Else
myStr = n
End Select
Cells(行, 6).NumberFormatLocal = "@"
Cells(行, 6).Value = myStr
行 = 行 + 1
Loop
End Sub

お礼

ありがとうございます。
回答ＮＯ．１で
ダミーデータ（Ｂ，Ｃ列）でうまくできました。

今回の回答をそっくりコピぺして
本番環境で行い、正しく動作しました。

ありがとうございました。

質問の仕方が不適切でご迷惑かけました。