A列にある文字データで△△を含むセルの値をB列に□□にして置き換えしたい

質問

A列に文字データが100行まであります。
　　 A　　　　　　　　　　　B 　　
１　ミカンを食べた　　　　　ZZ
２　イチゴを食べた　　　　　XX
３　西瓜を食べた　　　　　　YY
４　ミカンを捨てた　　　　　ZZ
５　西瓜を拾った　　　　　　YY
６　ミカンを無くした　　　　ZZ
７　西瓜を貰った　　　　　　YY
８　イチゴをあげた　　　　　XX

それを表のようにB列（任意の列）に置き換えたいです。
イチゴを含むセルはB列にXXをミカンはZZみたいな感じです。
A列のセルには同じ文字列は（今のところ）含みません。
文字の一部を置き換えて変換し、それを繰り返すところまではできましたが、上記のことがができません。
VBAを使って一発で変換したいのですが教えていただけますでしょうか？

回答No.3

hige_082 · ベストアンサー

こんな感じでよい？

Sub test()
Dim a
Dim b
Dim i, j
a = Array("ミカン", "イチゴ", "西瓜")
b = Array("ZZ", "XX", "YY")
For i = 0 To UBound(a)
For j = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If InStr(Cells(j, 1).Value, a(i)) > 0 Then Cells(j, 2).Value = b(i)
Next j
Next i
End Sub

参考まで

お礼

そうですこれがやりたかった事です。
素晴らしい。
大変勉強になりました。
ありがとうございました

回答No.2

myRange

回答１さんのいう変換表というのは

＿Ｆ＿＿＿Ｇ＿＿
ミカン＿＿黄＿＿
スイカ＿＿緑＿＿
スイカ＿＿緑＿＿

こういう表を別なセルに作っておいた方が汎用性があるということです。
で、こういう表がＦ、Ｇ列にあるとして。。。。

'------------------------------
Sub test()
　Dim i As Long
　Dim myCell As Range
　Dim myTable As Range
　Set myTable = Range("F1", Cells(Rows.Count, "F").End(xlUp).Offset(0, 1))
　For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
　　　Set myCell = myTable.Find(Cells(i, "A").Value, , xlValues, xlPart)
　　　　　If Not myCell Is Nothing Then
　　　　　　　Cells(i, "B").Value = myCell.Offset(0, 1).Value
　　　　　End If
　Next i
End Sub
'------------------------------------

データが少なければ、For Nextなどで変換表をぐるぐる回してもいいのですが
データが多い場合は上記のようにFindメソッドを使うほうがいいでしょう。
というより使うべきです。処理スピードが格段に違います。
以上です。

お礼

回答ありがとうございます。
上記ですることが可能でした。

一応VBAの中で完結できればなと思っております。

回答No.1

bin-chan

△△はどこに定義してあるのかな？

ミカンがＺＺ、西瓜がＹＹ、イチゴがＸＸのようですが、それをどうやって処理するのかな？

列Ａと列Ｂだけでは難しいでしょうね。
列Ａはいまのまま、列Ｂに△△を、列Ｃに□□を、列Ｄに置換後も文字列が出るようにするのはアリですか？

１）セルＤ１に式[=SUBSTITUTE(A1,B1,C1)]を設定。
２）セルＤ１の右下の■を下方向にドラッグしてコピー。

お礼

早速誤解とありがとうございます。

＞△△はどこに定義してあるのかな？
VBA内でのことでしょうか？
もしそうでしたらそれも教えていただきたいですが、
すいません、質問の意味がわかりません。

＞ミカンがＺＺ、西瓜がＹＹ、イチゴがＸＸのようですが、それをどうやって処理するのかな？

ミカンが入ったセルをB列に違う文字列に変換するということです。
結果が以下のようになるようにしたいです。
　 A　　　　　　　　　　　B 　　
１　ミカンを食べた　　　　きいろ
２　イチゴを食べた　　　　赤
３　西瓜を食べた　　　　　緑
４　ミカンを捨てた　　　　きいろ
５　西瓜を拾った　　　　　緑
６　ミカンを無くした　　　きいろ
７　西瓜を貰った　　　　　緑
８　イチゴをあげた　　　　赤

もちろん最初にB列は空白です。

＞列Ａと列Ｂだけでは難しいでしょうね。
列Ａはいまのまま、列Ｂに△△を、列Ｃに□□を、列Ｄに置換後も文字列が出るようにするのはアリですか？

A列はそのままでも大丈夫です。
関数を使うのは大体分かるのですが、マクロにして何度も一度で処理をしたいので、VBAで作りたいのですが。