[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『入力文字変換』(ポン太2号)
いつも参考にさせていただいております。
K列に文字が入力されたら既存マクロ実行させたいと思っています。 実行したいのは、日本語入力した文字を英語に変換することです。 例)「日本」と入力→「JAPAN」に変換
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 11 Then Cells(Target.Row, 11) = _ Application.VLookup(Cells(Target.Row, 11).Value, Range(Cells(4, "X"), Cells(9, "Y")), 2, False)
'隣の列に移動 Cells(Target.Row, 12).Select End If End Sub
(X4:Y9)の範囲でリスト作成しています。 ※別シートにしたかったが失敗 ____X___ ___Y____ [4]国名 英語表記 [5]日本 JAPAN [6]中国 CHINA [7]韓国 KOREA [8]アメリカ USA [9]米国 USA
上記実行(セル入力)すると、 一瞬英語変換されたのが見えますが、すぐに「#N/A」になります。 やはり同一セルで変換するのは無理なのでしょうか? ご教示宜しくお願いします。
< 使用 Excel:Excel2013、使用 OS:Windows10 >
書き換えた際にまたWorksheet_Changeが実行されて、書き換えられた値でまた実行しているためにそのような状況になっている。 >If Target.Column = 11 Then の下に Application.EnableEvents = False >End If の前に Application.EnableEvents = True を入れてみてくれ。 (ねむねむ) 2018/12/26(水) 15:42
>(X4:Y9)の範囲でリスト作成しています。 ※別シートにしたかったが失敗 With Worksheets("別シート名") Cells(Target.Row, 11) = _ Application.VLookup(Cells(Target.Row, 11).Value, .Range(.Cells(4, "X"), .Cells(9, "Y")), 2, False) End With でできないか?
(ねむねむ) 2018/12/26(水) 15:46
ねむねむ様
早速のご教示ありがとうございます。 素晴らしいです! 思い通りのことが出来ました!
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then
Application.EnableEvents = False
With Worksheets("国名") Cells(Target.Row, 11) = _ Application.VLookup(Cells(Target.Row, 11).Value, .Range(.Cells(1, "A"), .Cells(6, "B")), 2, False) End With
'隣の列に移動 Cells(Target.Row, 12).Select
Application.EnableEvents = True End If End Sub
が、また1つ問題が発生してしまいました。
誤って入力してDeleteを押すと、「#N/A」が表示されます。 入力自体が不要な時は、元の空欄(空白)状態に戻したいのですが、 試しに、 >Application.EnableEvents = True の前に If Cells(Target.Row, 11).Value = "#N/A" Then Cells(Target.Row, 11).Clear End If と入れると、 「型が一致しません」とエラーになります。 再度ご教示宜しくお願いします。 (ポン太2号) 2018/12/26(水) 17:33
if Iserror(Cells(Target.Row, 11)) = True then
iF Application.IsnA(Cells(Target.Row, 11)) = True then
(BJ) 2018/12/26(水) 19:31
BJ様
ご教示ありがとうございます。 ご提示のコードで出来ました。 (ポン太2号) 2018/12/27(木) 16:18
新たにコードを追加したのですが、またも壁にぶつかってしまったので、 再度ご教示宜しくお願いします。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
'列Kに日本語入力→英字変換 If Target.Column <> 11 Then Exit Sub
'入力されたのが英字なら変換しない s = Target.Value If s Like "[A-Za-z]" Then Exit Sub
Application.EnableEvents = False
With Worksheets("国名") Cells(Target.Row, 11) = _ Application.VLookup(Cells(Target.Row, 11).Value, .Range(.Columns(1), .Columns(2)), 2, False) End With
'誤入力した場合の対処 If IsError(Cells(Target.Row, 11)) = True Then MsgBox "国名リストにありません" & vbCrLf & "リストに追加し、入力し直して下さい", vbExclamation Cells(Target.Row, 11).ClearContents End If
'隣の列に移動 Cells(Target.Row, 12).Select
Application.EnableEvents = True End Sub
追加したのは、 >'入力されたのが英字なら変換しない >s = Target.Value >If s Like "[A-Za-z]" Then Exit Sub の部分です。
例えば「JAPAN」と入力したら、そのまま「JAPAN」としたいのですが、 上記コードでは、'誤入力した場合の対処 以下が実行され、「#N/A」表示になった後クリアされます。
(ポン太2号) 2019/01/09(水) 16:23
>If Not s Like "*[!a-zA-Z]*" Then Exit Sub ではどうだろうか? ただ検索で見つけてきたもので私自身は正規表現関係は苦手なためそちらでも十分テストしてみてくれ。 それで問題があればすまない。
(ねむねむ) 2019/01/10(木) 09:41
Like演算子の参考になるサイトって少ないのね・・・。 なんか全部結果が違ってるし。疲れた。
で、エクセルの学校より抜擢正規表現(そのまんまだけど)。 [[20100309131848]]
お試しコードを書いてみた。 A1:A20 に適当に判別したい文字をいろんなパターンで入れて置く。 B列に結果を。 尚、空白判定は入れてません。
With CreateObject("VBScript.RegExp") For i = 1 To 20 .Global = True .IgnoreCase = True .Pattern = "[^a-zA-Z゚]+" If .test(Cells(i, 1).Value) Then HH = "NG" Else HH = "OK" End If Cells(i, 2).Value = HH Next End With (BJ) 2019/01/10(木) 11:45
If Not s Like "*[!a-zA-Z]*" Then Exit Sub
でOKかと。
Like演算子に関しては、下記の本家サイトでもれなく解説されていると思います。
Like 演算子 | Microsoft Docs
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/like-operator
(hatena) 2019/01/10(木) 15:02
この下から aArTH F ABC abc123 Gあいうえ Aerbhyd mmmdkア アイウエオ XYZ xyz aa ww a 123 vvvv 7890 aVBRTbb aaaaa aaa-mm
-abcd この上まで
上のような文字で結果が碌でもないけど? エクセル(2007)が壊れた?
For i = 1 To 20 s = Cells(i, 1).Value If Not s Like "*[!a-zA-Z]*" Then HH = "NG" Else HH = "OK" End If Cells(i, 2).Value = HH Next (BJ) 2019/01/10(木) 15:56
BJさんのところではどのような結果になったのだろう? 2010では英字のみのセル(空白セルを含む)でNG、英字以外も含まれるセルでOKとなったが。
(ねむねむ) 2019/01/10(木) 16:04
Bが(BJ) 2019/01/10(木) 15:56 Cが(BJ) 2019/01/10(木) 11:45 2013です。
|[A] |[B]|[C] [1] |aArTH |NG |OK [2] |F |NG |OK [3] |ABC |OK |NG [4] |abc123 |OK |NG [5] |Gあいうえ|OK |NG [6] |Aerbhyd |NG |OK [7] |mmmdkア |OK |NG [8] |アイウエオ |OK |NG [9] |XYZ |NG |OK [10]|xyz |NG |OK [11]|aa ww |OK |NG [12]|a 123 |OK |NG [13]| vvvv |OK |NG [14]| 890|OK |NG [15]|aVBRTbb |NG |OK [16]|aaaaa |OK |NG [17]|aaa-mm |OK |NG [18]| |NG |OK [19]|-abcd |OK |NG (稲葉) 2019/01/10(木) 16:19
ぶつかったけど。 ああ、誤解を生むような書き方だった。
一応これだけ他試した結果Like演算子をあきらめ、正規表現に変えた。
Dim s As String For i = 1 To 20 s = Cells(i, 1).Value 'If Not Cells(i, 1).Value Like "*[A-Za-z]*" Then 'If Not Cells(i, 1).Value Like "*[^a-zA-Z]*" Then 'If Not Cells(i, 1).Value Like "*[a%z%A%Z%]*" Then 'If (Not s Like "[a-z]") And (Not s Like "[A-Z]") Then 'If Not Cells(i, 1).Value Like "*[!a-zA-Z]*" Then If Not s Like "*[!a-zA-Z]*" Then HH = "NG" Else HH = "OK" End If ' If s Like "*[!a-zA-Z]*" Then ' HH = "OK" ' Else ' HH = "NG" ' End If Cells(i, 2).Value = HH Next
結果は、こんな感じ。
aArTH NG F NG ABC OK abc123 OK Gあいうえ OK Aerbhyd NG mmmdkア OK アイウエオ OK XYZ NG xyz NG aa ww OK a 123 OK vvvv OK ←左に半角スペースが入ってる。 7890 OK aVBRTbb NG aaaaa OK ←右に半角スペースが入ってる。 aaa-mm OK NG NG -abcd OK (BJ) 2019/01/10(木) 16:23
ごめんなさい。
>列Kに日本語入力→英字変換
英文字を日本語にすると勘違いしてました、 (BJ) 2019/01/10(木) 16:45
皆様、沢山のご回答ありがとうございます。 サイトを検索してくださったり、 コードの検証結果を提示してくださったり、 ただただ感謝です! しっかり勉強させていただきます。
まだ、一番最初のねむねむ様のしか試せていませんが、 いくつか入力してみて、今のところ思う通りになっています。 「含む(含まない)」としないといけなかったんですね。 特定多数の人が使用するものなので、もう少し検証してみます。
BJ様のはこれから試そうと思いますが、結果のご報告は週明けになりそうです。 早々にご教示いただいたのに申し訳ありません。
(ポン太2号) 2019/01/10(木) 17:11
BJ様
大変遅くなりましたが、 2019/01/10(木) 11:45記載の、正規表現でのコード試してみました。 (BJ様の意図と違っていたら申し訳ありません。)
思う通りに動きました! ありがとうございます。
Private Sub Worksheet_Change(ByVal Target As Range) '---K列に文字入力された時のみマクロ実行
'K列以外の時は何もしない If Target.Column <> 11 Then Exit Sub
Application.EnableEvents = False
'入力文字が英字の時も何もしない With CreateObject("VBScript.RegExp") .Global = True '文字列全体を検索する .IgnoreCase = True '大文字と小文字を区別しない .Pattern = "[^a-zA-Z゚]+" '大文字・小文字のアルファベット以外 If .test(Target.Value) Then 'Testメソッド:パターンと一致した場合はTrueを返す
'日本語入力→英字変換 With Worksheets("国名") Cells(Target.Row, 11) = _ Application.VLookup(Cells(Target.Row, 11).Value, .Range(.Columns(1), .Columns(2)), 2, False) End With End If End With
'誤入力した場合の対処 …… Application.EnableEvents = True
End Sub
1つ質問なのですが、 >.Pattern = "[^a-zA-Z゚]+" の部分で、Zの後の「゚」は濁点半濁点の意味で合っていますでしょうか?
(ポン太2号) 2019/01/16(水) 17:01
>1つ質問なのですが、 > >.Pattern = "[^a-zA-Z゚]+" >の部分で、Zの後の「゚」は濁点半濁点の意味で合っていますでしょうか?
そうですね、すみません。 消し忘れたみたいです。 パクリもんで、かつ老眼と近視が進んでよく見えなくて・・・・。
私は勘違いして、半角英文字以外はダメといった意図で使わせてみらいました。 だから、参考にはならないと思います。
(BJ) 2019/01/17(木) 00:26
私の場合正規表現は苦手なので、大文字に変換したものと小文字に変換したものを比較して一致する場合、アルファベットは含まれていないと判定する作戦をかんがえてみました。
その他、コピペや一括入力などで複数セルを同時変更することがあり得るかなとおもったので、チョイ修正してみました、
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRNG As Range, buf As Variant
'K列の変更でなければ即終了 If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
'イベント停止 Application.EnableEvents = False
For Each MyRNG In Intersect(Target, Range("K:K"))
Select Case True Case MyRNG.Value = "" '空白に変更された '→なのでなにもしない
Case UCase(MyRNG.Value) = LCase(MyRNG.Value) '大文字に変換と小文字に変換を比較しても同じ=アルファベットは含まれない '→なので何もしない
Case Else '何もしない条件を満たさない 'なので処理する buf = Application.VLookup(MyRNG.Value, Worksheets("国名").Range("X4:Y9"), 2, False)
If IsError(buf) Then MsgBox MyRNG.Address(0, 0) & "は入力エラーです" & vbCrLf & _ "国名リストにありません" & vbCrLf & "リストに追加し、入力し直して下さい", vbExclamation Else MyRNG.Value = buf End If End Select
Next MyRNG
'イベント再開 Application.EnableEvents = True
'↓この処理いらないような気はしますが一応。。。 Target.Offset(, 1).Select
End Sub
ざっくりとしかテストしてないのでミスっていたらごめんなさい。
(もこな2) 2019/01/17(木) 04:38
ご回答ありがとうございます。
BJ様) 私も老眼と近視と乱視で、質問の「゚」もかなり拡大して見ました。 決して揚げ足を取ろうとした訳ではなく、 正規表現を初めて使うので、コードの意味を調べていての質問でした。 記号?は頭になかったので、そこまで対処されているとはさすが!と思った次第です。 が、実際は消し忘れだったのですね。 失礼致しました。
もこな2様) 参戦ありがとうございます。
>buf = Application.VLookup(MyRNG.Value, Worksheets("国名").Range("X4:Y9"), 2, False) ↓ buf = Application.VLookup(MyRNG.Value, Worksheets("国名").Range("A:B"), 2, False) ^^^^^^ に変更した以外はそのままコピペして試したのですが、2点問題が…。 自分では修正箇所が分からなかったので、再度ご教示いただければ幸いです 以下に問題点を挙げます。
1.日本語入力しても英字変換されない 例)日本→日本のまま
シート:国名 ____A___ ___B____ [1]国名 英語表記 [2]日本 JAPAN [3]中国 CHINA [4]韓国 KOREA [5]アメリカ USA [6]米国 USA
2.英字入力できるがIsError時のメッセージが出る 例)JAPAN→JAPAN 「K4は入力エラーです…」 ※複数セル入力(コピペ)した時もそのセル全部一つずつメッセージが出ます。
宜しくお願いします。
(ポン太2号) 2019/01/17(木) 17:01
もこな2さんの発想は大変すばらしいと思います。が、テストされてないとのことなので、 単純に処理をする条件を間違えただけかと思います。 >'大文字に変換と小文字に変換を比較しても同じ=アルファベットは含まれない >'→なので何もしない ~~~~~~~~~~~→処理をする
なので、 Case UCase(MyRNG.Value) = LCase(MyRNG.Value) Case Else の処理をそっくり入れ替えれば希望通りかと思います。
(稲葉) 2019/01/17(木) 19:02
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRNG As Range, buf As Variant, フラグ As Boolean
'K列の変更でなければ即終了 If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
'イベント停止 Application.EnableEvents = False
For Each MyRNG In Intersect(Target, Range("K:K"))
Select Case True Case MyRNG.Value = "" '空白に変更された '→なのでなにもしない
Case Not UCase(MyRNG.Value) = LCase(MyRNG.Value) '大文字に変換と小文字に変換を比較すると同じ【ではない】=アルファベットが含まれる '→なので何もしない
Case Else '何もしない条件を満たさない '→なので処理する With MyRNG buf = Application.VLookup(.Value, Worksheets("国名").Range("A:B"), 2, False)
If IsError(buf) Then フラグ = True .Interior.Color = 65535 Else .Interior.ColorIndex = 0 .Value = buf End If End With End Select
Next MyRNG
'イベント再開 Application.EnableEvents = True
'↓この処理いらないような気はしますが一応。。。 Target.Offset(, 1).Select
If フラグ Then MsgBox "国名リストにないものがあります" & vbCrLf & _ "リストに追加し、黄色セルに入力し直して下さい", vbExclamation End If
End Sub
1セルごとにメッセージボックスが出るのは、元のコードに合わせてそういうように作りました。
ただ、私もいちいち該当セルごとにメッセージが出てきたら煩わしいとおもいますので、別途フラグを設けていっぺんにお知らせするようにしたほうがユーザーフレンドリーのようにおもいます。
(もこな2) 2019/01/17(木) 21:04
ポイントは
Case UCase(MyRNG.Value) = LCase(MyRNG.Value) ↓ Case Not UCase(MyRNG.Value) = LCase(MyRNG.Value)
のように、Notを入れて True を False にしているところです。
これで
大文字に変換と小文字に変換を比較しても同じ=アルファベットは含まれない ↓ 大文字に変換と小文字に変換を比較すると一致しない=アルファベットが含まれている
という判定に変えています。
(もこな2) 2019/01/17(木) 21:14
ご教示ありがとうございます。
稲葉様) ご教示の通り、処理を入れ替えたら上手くいきました。 英字入力は出来ているのだからと、逆になっていることも認識していませんでした。 ありがとうございます。
もこな2様) 別案のご提示ありがとうございます。 素晴らしいです! 複数セル入力にも対応して、思い通りの動きをしてくれました。 ユーザーへの気遣いにも敬服致しました。
勉強の為にもう1つ質問させてください。
>If IsError(buf) Then > フラグ = True > .Interior.Color = 65535 >Else > .Interior.ColorIndex = 0
このコードで、 日本語入力のエラー時にセルの色を変えて、 このエラー箇所を日本語で再入力して、英字変換されれば元の色に戻しているのだと思いますが、 英字入力した場合(英字→英字)も色を元に戻すには、
> Case Not UCase(MyRNG.Value) = LCase(MyRNG.Value) の後に、 MyRNG.Interior.ColorIndex = 0 と入れる考えで合っていますでしょうか? (実際入れてみて、思う動きはしてくれています。)
何度も申し訳ありませんが、宜しくお願いします。
(ポン太2号) 2019/01/18(金) 13:34
ただ、現状だと本来【入力すべきで無いところ】に【誤って日本語】で【リスト】にないものを入れちゃった場合も、黄色くなります。
それを、クリアしても塗りつぶしは残ったままですから、思い切ってK列の変更があったセルは一旦塗りつぶしを解除してしまうようにすれば、どちらにも対応できますね。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRNG As Range, buf As Variant, フラグ As Boolean
'K列の変更でなければ即終了 If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
'イベント停止 Application.EnableEvents = False
'変更があった、かつ、K列にあるセルの塗りつぶしを解除 Intersect(Target, Range("K:K")).Interior.ColorIndex = 0
For Each MyRNG In Intersect(Target, Range("K:K"))
If MyRNG.Value = "" Then 'なにもしない ElseIf Not UCase(MyRNG.Value) = LCase(MyRNG.Value) Then 'なにもしない Else '以下の処理を実行 With MyRNG buf = Application.VLookup(.Value, Worksheets("国名").Range("A:B"), 2, False)
If IsError(buf) Then フラグ = True .Interior.Color = 65535 Else .Value = buf End If
End With End If Next MyRNG
'イベント再開 Application.EnableEvents = True
If フラグ Then MsgBox "国名リストにないものがあります" & vbCrLf & _ "リストに追加し、黄色セルに入力し直して下さい", vbExclamation End If
End Sub
※インデントが深くなっていたので、Select Case から IF〜ElseIf〜Else に変更しました。 好みの問題だとおもいますので、戻しても(たぶん)大丈夫
(もこな2) 2019/01/18(金) 19:29
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRNG As Range, buf As Variant, フラグ As Boolean
'K列の変更でなければ即終了 If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
'イベント停止 Application.EnableEvents = False
'変更があった、かつ、K列にあるセルの塗りつぶしを解除 Intersect(Target, Range("K:K")).Interior.ColorIndex = 0
For Each MyRNG In Intersect(Target, Range("K:K")) With MyRNG '「MyRNG」の値が空白ではない、かつ、アルファベットを含まない場合に処理 If .Value <> "" And UCase(.Value) = LCase(.Value) Then buf = Application.VLookup(.Value, Worksheets("国名").Range("A:B"), 2, False)
If IsError(buf) Then フラグ = True .Interior.Color = 65535 Else .Value = buf End If
End If End With
Next MyRNG 'イベント再開 Application.EnableEvents = True If フラグ Then MsgBox "国名リストにないものがあります" & vbCrLf & _ "リストに追加し、黄色セルに入力し直して下さい", vbExclamation End If End Sub
(もこな2) 2019/01/18(金) 21:48
K列に入力された文字列がA:Bのどちらかに存在するか判定する。 つまりリストに無ければエラー、ということなら単純に、
Private Sub Worksheet_Change(ByVal Target As Range) Dim s As String, x '列Kに日本語入力→英字変換 If Target.Column <> 11 Then Exit Sub If Target.Value = "" Then Exit Sub s = Target.Value Application.EnableEvents = False With Worksheets("国名") x = Application.VLookup(s, .Columns("a:b"), 2, False) ' 一旦xにLOOKUPの結果を保持 If Not IsError(x) Then 'xがエラーでなければそのまま変換 Target.Value = x Else ' xがエラーならB列にマッチするか確認 x = Application.Match(s, .Columns(2), 0) End If End With If IsError(x) Then 'B列にも対象国名が無ければ MsgBox "国名リストにありません" & vbCrLf & "リストに追加し、入力し直して下さい", vbExclamation Target.ClearContents Target(, 2).Select End If Application.EnableEvents = True End Sub (seiya) 2019/01/19(土) 10:55
早々にご回答頂いていたのに、返信が遅くなり申し訳ございません。
もこな2様)
どちらのコード(19:29,21:48)も完璧でした! ありがとうございます。
条件分岐はよく使いますが、 組立て方が下手で、インデントが深く深くなってしまいます。。 考え方一つで、こんなにもスッキリしたコードになるのですね。 勉強になります。 再三の質問にも最後まで丁寧にお応えいただき、本当にありがとうございました。
seiya様)
新たなコードのご提示ありがとうございます。 思うように動きました!
実を言うと、使用者にヒアリングすることなく見切り発車で作成したものでして… 実際どのように国名を入力するか不明です。
今のところ、 最終的に英字で国名が入っていればいい、くらいの考えでいますが、 国名が限られている、となった場合はseiya様の案を使わせていただきたいと思います。 ありがとうございました。
(ポン太2号) 2019/01/21(月) 16:06
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.