Compare text, replace with number

[kokkentor]

I have two sheets
Sheet A-text contains text in columns Q, R, S, T, W
Sheet A-ID contains a number in column A and text in column B. (The number is the ID for the text.)

I want to search through columns Q, R, S, T and W in Sheet A-text.
If text similiar (like; not necessary identical) to text in column B in Sheet A-ID is found, then replace the text in Sheet A-text with the number corresponding to the text in Sheet A-ID (i.e. with the number in column A, Sheet A-ID)

Can anybody help me with a start?

Thanks!

 

[kokkentor]

Edited after your last code

It runs, but then the rngUnique was set to A colum, it should be the B column, since column B contains the unique string I am searching for in Sheet("A-text")

Set rngUnique = Sheets("A-ID").Range("B2:B200")

When it ran, it only replaced the content of Q1 (which happened to contain a "1" among other tings, with the text from Sheet("A-ID").

So then I guess the offset should be -1 instead of 1?

 

[lozzablake]

note that the code is assume to be one cell to the right of the search text:

 varCode = rngUnique.Offset(i - rngUnique.Row, 1).Resize(1, 1).Value

 

[kokkentor]

Yess!
Changing the column offset, and voilá!
I mean: Thank you a thousand times! This really earned you some pints. If you're ever in Oslo:

I noticed I had to specify the rngUnique exactly, if not it only searched and searched for empty cells until blue in the face.

Well, I guess I may use some kind of like-operator when defining the strCode?

 

[lozzablake]

It's a while since I was in Oslo, but I recall the price of a pint and if I am ever there again I will look you up!

Yes you need to specify exactly. You could determine the last row using:

cells(65536,2).end(xlup).row

I do not think you can use wildcards in the Find method. However, you could do a search here for fuzzy searches and see if there is anything

 

[kokkentor]

Yeah, the prices... and they wonder why we drink!.

Thanks again!