続・エクセルのユーザー定義で

前回、「1234511」で「12345A1-1」となるユーザー定義「00000"A"0"-"0」を使っている列で、
「12345B1」で「12345AB-1」にする方法を教えていただきましたが、さらに問題が発生しました。
「12345A67-1」という風にAの次の数字が二桁の場合が出てきて、「12345671」では
「123456A7-1」になってしまいます。前回教えていただいたコードをいじれば修正可能なのでしょうか？
新たに「12345671」の入力で「12345A67-1」となるようにしたいのです。

前回のコード
private sub worksheet_change(byval Target as excel.range)
　dim h as range
　on error resume next
　for each h in application.intersect(target, range("A:A"))　’実際に合わせて修正
　　if h <> "" then
　　h.numberformat = "0000A0-0"
　　if application.istext(h) then
　　application.enableevents = false
　　h = application.replace(h, len(h), 0, "-")
　　h = application.replace(h, len(h)-2, 0, "A")
　　application.enableevents = true
　　end if
　　end if
　next
end sub

No.2 ベストアンサー 回答者： tom04 回答日時： 2012/10/11 21:47

こんばんは！

表示形式の操作ではなく、データそのものを変えてしまうコトになりますが・・・

Private Sub worksheet_change(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Or Selection.Count <> 1 Then Exit Sub
Dim str As String
str = Target
Application.EnableEvents = False
If Target <> "" Then
If Len(str) = 7 Then
Target = Left(str, 5) & "A" & Mid(str, 6, 1) & "-" & Right(str, 1)
Else
Target = Left(str, 5) & "A" & Mid(str, 6, 2) & "-" & Right(str, 1)
End If
End If
Application.EnableEvents = True
End Sub

※　A列に入るデータの文字数は7、もしくは8という前提です。
※　元データを変更させたくない場合は無視してください。m(_ _)m

この回答へのお礼

ありがとうございます。出来ました。
元データが変わるというのは全然構いません。ユーザー定義も必要なくなるということですね。
今夜はよく眠れそうです。

お礼日時：2012/10/11 22:42

No.1 回答者： KURUMITO 回答日時： 2012/10/11 15:33

次のようなコードにすればよいでしょう。

Private Sub worksheet_change(ByVal Target As Excel.Range)
Dim h As Range
Dim myStr As String
On Error Resume Next
For Each h In Application.Intersect(Target, Range("A:A")) '実際に合わせて修正
Application.EnableEvents = False
If IsNumeric(h) Then
myStr = h
If Len(myStr) = 8 Then
h = Left(String:=myStr, Length:=5) & "A" & Mid(String:=myStr, Start:=6, Length:=2) & "-" & Right(String:=myStr, Length:=1)
ElseIf Len(myStr) = 7 Then
h = Left(String:=myStr, Length:=5) & "A" & Mid(String:=myStr, Start:=6, Length:=1) & "-" & Right(String:=myStr, Length:=1)
End If
End If
Application.EnableEvents = True
Next
End Sub

この回答への補足

ご回答ありがとうございます。
出来た！と思ったのですが、前回の「12345B1」で「12345AB-1」にすることが出来なくなりました。
前回のコードのどこかを追加すればいいのかと思うのですが、素人ゆえ分かりません。
宜しくお願い致します。

補足日時：2012/10/11 16:08