Excel2007VBA「区」のところで改行したい

質問

●質問の主旨
エクセルワークシートのＢ列にある任意の住所をＤ列に転記します。
転記の際に「区」のところで改行したいと思いますが、
自作のコードではその通りになりません。
どのように書き換えたら良いでしょうか？
ご存知の方、ご教示願います。

●質問の補足
１．「住所１」には必ず「区」の文字が入ります
２．Mid関数の第2引数にInStr(Cells(i, 2), "区")を
使っていますが、この引数に何を使えば良いのかが分かりません。
３．添付の画像は自作のコードを使った結果、
作成された住所で、意図通りの改行はできていません。

●コード

Sub Macro1()

Dim i As Integer

For i = 2 To 6

Cells(i, 4) = Left(Cells(i, 2), InStr(Cells(i, 2), "区")) _
& Chr(10) & Mid(Cells(i, 2), InStr(Cells(i, 2), "区"))

Next i

End Sub

回答No.6

Cor_moriyan · ベストアンサー

#2の補足に対する回答です。

> Cells(i,4)=Replace(Cells(i, 2), "区", "区" & vbLf, 1, 1)

> こちらのご回答では第4引数まで
> 加えられているのはどうしてでしょうか？

Replace([元の文字列],[探す文字列],[置換する文字列],[何文字目から],[いくつ置換する])

です。3番目と4番目の引数に 1,1 とする事で、
「1文字目から検索し、1回だけ置換する」事になります。住所ではまず無いと思いますが・・・仮に「区」が含まれた町名があった場合、指定しないと2つ目の「区」も「区＋セル内改行」に置換されてしまうんです。
それを避けるために指定しています。
まぁ考慮しなくても大丈夫でしたら、

> Cells(i,4)=Replace(Cells(i, 2), "区", "区" & vbLf)

でもイケますよ(^^;
Left,Mid,InStrを駆使するよりも、Replace命令の方がスマートでお勧めです。
ただ・・・Excel97以前のバージョンではReplace命令が無いので気をつけて下さい。

あと、vbLf は Chr(10) の定数です。

お礼

Cor_moriyan様
再度のご回答ありがとうございます。
またお礼が遅くなり恐れ入ります。

Ｒeplace関数の使い方良く分かりました。
文字の切り取りに関してはLeft,Mid,InStr関数よりも
Replace関数を使うようにします。

回答No.5

imogasi

質問には、じっこうしてどんなエラーや、気に食わない結果が出たかを書いて質問すること。
ーー
Sub Macro1()
Dim i As Integer
d = Range("B65536").End(xlUp).Row
MsgBox d
For i = 2 To d
MsgBox InStr(Cells(i, "B"), "区")
Cells(i, 4) = Left(Cells(i, 2), InStr(Cells(i, 2), "区")) _
& Chr(10) & Mid(Cells(i, 2), InStr(Cells(i, 2), "区") + 1, Len(Cells(i, 2)) - InStr(Cells(i, 2), "区"))
Next i
End Sub
MID関数では、スタート位置と切り出す長さを指定する。
この関数の理解とコード上のくみこみが出来てない、エらーは自明のことではないか。
Msgbox ででも適宜入れて確認すれば情況はわかって、調べる手立てはわかるはず。
ーー
もうひとつ「区」の無いセルは手当てがしてないがそれで良いのか。

お礼

imogasi様
ご回答ありがとうございます。

＞もうひとつ「区」の無いセルは手当てがしてないがそれで良いのか。

今回はＢ列のセルには必ず「区」があるので、
アドバイスしてくださったコード対応ができます。
ありがとうございました。

回答No.4

Cor_moriyan

#2訂正です。
参考までに、Replace命令で行った一例です(^^;

Cells(i,4)=Replace(Cells(i, 2), "区", "区" & vbLf, 1, 1)

お礼

Cor_moriyan様
再度のご回答ありがとうございます。
NO.6をご覧くだされば幸いです。

補足

たびたびの質問恐れ入ります。

No.2のご回答でも当初の質問に対する
ご回答になっていると思います。

こちらのご回答では第4引数まで
加えられているのはどうしてでしょうか？

よろしくお願いします

回答No.3

Tom-3

「区」がない行があっても正常に動作させるためには

If InStr(Cells(i, 2), "区") > 0 Then
Cells(i, 4) = Left(Cells(i, 2), InStr(Cells(i, 2), "区")) _
& Chr(10) & Mid(Cells(i, 2), InStr(Cells(i, 2), "区") + 1)
Else
Cells(i, 4) = Cells(i, 2)
End If

とすれば大丈夫だと思います。

お礼

Tom-3様
ご回答ありがとうございます。

「区」がない場合は想定していませんでした。
アドバイスありがとうございます

回答No.2

Cor_moriyan

参考までに、Replace命令で行った一例です(^^;

Cells(i,4)=Replace(Cells(i, 2), "区", "区" & vbLf, 1)

お礼

Cor_moriyan様
ご回答ありがとうございます。

Replace関数で処理できることも
分かりました。大変助かります。

回答No.1

mu2011

一例です。
Cells(i, 4) = Left(Cells(i, 2), InStr(Cells(i, 2), "区")) _
& Chr(10) & Mid(Cells(i, 2), InStr(Cells(i, 2), "区") + 1)

お礼

mu2011様
いつもご回答ありがとうございます。

アドバイス非常に分かりやすかったです。