エクセル マクロ：文字変更

質問

教えてください。

sheet5にデータがあります。
マクロを実行すると、一番右の列のセルに○があると●と書き換える
一番右の列のセルに△があると▲と書き換えるコードを作成しています。
下記のコードでは時間がかかってしまいます。

省略
If Sheets("sheet5").Cells(r, cmax).Value = "○" Then
Sheets("sheet5").Cells(r, cmax).Value = "●"
省略

AutoFilterを使用してマクロを作成しましたが、列に○と△が両方無いと
範囲指定したセルがすべて▲となってしまいます。
下記コードをどのように手直ししたらよいのか教えて頂けないでしょうか。
よろしくお願いします。

Sub 文字変更()

Dim c As Integer
Dim cmax As Integer
Dim rmax As Long

With Sheets("sheet5")
rmax = .Range("A3").End(xlDown).Row
cmax = .Range("A3").End(xlToRight).Column

.Rows("1:1").Select

Selection.AutoFilter

For c = 2 To cmax
Selection.AutoFilter Field:=c, Criteria1:="○"
.Cells(1, c).Offset(1, 0).Select
.Range(Selection, Cells(rmax, c)).Value = "●"
Selection.AutoFilter Field:=c, Criteria1:="△"
.Cells(1, c).Offset(1, 0).Select
.Range(Selection, Cells(rmax, c)).Value = "▲"
Selection.AutoFilter Field:=c
Next c
End With

Selection.AutoFilter

End Sub

回答No.3

tsubuyuki · ベストアンサー

まぁ、乗りかかった船ですし。

考え方は色々ありますが、一例として、
　　　　If WorksheetFunction.CountIf(.Columns(cmax), "○") > 0 Then
　　　　　　　　.Range("A1").AutoFilter Field:=cmax, Criteria1:="○"
　　　　　　　　.Range(Cells(2, cmax), Cells(rmax, cmax)).SpecialCells(xlCellTypeVisible) = "●"
　　　　End If
などとして、フィルタをかける前に
「最終列（cmax列）に該当する値があるかどうか」を調べてやるのが比較的楽です。
ちなみに、ワークシート関数のＣＯＵＮＴＩＦと意味も使い方も同じです。

他にも考え方は様々ですが、
ＣＯＵＮＴＩＦ関数なら馴染みやすいかなぁ、と思います。

他人さまの回答への補足に対するレスポンスで恐縮ですが、
置換機能を使った＃１さんの回答への補足で
>> Selection.Replace What:="○", Replacement:="●", LookAt:=xlPart, _
>> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
>> ReplaceFormat:=False
> 最初のコードを"○"→"１","●"→"０、
> "２番目のコードを"△"→"１１","▲"→"２"と書き直して実行したところ、
> 最初のコード実行で１１を０に変換してしまいました。
とありますが、エクセルの「挙動」としてはごく自然なことです。

通常時（ＶＢＡを使わずに）置換機能をお使いになったことはありますか？
オプションの中に「セルの内容が完全に同一であるものを検索する」というチェックがあります。
そのチェックをオンにしてやると、検索する文字列に「完全に一致したモノ」だけを置換できます。
つまり、検索する文字列に「1」を指定してやると、
「1」だけを置換することが可能です（「11」はスルーしてくれます）。

「マクロの記録」機能を使って、これをコードに録ってやると、
　　　　Columns(cmax).Replace What:="1", Replacement:="0", LookAt:=xlWhole
と書かれます（検索する範囲は書き換え、余分な部分は消去していますが）。
＃１さんの回答では「LookAt:=xlPart」となっていると思いますが、
コレは「文字列の一部でも一致したら、その一部を置き換え」の意味ですから、
「11」⇒「00」に置換され、エクセルの都合上「0」と入力されてしまった、という事です。
で、コレを回避するために「LookAt:=xlWhole」として「完全一致」を狙ってやります。
ココに気付けば、実際問題として「置換機能を使う方が早い」のですから、
速度を求めるのであれば最善、と言えます。

コード自体も
Sub Macro3()
Dim cmax As Long
　　　　cmax = ActiveSheet.Range("A3").End(xlToRight).Column
　　　　　　　　Columns(cmax).Replace What:="1", Replacement:="0", LookAt:=xlWhole
　　　　　　　　Columns(cmax).Replace What:="11", Replacement:="2", LookAt:=xlWhole
End Sub
これだけで済んでしまうのです。

まぁ、何を言いたいか、と言うと・・
「ＶＢＡを扱うのであれば、基本機能も同時に覚えておくと良いですね。」
「マクロの記録機能は、書き方を覚えるのに便利ですよ。」
という２点です。

基本機能で出来ることであれば「マクロの記録」である程度のコードを作成できます。
ソレに手を加えて、使い易いコードに改造していくのが
ＶＢＡを覚えるための近道であると私は思います。
そのためには「基本機能でできること」も疎かにしてはいけない、と言うことですね。

少々説教くさい内容になってしまいましたが、
何かの参考になれば、と思います。

お礼

ご指導ありがとうございます。
通常時の置き換え機能を使ったことがありませんでした。
通常時の機能も使いこなせるように、あれこれと触ってみたいと思います。
マクロの記録で記録したコードを修正して、「F８」キーを押して実行画面を確認出来る事を最近知ったばかりです。
まだまだ知らないことが多いのでご指導のほどよろしくお願い致します。

回答No.2

tsubuyuki

やり方は人それぞれ、いろいろあって良いと思いますが、
今回の処理を例にとると、個人的には
「フィルタを使うなら、ループは必要無い」
「ループさせるなら、フィルタは必要無い」
と思います。

前者、フィルタを使うなら（一部だけですが）
Sub Macro1()
Dim rmax As Long, cmax As Long
　　　　With Sheets("sheet5")
　　　　　　　　rmax = .Range("A3").End(xlDown).Row
　　　　　　　　cmax = .Range("A3").End(xlToRight).Column

　　　　　　　　.Range("A1").AutoFilter

　　　　　　　　.Range("A1").AutoFilter Field:=cmax, Criteria1:="○"
　　　　　　　　' ↓フィルタで抽出されたセルのみを選択して"●"に置き換え
　　　　　　　　.Range(Cells(3, cmax), Cells(rmax, cmax)).SpecialCells(xlCellTypeVisible) = "●"

　　　　　　　　.Range("A1").AutoFilter Field:=cmax, Criteria1:="△"
　　　　　　　　' ↓上記同様"▲"に置き換え
　　　　　　　　.Range(Cells(3, cmax), Cells(rmax, cmax)).SpecialCells(xlCellTypeVisible) = "▲"

　　　　　　　　.Range("A1").AutoFilter
　　　　End With
End Sub
こんな具合に「可視セル」だけを選択して一気に書き換えた方が早いです。

後者、ループを使うなら
Sub Macro2()
Dim rmax As Long, cmax As Long, r As Long
　　　　With ActiveSheet
　　　　　　　　rmax = .Range("A3").End(xlDown).Row
　　　　　　　　cmax = .Range("A3").End(xlToRight).Column

　　　　　　　　For r = 3 To rmax
　　　　　　　　　　　　If .Cells(r, cmax) = "○" Then
　　　　　　　　　　　　　　　　.Cells(r, cmax) = "●"
　　　　　　　　　　　　ElseIf .Cells(r, cmax) = "△" Then
　　　　　　　　　　　　　　　　.Cells(r, cmax) = "▲"
　　　　　　　　　　　　End If
　　　　　　　　Next
　　　　End With
End Sub
こんな感じで直接書き換えてしまった方が確実です。

> 列に○と△が両方無い
と言うケースが想定されるなら、後者の方が安全です。
前者の場合は、フィルタが空振りした時のエラー処理が面倒なので。

ちなみに両者とも５０００件のデータに対しても１秒かかりません。
（どちらかと言うと、前者の方がちょっと早いですが。）

「置換機能」を使うのももちろんアリです。
（多分、これが処理時間は「一番早い」です。）

お礼

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

補足

ご回答ありがとうございます。
質問文で表全体の書き換えのコードをコピペしてしまい混乱させてしまいました。
前者と後者とでは前者の方が処理時間が３割ほど早い為、どうにか前者を使いたいのですが…
前者の空振り対策を教えて頂けないでしょうか。
よろしくお願いします。

回答No.1

MC_K

meina04 さん

こんばんは!

下記のようなコードでいかがでしょうか?
今回の例では、D列を右端の列として記載しています。
試しに、テストしてみてください。

Columns("D:D").Select
Selection.Replace What:="○", Replacement:="●", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Columns("D:D").Select
Selection.Replace What:="△", Replacement:="▲", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

よろしくお願いいたします。

お礼

通常時の機能を使いこなせていなかった為、捕捉で余計な質問をしてしまいました。
自分に合ったマクロに書き換える事ができるよう、まだまだ勉強が必要なようです。
今後ともご指導をお願い致します。

補足

ご回答ありがとうございます。
使用してみて処置の速さに驚きました。
〇や△を他の形に置き換えて使用したところ、最初のコードを"○"→"１","●"→"０、"２番目のコードを"△"→"１１","▲"→"２"と書き直して実行したところ、最初のコード実行で１１を０に変換してしまいました。
説明不足で申し訳ないのですが、ご指導のほどよろしくお願い致します。