セルに入力された値を別セルに条件付で転記したい

質問

エクセルにおいて、

セルのＡ１に
１
と入力したら
セルＡ２に
半角で
Q00000001と表示

セルのＡ１に
10
と入力したら
セルＡ２に
半角で
Q00000010と表示

セルのＡ１に
100
と入力したら
セルＡ２に
半角で
Q00000100と表示させたいです。

先頭は必ずQで計9桁でセルＡ１の入力値がみたない場合0で埋める。

これを行う一番いい方法は何がありますでしょうか？
できたらセルには式は入れたくないです。

そのエクセルには

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ●●
End Sub　

が入っています。
シート保護をかけて
セルＡ１とＢ１しか入力できないようになっています。
今はセルＢ１に入力するとマクロ●●が走って
セルＢ２にマクロのSelect Caseで変換された値が転記されるようになっています。

もうひとつマクロを作って

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ●●
Call ●●２
End Sub

がいいでしょうか？
その場合、そのマクロの記述そのものを教えてください。　
お願いします。

回答No.4

merlionXX · ベストアンサー

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Intersect(Target, Range("I9:I10")) Is Nothing Then Exit Sub
　　On Error GoTo line
　　Application.EnableEvents = False
　　Select Case Target.Address(0, 0)
　　Case "I9"
　　　　Target.Offset(, 1).Resize(, 5).Value = Sheets("Sheet2").Range("B1").Offset(Target.Value).Resize(, 5).Value
　　　　Call ハイフン挿入0M
　　Case "I10"
　　　　Application.EnableEvents = False
　　　　Range("D15").Value = "Q" & Format(Target.Value, "00000000")
　　End Select
line:
　　Application.EnableEvents = True
End Sub

なら？

お礼

ありがとうございます。
すいません。

I9に
10と入力したら
Sheet2の
B11→Sheet1のJ9
C11→Sheet1のK9
D11→Sheet1のL9
E11→Sheet1のM9
F11→Sheet1のN9
に転記がされ、
Call ハイフン挿入0Mが走る。

I10に
50と入力したら
D15に　Q00000050と転記される。

予定なのですが

I9に入力してもJ9～N9は変化せず
Callマクロも走らず。
またさっきは
動いていた
I10に50と入力しても
D15　が　変化しません。

分かりません。すいません。

補足

申し訳ありません。
動きました。
ただSheet2の
B11→Sheet1のJ9
ここが数字14桁の場合
7.04871E+13と表示されます。
セルの値は正常です。

これはSheet1のJ9
のセルの書式設定を文字列にしておくしかないでしょうか？

回答No.7

merlionXX

>「数値が文字列として保存されています」
> となっています。
>（先頭に　'　は入っていません）

> そのセルの値をマクロとかで別セルに転記すると
> 転記先では1.23457E+13
> と表示されてしまいます。

> この件はずっともやもやしています

なるほど、そういう意味ですか。
これはわたしも経験があります。

でもなぜそうなるのかはわたしも知りません。
体験からするとエクセル以外のデータベースからインポートされた数字のデータはセルの書式が標準でも文字列となるようです。
ところがこれを転記すると、今度はエクセルのおせっかい機能が働いて、数字は数値に変換され、先頭の０は消えるし、桁数が多いと指数表示になってしまいますね、
わたしもなぜセルの書式が標準なのに中の数字が文字列でいられるのか疑問に思ってました。
この件、別途質問されたらいかがでしょう？
詳しい方から回答があるかも。

お礼

merlionXXさんも経験があるんですね。

別スレッドを立ち上げてみます。
いろいろとありがとうございました。

回答No.6

merlionXX

> ここが数字14桁の場合
> 7.04871E+13と表示されます。

それはマクロに限った話ではないでしょう？
セルの書式が、[標準] 形式では、12 桁以上の数値に対して指数表示になります。
指数表現が使用されないようにするには、[数値] などの別の表示形式を適用する必要があります。

http://121ware.com/qasearch/1007/app/servlet/qadoc?QID=002938

お礼

基幹システムの吐き出しデータは
Microsoft Excel 2.1ワークシートで
全てのセルの書式設定は「標準」です。

で例えば

12345678901234
は
12345678901234
と表示されていて

「数値が文字列として保存されています」
となっています。
（先頭に　'　は入っていません）

そのセルの値をマクロとかで別セルに転記すると
転記先では1.23457E+13
と表示されてしまいます。

この件はずっともやもやしています。

セルの書式設定で同じ標準なのに
片方は
12345678901234と表示され
片方は
1.23457E+13と表示されます。

まっさらなエクセルに自分で入力すると

・セルの書式設定が標準だと
12345678901234
↓
1.23457E+13になります。

・セルの書式設定が数値だと
12345678901234
↓
12345678901234

・セルの書式設定が文字列だと
12345678901234
↓
12345678901234
（ただし　数値が文字列と保存されています　と注釈有）

なのに、基幹システムの吐き出しデータは
セルの書式設定が標準なのに
12345678901234
↓
12345678901234
（ただし　数値が文字列と保存されています　と注釈有）
となっているので？？？。

手入力では書式設定が標準の状態で
12345678901234
と表示させるには先頭に'をつけるしか知りませんが
'が無いのに表示されています。

でこれをマクロで転記すると
1.23457E+13
となります。

とりあえず、転記先のセルの書式設定を
数値、文字列、とかに変更して転記するようにしていますが
いまいちすっきりしていません。

別スレでの行削除でもそうなのですが、

行削除するデータは基幹システムの吐き出しデータそのままなので
セルの書式設定は全て標準で数値のみの場所は同じく
12345678901234（ただし　数値が文字列と保存されています　と注釈有）
と表示されています。
配列に取り込んで書き出しても
12345678901234（ただし　数値が文字列と保存されています　と注釈有）
と保っています。

なのにセルから別セルへ転記すると
1.23457E+13
となってしまいます。

マクロ以上にエクセル自体の理解が無く申し訳ないです。
いろいろありがとうございました。

回答No.5

merlionXX

> またさっきは動いていたI10に50と入力してもD15　が　変化しません。

マクロエラーが発生しませんでしたか？
とりあえず、このマクロをコピペして実行してみてください。

Sub イベント復旧()
　　Application.EnableEvents = True
End Sub

お礼

すいません。
マクロエラーは出ませんでした。
また正常に動作しています。
シート保護が悪さをしました。
申し訳ありません。
どうもありがとうございました。

回答No.3

merlionXX

merlionXXです。
ANo2のコードは使っちゃいけません。とんでもないミスがありました。
以下訂正です。

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
　　On Error GoTo line
　　Application.EnableEvents = False
　　If Target.Address(0, 0) = "B1" Then
' Call ●●
　　Else
　　　　Range("A2").Value = "Q" & Format(Target.Value, "00000000")
　　End If
line:
　　Application.EnableEvents = True
End Sub

お礼

ありがとうございます。
Private Sub Worksheet_Change(ByVal Target As Range)
に変更だとするとちょっと構想が変わってしまいました。
最初から纏めて質問すべきでした。m(__)m

実際には
I10に数値を入力すると
D15がQ頭の9桁に変換されて転記。
でJ9の値が変わったら
Call　ハイフン挿入0M　を走らせるつもりでした。
Q頭の9桁の方は変換はできましたが、
今Call　ハイフン挿入0M　が走りません。

もっと言いますと
I9に数字を手入力したら
マスターデータとなるシート２から該当部分を
J9,K9,L9,M9,N9に転記をし、
そうしたら
Call　ハイフン挿入0M　
を走らせたいのです。
（こちらは今質問中(^_^.)

------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I10:J9")) Is Nothing Then Exit Sub
On Error GoTo line
Application.EnableEvents = False
If Target.Address(0, 0) = "J9" Then
Call ハイフン挿入0M
Else
Range("D15").Value = "Q" & Format(Target.Value, "00000000")
End If
line:
Application.EnableEvents = True
End Sub

--------------------
Sub ハイフン挿入0M()
n = Range("J9")
If Len(n) = 14 Then
Select Case True
Case Left(n, 2) = "9X" And InStr(n, "-") = 0
myStr = Left(n, 3) & "-" & Mid(n, 4)
Case Mid(n, 9, 1) = "-"
myStr = Left(n, 3) & "-" & Mid(n, 4, 11)
Case Left(n, 1) = "9" And InStr(n, "-") = 0
myStr = Left(n, 5) & "-" & Mid(n, 6, 5) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
Case InStr(n, "-") = 0
myStr = Left(n, 3) & "-" & Mid(n, 4, 5) & "-" & Mid(n, 9, 2) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
Case Else
myStr = n
End Select
Else
myStr = n
End If
Range("J10").Value = myStr
End Sub

回答No.2

merlionXX

merlionXXです。

入力にあわせて自動実行ならWorksheet_Changeを使うべきです。
Worksheet_SelectionChangeはセルを選択しただけで作動してしまいます。
入力セルによる分岐も出来ます。
以下は、A１に入力すればご質問のマクロを、
B1なら●●を呼び出します。
それ以外のセルの場合は何もおきません。

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
　　On Error GoTo line
　　Application.EnableEvents = False
　　If Target.Address(0, 0) = "B1" Then
' Call ●●
　　Else
　　　　Range("A2").Value = "Q" & Format(Target.Value, "00000000")
　　End If
line:
　　Application.EnableEvents = False
End Sub

お礼

いつもすいません。
すでに次の回答があり
これは使用しないようにとの事ですので
スルーします。
ありがとうございました。

回答No.1

KURUMITO

式を使わないでA１のデータをA２に表示させるためにはマクロを組むことになるでしょう。
単にA１セルに１や１０を入力してお示しの表示にするだけでしたらA１セルを右クリックして「セルの書式設定」から「表示形式」の「ユーザー定義」で　Q0000000#　と入力すればよいでしょう。

お礼

>A１セルを右クリックして「セルの書式設定」から「表示形式」の
>「ユーザー定義」で　Q0000000#

この場合、セルには例えばQ00000050と表示されているのですが
実際のセルの値は５０でした。
このセルの値をべつで使う為
セルの値も表示もQ00000050でないと
まずいのです。
申し訳ありませんでした。

＞マクロを組むことになるでしょう。
それが出来なくて困っています。

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