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]

Re: Help with a Find/Replace Macro

Welcome to the Board!

Please post the VBA code that you currently have, and we can see if it is something we can help you to modify (or if we need to take a different approach than what you have tried so far).

 

[Larcen60]

Re: Help with a Find/Replace Macro

Hey Joe,

Thanks for your help! Pretty much, I got asked if I could figure out how to do this in my office. I think since I have glasses they just assume I can code in VBA

In all seriousness, I haven't had to use VBA in like 6 years so I don't have much retained knowledge. This is all I have right now, any help you can give me would be incredibly appreciated.

Sub NewOne()


Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range


fnd = "#N/A"


Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)


If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If


Set rng = FoundCell


Do Until FoundCell Is Nothing

Set FoundCell = myRange.FindNext(after:=FoundCell)

Set rng = Union(rng, FoundCell)

If FoundCell.Address = FirstFound Then Exit Do

Loop


rng.EntireRow.Interior.Color = RGB(0, 0, 255)

Exit Sub


NothingFound:
MsgBox "No values were found in this worksheet"


End Sub

 

[Joe4]

Re: Help with a Find/Replace Macro

OK, I would choose to do it a little differently.

Also, are those "#N/A" values in column C literal text entries, or are they the "#N/A" errors?

If literal text entries, use this code:

Sub MyMacro()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = True
    
'   Find last row with data in column C
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Loop through all rows in column C
    For myRow = 1 To lastRow
        If Cells(myRow, "C") = "#N/A" Then
            If Cells(myRow, "B") = Cells(myRow - 1, "B") Then
                Cells(myRow, "C") = Cells(myRow - 1, "C")
            Else
                Rows(myRow).Interior.Color = RGB(0, 0, 255)
            End If
        End If
    Next myRow
    
    Application.ScreenUpdating = True
       
End Sub

If there are error codes, then change this line:

        If Cells(myRow, "C") = "#N/A" Then

to this:

        If Application.WorksheetFunction.IsNA(Cells(myRow, "C")) Then

 

[Larcen60]

Re: Help with a Find/Replace Macro

That's awesome, Joe, thanks for the help!

One final thing, I also realized I want to get it to check 1 row ahead as well and if neither the row before or the row after match, THEN highlight the row. I've got the code figured out to check before and after, however I can't seem to figure out the highlight part. Would you mind taking one last look at this and helping me out? I really appreciate the help!!

Sub MyMacro()


Dim lastRow As Long
Dim myRow As Long

Application.ScreenUpdating = True

' Find last row with data in column C
lastRow = Cells(Rows.Count, "C").End(xlUp).Row

' Loop through all rows in column C
For myRow = 1 To lastRow
If Application.WorksheetFunction.IsNA(Cells(myRow, "C")) Then
If Cells(myRow, "B") = Cells(myRow - 1, "B") Then
Cells(myRow, "C") = Cells(myRow - 1, "C")

If Application.WorksheetFunction.IsNA(Cells(myRow, "C")) Then
If Cells(myRow, "B") = Cells(myRow + 1, "B") Then
Cells(myRow, "C") = Cells(myRow + 1, "C")
Else
Rows(myRow).Interior.Color = RGB(255, 0, 0)
End If
End If
End If
End If
Next myRow

Application.ScreenUpdating = True

End Sub

 

[Joe4]

Re: Help with a Find/Replace Macro

One final thing, I also realized I want to get it to check 1 row ahead as well and if neither the row before or the row after match, THEN highlight the row

I am not sure I follow you. There would be no row before Row 1.
Can you try explaining again? Maybe show an example of what you are talking about?

 

[Larcen60]

Sorry for not being clearer!

I have some fields where the "#N/A" is at the top of a list of company names. Since it is at the top, when the macro looks backwards 1 row, the data in Column B does not match and it doesn't replace Column C appropriately. What I'm trying to get it to do is to look before and after 1 row in column B and if either of those two columns match, replace Column C from the original row with the matched data. If neither the row before or after matches, then highlight it. The Input would look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Policy[/TD]
[TD]Company[/TD]
[TD]Client Number[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]Dummy Company[/TD]
[TD]#N/A[/TD]
[TD]For this instance the macro would look down 1 row.[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]Dummy Company[/TD]
[TD]123456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF456[/TD]
[TD]Fake Company[/TD]
[TD]7891011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF456[/TD]
[TD]Fake Company[/TD]
[TD]#N/A[/TD]
[TD]For this instance the macro would look up 1 row.[/TD]
[/TR]
[TR]
[TD]GHI789[/TD]
[TD]Super Fake[/TD]
[TD]#N/A[/TD]
[TD]For this, since neither the row before or after in Column B matches, this would be highlighted[/TD]
[/TR]
[TR]
[TD]JKL894[/TD]
[TD]Not Real at All[/TD]
[TD]5896[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The Output would be:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Policy[/TD]
[TD]Company[/TD]
[TD]Client Number[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]Dummy Company[/TD]
[TD]123456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]Dummy Company[/TD]
[TD]123456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF456[/TD]
[TD]Fake Company[/TD]
[TD]7891011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF456[/TD]
[TD]Fake Company[/TD]
[TD]7891011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI789[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Super Fake

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JKL894[/TD]
[TD]Not Real at All[/TD]
[TD]5896[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 164px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]7891011[/TD]
[/TR]
</tbody>[/TABLE]
</body>Thank you again for all the help!!

 

[Joe4]

This variation should do it. Note that since we have headers, I am starting on row 2.

Sub MyMacro()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = True
    
'   Find last row with data in column C
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Loop through all rows in column C
    For myRow = 2 To lastRow
        If Application.WorksheetFunction.IsNA(Cells(myRow, "C")) Then
            If Cells(myRow, "B") = Cells(myRow - 1, "B") Then
                Cells(myRow, "C") = Cells(myRow - 1, "C")
            Else
                If Cells(myRow, "B") = Cells(myRow + 1, "B") Then
                    Cells(myRow, "C") = Cells(myRow + 1, "C")
                Else
                    Rows(myRow).Interior.Color = RGB(0, 0, 255)
                End If
            End If
        End If
    Next myRow

    Application.ScreenUpdating = True
       
End Sub

 

[Larcen60]

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

 

[mrmmickle1]

Joe's solution works perfect...I just wanted to provide some additional guidance / input in reference to identifying cell errors:

The CVErr function can help to identify other types of errors in addition to "#N/A" Errors. So if you needed to identify a different type of error you could use this function to help.

In the above example you could use syntax like this:

Sub Test()
    If ActiveCell = CVErr(xlErrNA) Then
        MsgBox "I am an #N/A error"
    End If
End Sub

The different types of errors are:

xlErrDiv0 = #DIV/0!
xlErrNA = #N/A
xlErrName = #NA ME?
xlErrNull = #NULL !
xlErrNum = #NUM !
xlErrRef = #REF !
xlErrValue = #VALUE !

So if you wanted to identify a different error you could use this function to accomplish your task. here is some more information that may be of use in the future: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/cell-error-values