How to check if string contains key value from dictionary?

[adamvas69]

Hello all, i have table like this:
[TABLE="width: 1000"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]texttexttextKEYVALUEtexttexttext[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]here i need fill value of item for if the key is found in text in column A[/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]texttexttextKEYVALUEtexttexttext[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]here i need fill value of item for if the key is found in text in column A[/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]texttexttextKEYVALUEtexttexttext[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]here i need fill value of item for if the key is found in text in column A[/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]texttexttextKEYVALUEtexttexttext[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]here i need fill value of item for if the key is found in text in column A[/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]texttexttextKEYVALUEtexttexttext[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]here i need fill value of item for if the key is found in text in column A[/TD]
[/TR]
</tbody>[/TABLE]
In my Dictionary i have some values (key,item). I need some code which will check the column A and if column A contains value from keys in dictionary then macro give value of item to column B. For example i have dictionary: Peach-fruit, apple-fruit, strawberry-fruit, carrot-vegetable, cauliflower-vegetable. If cell A1 will contains texttexttextCARROTtexttexttext i need cell B1 to contains vegetable. Thanks for help to all of you and sorry for my English

 

[Oscar Cronquist]

Book1
	A	B	C	D	E	F
1	texttexttextcarrottexttexttext	texttexttextvegetabletexttexttext		peach	fruit
2				apple	fruit
3				strawberry	fruit
4				carrot	vegetable
5				cauliflower	vegetable
6
Sheet1

Array formula in cell B1:
=IF(OR(ISNUMBER(SEARCH($D$1:$D$5,A1))),SUBSTITUTE(A1,INDEX($D$1:$D$5,MATCH(TRUE,ISNUMBER(SEARCH($D$1:$D$5,A1)),0)),INDEX($E$1:$E$5,MATCH(TRUE,ISNUMBER(SEARCH($D$1:$D$5,A1)),0))),A1)

 

[adamvas69]

Thanks for reply, but this will doesnt work with Dictionary object. I have about 60000 rows and dictionary object is the fastest way how to something find and replace. But im not sure if exist any way how to find the key value if it is inside some other string.

 

[MickG]

Could you not do it with "Select Case" as below:-

[COLOR="Navy"]Sub[/COLOR] MG27Apr54
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, oRes
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] True
        [COLOR="Navy"]Case[/COLOR] InStr(1, Dn.Value, "apple", vbTextCompare) > 1, _
            InStr(1, Dn.Value, "Peach", vbTextCompare) > 1, _
            InStr(1, Dn.Value, "Strawberry", vbTextCompare) > 1: oRes = "Fruit"
        [COLOR="Navy"]Case[/COLOR] InStr(1, Dn.Value, "Carrot", vbTextCompare) > 1, _
            InStr(1, Dn.Value, "Cauliflower", vbTextCompare) > 1: oRes = "Vegetable"
         [COLOR="Navy"]Case[/COLOR] Else: oRes = ""
    [COLOR="Navy"]End[/COLOR] Select
Dn.Offset(, 1).Value = oRes
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[adamvas69]

Thank you, Mick, for this case i should really forget to Dictionary object and will do it easily without it. Thank you for your time and help!

 

[MickG]

You're welcome