エクセルのセルの一部の書式変更について

質問

エクセルのセルの中の文字の一部を書式を変更するのに、ループは使えないのでしょうか。
他の回答の中に変換のマクロがあってそれを使わせてもらったのですが、１セルの中で何回か変更をしようとループ処理をしたのですが、反映されませんでした。
具体的には
=================================
Option Explicit
private Const FNAME As String = "ＭＳ ゴシック"
Private Const FSTYLE As String = "標準"
Dim mWh As String
Dim mFa As String
Dim c As Range

For j = 1 To 100
Sheets("さくいん").Select
mWh = "東京" & j & " "
Set c = ActiveSheet.UsedRange.Find(What:=mWh, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=True, MatchByte:=True)
If Not c Is Nothing Then
mFa = c.Address
ReplaceFont c, mWh
Do
Set c = ActiveSheet.UsedRange.FindNext(c)
If c.Address = mFa Then Exit Sub
ReplaceFont c, mWh
Loop Until c Is Nothing
End If

Next j
End Sub
'----
Private Sub ReplaceFont(rng As Range, strSearch As String)
Dim i As Integer
Dim Ln As Integer
Ln = Len(strSearch)
i = InStr(rng.Value, strSearch)
With rng.Characters(i, Ln).Font
.Name = FNAME
.FontStyle = FSTYLE
End With
End Sub
======================================
というかんじで、「東京1」～「東京100」のフォントをゴシックにしたいのですが、１セルの中に「てすと　東京1　品川区　東京22　・・」といった具合に複数の該当文字が存在するのです。
ループなしで１つずつ実行すると問題ないのですが、・・・
よろしくお願いいたします。

回答No.2

myRange · ベストアンサー

見たところFindメソッドの終了方法が間違っているようです。

'----------------------------------------
Option Explicit
　　Private Const FNAME As String = "ＭＳ ゴシック"
　　Private Const FSTYLE As String = "標準"

'-----------------------------------------
Sub Test()
　Dim mWh As String
　Dim mFa As String
　Dim c As Range
　Dim j As Integer

　Sheets("さくいん").Select

　For j = 1 To 100
　　　mWh = "東京" & j & " "
　　　Set c = ActiveSheet.UsedRange.Find(What:=mWh, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=True, MatchByte:=True)
　　　If Not c Is Nothing Then
　　　　　mFa = c.Address
　　　　　Do
　　　　　　　ReplaceFont c, mWh
　　　　　　　Set c = ActiveSheet.UsedRange.FindNext(c)
　　　　　Loop Until c.Address = mFa
　　　End If
　Next j

End Sub

'------------------------------------------------
Sub ReplaceFont(rng As Range, strSearch As String)
　　Dim i As Integer
　　Dim Ln As Integer

　　Ln = Len(strSearch)
　　i = InStr(rng.Value, strSearch)
　　With rng.Characters(i, Ln).Font
　　　　.Name = FNAME
　　　　.FontStyle = FSTYLE
　　End With
End Sub
'---------------------------------------------

●Findメソッドは慣れないとちょと使いにくいかもしれません。
データ数が多くなければ、回答１さんも指摘されてるように
Findメソッドを使わなくてもいいような気もします。

'--------------------------------------------
Sub Test222()
　Const FNAME As String = "ＭＳ ゴシック"
　Const FSTYLE As String = "標準"

　Dim Rng As Range
　Dim mWh As String
　Dim Ln As Integer
　Dim i As Integer
　Dim j As Integer

　Sheets("さくいん").Select

　For j = 1 To 100
　　　mWh = "東京" & j & " "
　　　Ln = Len(mWh)

　　　For Each Rng In ActiveSheet.UsedRange
　　　　　i = InStr(Rng.Value, mWh)
　　　　　If i > 0 Then
　　　　　　　With Rng.Characters(i, Ln).Font
　　　　　　　　　.Name = FNAME
　　　　　　　　　.FontStyle = FSTYLE
　　　　　　　End With
　　　　　End If
　　　Next Rng

　Next j

End Sub
'--------------------------------------------

お礼

myRangeさん、ありがとうございました。うまくいきました。

回答No.1

imogasi

まあ凝ったことを良くやると言う印象。
（１）エクセルはセルという単位が有るのが特徴であるのはわかるだろう。
検索については、
（１）その文字列が見つかるセルの位置を知る
（２）そのセルの中の文字列の位置を知る
この２つがある。質問者はこれらをはっきり認識して無いようにコードでは見える。質問も明確にそういうことを始めに書かず、コードを丸写しして、最後のほうに、「東京1」～「東京100」を探しているんだ（それもあるセルの他の文字列の中に混在していると）と書いている。回りくどく、ストレートでない。
ーーー
Ｆｉｎｄ関数は（１）のセルを探すメソッドです。だから半分解決しても、（２）は別の方法が必要。
ーー
もうひとつＦｉｎｄメソッドは
（Ａ）セル全体の値で検索
（Ｂ）セルの文字列の一部で検索
が、LookAt:=xlPartで使い分けられる。しかしセル内での位置は、教えてくれない。
ーー
以上から
（Ａ）Ｆｉｎｄ（とＦｉｎｄＮｅｘｔ）とＩｎｓｔｒ関数
（Ｂ）Ｉｎｓｔｒ関数一本やり
の２つが考えられる。
当然（Ｂ）の方が統一的で簡単になり、私は好きだ。
ーー
質問者は（Ａ）の途中で行き詰ったのかな。
ーーー
私なりの簡単な例をやってみる
例データ
「東京」の文字を赤色にした。
Ａ１：Ｄ１０
ー ー ー ー
ー aaa東京ｄｄｄｓｓｓ
ー ー ー ー
ー ｘｘaaa東京ｓｓｓ
ー ー ー ー
ー ー ー ー
ー ー ー ー
ー ｑｑｑ東京ｓｓ東京ｓ ー
ー ー ー ー
ー ー ー a東京ｓｓｓ東京f東京
ーーーーーー
Sub test01()
For Each cl In Range("A1:D10")
s = 1
Do
p = InStr(s, cl, "東京")
If p <> 0 Then
MsgBox cl.Address & "の　" & p & "文字目から東京あり"
cl.Characters(p, 2).Font.ColorIndex = 3
s = p + 2
End If
Loop While p <> 0
Next
End Sub
東京の文字を赤色にした。
ただ質問では「東京ｘｘｘ｝と数字が付いているらしいが、これを組み込むロジックは、簡単なのは(注）思い浮かばないので質問者で考えて。
（注）最悪「東京」の京、すなわち上記コードでｐ＋３文字以後が数字である（ＩｓＮｕｍｅｒｉｃで聞く）間は（着色の）範囲に含める、というロジックで出来るが、泥臭いなあ。
ほかに
Sub test02()
a = "12344dfgsdfg"
b = "asd123"
MsgBox Val(a)
MsgBox Val(b)
End Sub
のＶＡＬ関数が使えるかも。見つかった東京の後３文字をＶＡＬして返ってきた数字を使えないか。文字列から始まると０が返るようだが。

お礼

imogasiさん、ご回答ありがとうございます。早速確認させていただきます。
（ご指摘のとおり、コーディングに際してはっきりした認識をせずに処理を進めており、それゆえ質問もあやふやになっていると思います。わかりにくくすみませんでした。）