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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Policy[/TD]
[TD]Company Name[/TD]
[TD]Client Number[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]ABC123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="width: 117"]Fake Company[/TD]
[/TR]
[TR]
[TD="width: 117"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]45678[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]ABC123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="width: 117"]Fake Company[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]DEF456[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="class: xl65, width: 117"]Dummy Company[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Policy[/TD]
[TD]Company Name[/TD]
[TD]Client Number[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]Fake Company[/TD]
[TD]45678[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]Fake Company[/TD]
[TD]45678[/TD]
[/TR]
[TR]
[TD]DEF456[/TD]
[TD]Dummy Company[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


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.