vba search for value replace if not found

[Ironman]

Hello - I have a worksheet with columns A - AF. In column A is employee number, column B is region (of the 8000 rows, there are four regions) and column U has supervisor id. I'm looking for some vba code that would check the value in U2 in column A:A and if not found, replace the value in U2 with another supervisor number, based off the offset value of column B. If supervisorID is not in column A, then IF value in B2 = "ab-us", then replace u2 with "0012345". If value in B2 = "ab-fr", then replace U2 with "0098765", if value in B2 = "ab-xyz" then replace U2 with "0065432", if value in b2 = "ab-378" then change U2 value to "00223344", etc.

If the value is found, then do nothing.

This would then repeated to the lastrow, etc.

Thanks for reading!

5/1/2017 - Adding a sample file attachment...!

[Sintek]

Hi Ironman

Perhaps a sample workbook would help us assist you.

[JLGWhiz]

Hi Ironman

Perhaps a sample workbook would help us assist you.

So B2 value does not match U2 value and B2 = "ab-us", U2 is changed to 0012345 and A2 still does not match U2. What happens?

[Sintek]

Is this what you mean.....

Please Login or Register  to view this content.

[Ironman]

Thank you sintek, I've added a sample file. I tried your suggested code and every value in column U changed to '046903', not just the ones that were not there. The attachment has 8 cells in column U that are highlighted in light green - it is these cells I'd expect to see change to 046903 as the value in column B is "en-US". I added the "NEXT I" to your code.

Please Login or Register  to view this content.

Any thoughts on why it may not be working as planned? thanks again!!!

[Sintek]

K let me try to understand...With your sample
If U2 = A2 then do nothing...Is this right?
If U2 does not = A2 then
If B2 = "en-US" then change U2 to "046903"

[Ironman]

Close....if the value in U2 is not in column A:A, then the offset value for U would be '046903'. Also need to keep that leading '0'. Basically the file contains a list of employees and their supervisor (via id numbers). If a given supervisor is no longer with the company and no longer found in column A as an employee, then the corresponding value in U needs to be updated/changed to '046903'. The file is used as an upload to an application and it will error out on upload if there is a supervisor ID that is not also in column A as an employee. Hope that makes sense!

[jindon]

Please Login or Register  to view this content.

[Ironman]

Thank you jindon. Your code worked for me, but I have to admit, not sure what exactly going on. For example you are referencing column V in your rng = section. Also I'm unfamiliar with the specialcells and what that is doing. Additionally I would need to repeat this code for column AA, AB, AC and AD. Would making a copy and changing the "-19" to "-25" (for AA), "-26" (for AB), etc?

Thoughts? Thanks again!

[jindon]

Confusing...

If you upload a workbook with before/after showing clear logic behind it, it would help.