Find/Replace Macro

[Larcen60]

Hello!

I'm trying to create a macro that does the following:

1. Find all instances of the value "#N/A" in Column C
2. When it finds "#N/A" in Column C compare the value in Column B of that same row to Column B of the row above it.
3. If Column B matches the Row above it, then copy Column C from that row into the Row below it.
4. If Column B does not match the row above it, highlight that row Blue.

Here is what the input would look like:

Policy	Company Name	Client Number
ABC123 <tbody> </tbody>	Fake Company <tbody> </tbody>	45678 <tbody> </tbody>
ABC123 <tbody> </tbody>	Fake Company <tbody> </tbody>	#N/A
DEF456 <tbody> </tbody>	Dummy Company <tbody> </tbody>	#N/A

<tbody>
</tbody>

And here's what I'm looking for the Output to look like:

Policy	Company Name	Client Number
ABC123	Fake Company	45678
ABC123	Fake Company	45678
DEF456	Dummy Company	#N/A

<tbody>
</tbody>

So far I have a loop that looks for all instances of "#N/A" and highlights if they don't match, but does anyone have any ideas of how I would code the "Find/Replace" part of that? Thanks very much for any help!

 

[Joe4]

That did it! Thanks so much for your help, I really appreciate it!

You are welcome.

 

[Rick Rothstein]

Here is another macro (using a completely different approach) that you can also consider...

[table="width: 500"]
[tr]
	[td]Sub FixNAs()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace(Replace(Replace("IF(ISNA(C2:C#),IF(B2:B#=B1:B@,C1:C@,IF(B2:B#=B3:B%,C3:C%,C2:C#)),C2:C#)", "#", LastRow), "@", LastRow - 1), "%", LastRow + 1))
  Intersect(Columns("C").SpecialCells([B][COLOR="#FF0000"]xlConstants[/COLOR][/B], xlErrors).EntireRow, ActiveSheet.UsedRange).Font.Color = vbBlue
End Sub[/td]
[/tr]
[/table]

NOTE: The above assume the #N/A in Column C are not produced from a formula. If they are produced from a formula, then change the red highlighted text to xlFormulas instead.