エクセルＶＢＡ 入力時に行番号-4を転記したい

質問

台帳登録入力をしています。

その台帳ファイル（シート名　施設）のＥ列には、登録施設が漢字で入っていて、新規登録施設がでた場合には、最下行に手入力しています。

Ａ列は登録番号
Ｂ列はソート用の判別番号（他のＶＢＡでソートに使用）
Ｃ列は施設所在地
Ｄ列は施設名フリガナ
Ｅ列は施設名漢字
Ｆ列は備考　　　　となっています。

このシートの１行から４行は項目などを記載したため、最初の施設は５行目から記載しています。（登録番号１の施設は５行目です）

もし、新規施設を入力する際に、その漢字入力セルがE200だった場合、A200には　196　と記載され、B200とC200は何も記載せず、D200にはE200のフリガナを振り、F200には　なし　とテキスト文字を記載したいのです。

フリガナとテキストは何とかできましたが、　行番号-4　がわからないのです。

教えていただけるでしょうか。

今わかっている部分です。（シートモジュールに貼り付けていますが、標準モジュールの方が良いのでしょうか？　標準モジュールの方が良いのでしたら、手順も教えてください。お願いします。）

'５行目以降でＥ列に施設名を漢字で入力すると、Ｄ列に振り仮名を振り、Ｆ列に"なし"と入力する
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim crng As Range
Application.EnableEvents = False
Set rng = Application.Intersect(Target, Range("E5:E65536"))
If Not rng Is Nothing Then
For Each crng In rng
With crng
.Offset(0, -1).Value = _
Evaluate("asc(phonetic(" & .Address & "))")
.Offset(0, 1).Value = "なし"
End With
Next
End If

Application.EnableEvents = True
End Sub

回答No.4

Wendy02 · ベストアンサー

こんにちは。

本来は、VBAが分かる方なら、#1　さんの回答でよいのですが、ご自身のコードではありませんね。

ただ、元の作者さんには悪いけれど、少し、違和感がありますね。オブジェクトを作ったら、それは開放しなければなりませんので、それが面倒だから、最初に、除外項目でイベント型マクロを抜けます。

本来、この、For Each crng In Target　コードは、別のシートから貼り付けたときにのみ、役に立ちます。通常は、Taget.Count で、セルの数を数え、1個以上なら、除外という項目をさらに設けます。

>シートモジュールに貼り付けていますが、標準モジュールの方が良いのでしょうか？　標準モジュールの方が良いのでしたら、手順も教えてください。

標準モジュール（クラスモジュールではありません）につけるイベントは、まったくコードの内容が違います。これは、シートモジュールにつける特別なマクロです。これは、入力するシートのみに有効のマクロです。

'-----------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim crng As Range
　'除外項目は、EnableEvents = Falseの前で行います。
　If Target.Row < 5 Or Target.Column <> 5 Then Exit Sub
　Application.EnableEvents = False
　　For Each crng In Target
　　　With crng
　　　　.Offset(, -4).Value = .Row - 4
　　　　.Offset(, -1).Value = StrConv(.Phonetic.Text, vbNarrow)
　　　　.Offset(, 1).Value = "なし"
　　　End With
　　Next
　Application.EnableEvents = True
End Sub

VBAは、スクリプト言語ですので、実務的には、なるべく省略するべきところは省略します。だから、「0」が略されています。

シートのセルのRangeオブジェクトから、ふりがなを取り出すときには、.Phonetic.Text とします。これは、ワークシート関数と同義です。厳密に言えば、上記のコードには、エラー時に対して足りない部分があるような気がしますが、それはたいした問題ではないので割愛します。

お礼

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

元は、Ｅ列に漢字で入力したらＤ列にフリガナを振る方法というＱ＆Ａを見つけて、これを拝借して、勝手に５行目以降を範囲として、テキストを振るように追加してと改造してしまったものです。

ＶＢＡの入門書を毎日５ページずつ読んでいて、少しずつ覚えているところですが、素人の付け焼刃で、お恥ずかしい限りです。

回答コードだけでなく、考え方まで教えていただき、大変勉強になりました。

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

（以前、このサイトへセルの点滅について回答されていましたが、同じ事をしたいのですが、うまくいかないもので別質問でお伺いしたいと思います。（スミマセン、勝手なことを申しまして）

今回も、大変お世話になりました。

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

回答No.6

mshr1962

#3です。
>24　はどのような意味なのでしょうか？
#4,5のかたが
VbKatakana + VbNarrow
とかかれてますが、これを数値化したものです。
VbKatakana = 16　：ひらがなをカタカナに変換
VbNarrow = 8　：全角文字を半角文字に変換
他の変換の引数に関しては、Helpで検索してください。

お礼

そのように数値化できるものなのですか。

すごいですね。

もしかしたら、数値化が基本で、英単語のようになっているものが後からできたのかも知れませんが。

勉強になりました。
エクセルの手法が一つわかってとても嬉しいです。

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

回答No.5

onlyrom

こんにちは。
お、頑張ってらっしゃいますね。
始めのうちは扱っているセルが一目瞭然になるようなコードがいいかも知れませんねぇ。
そして新しい登録者を入力するのは一人ずつでしょうから、少なくともそれもチェック。
また、Application.Intersectはも少し慣れてから使うことをお勧めします。

'--------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

　If Target.Count > 1 Then Exit Sub
　If Target.Row < 5 Then Exit Sub
　If Target.Column <> 5 Then Exit Sub

　Application.EnableEvents = False

　With Target
　　　Cells(.Row, "A").Value = Row - 4
　　　Cells(.Row, "D").Value = StrConv(.Phonetic.Text, vbNarrow + vbKatakana)
　　　Cells(.Row, "F").Value = "なし"
　End With

　Application.EnableEvents = True
End Sub
'----------------------------------------------------------

ここまで。

お礼

たびたびお世話になります。
以前教えていただいたことで、ＶＢＡの面白さに取り付かれ、毎日（夜中に布団の中で）10日でできるＶＢＡというような本を5ページずつ読んでいます。

すっきりしたコードを教えていただいてありがとうございました。

もっともっと勉強していきたいと思います。

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

回答No.3

mshr1962

＞行番号-4　がわからないのです。
＞A200には　196　と記載され
ここのことですか？
.Offset(0, -1)がD200ということなので
A200は
.Offset(0, -4) = .Row - 4
ですね。
あと、ふりがなの方はこういう方法もあります。
.Offset(0, -1).Value = StrConv(Application.GetPhonetic(crng), 24)

＞標準モジュールの方が良いのでしょうか？
現状はセル移動の度に実行されます。
ボタンかショートカットで起動なら標準モジュールに移動
E列に変更があったときだけなら、E列の該当行のみ行うようにしたほうが良いですね。

お礼

ありがとうございました。読みづらい質問文で申し訳ありません。
（今、読み直したら、質問事項が他の文章に埋もれてハッキリしていませんでした）

手元の参考書には、フリガナについての解説がなく、ネットで調べています。

お礼の欄で申し訳ありませんが、
StrConv(Application.GetPhonetic(crng), 24)
の　24　はどのような意味なのでしょうか？

解決はしたのですが、勉強のため、教えていただきたく、よろしくお願いいたします。

回答No.2

masa_019

こんにちは。

Target.Offset(, -4).Value = Target.Row - 4

ってことですか？

お礼

はい。そのとおりです。
ありがとうございました。安心しました。

Target.Offset(, -4).Value の(0, -4)の0は省略してもいいのですね。勉強になりました。

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

回答No.1

hana-hana3

ヒントだけですが。

Target.Row
とすれば行番号を取得できます。

お礼

今あるコードの　　.Offset(0, 1).Value = "なし"　　の次行に

　　 .Offset(0, -4).Value = Target.Row - 4

と加えたら、思い通りに動きました。

私のレベルに合わせたヒントをいただき、それを元に解けたので少し達成感も味合うことができました。（ほとんど回答をいただいてしまいましたが）

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