Instr and Mid statements to find/replace data from 1 wksht to another 1

[scpankow]

I have one worksheet that has four columns, Description, Category, New Number, Old Number. I have a second worksheet that has four columns, Sec. Reg. No., HIPAA Req., Doc. Type, and Doc. Name/Description. I need to search the data in the Doc. Name/Description field for Old Numbers and replace them with the corresponding New Numbers from the first sheet. I believe that I need to use Instr and Mid, but I am a VBA newbie and not sure how to do so. Any and all help would be greatly appreciated.

Thanks,

Shannon

 

[Bong]

Set s2 = Workbooks("GuidelinesMapping_oldtonew.xls").Sheets("Sheet1")

Either there is no "GuidelinesMapping_oldtonew.xls", or it isn't open, or it doesn't have a sheet named "Sheet1".

What is the <do your thing>?

That's what I don't know. Now that you've found a cell in s1 that has a value that contains a value in s2, what do you want to do?

_________________
Bob Rashkin

 

[SkipVought]

FYI: Looping thru cells to make multiple changes is slooooower than doing Edit > Replace.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[scpankow]

I want to take the value that I found in s1 and replace it with the value that is in the adjacent cell to the left of the value in s2.

Shannon

 

[Bong]

... value that I found in s1

c2.value

... the adjacent cell to the left of the value in s2

c1.offset(0,-1)

_________________
Bob Rashkin

 

[Bong]

Skip is absolutely correct. Again, if we saw the data, it might be possible to help you do it more efficiently.

_________________
Bob Rashkin