VBA Help- Looping the Find Function

[jbjetmore]

Hello,

I am new to VBA and largely have been attempting to teach myself how to do this with Google.

Essentially I want part of this macro to loop the 'Find' function to locate every instance of the text "#N/A" in a very large data set. If the line above the cell has data, I want to copy that data and paste it over the "#N/A" cell. If the line above does not have data, I want to copy the data from the cell below and paste it over the "#N/A" cell. If neither the cell above nor the cell below has data, I want it to just input "Filler" over the "#N/A" cell. And I want this to loop until there are no more "#N/A"s.

I know that this is not correct and only partially addresses what I am trying to do, but so far I have this:

Do Until nullVal = False
    Columns("D:D").Select
    Selection.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    If Not IsEmpty(ActiveCell.Offset(-1, 0)) Then
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.Copy ActiveCell.Offset(1, 0)
    ElseIf IsEmpty(ActiveCell.Offset(-1, 0)) Then
    ActiveCell.FormulaR1C1 = "Filler"
    End If
    Loop

If you could help me turn this into what I am trying to accomplish, that would be wonderful, but at the very least, could someone please assist me to code the looping lines correctly? What should the "Do" line read in order to run my code until there are no more results with the "Find" function.

I really appreciate any and all help and consideration!

 

[James006]

Hello,
Have you considered making your life easier with AutoFilter ?

 

[jbjetmore]

Hello,
Have you considered making your life easier with AutoFilter ?

The idea is to automate formatting a very large and constantly changing data set in a workbook, and to relabel a lot of these columns based on the context of the lines around them, so that wouldn’t really get the job done properly. This is just part of the macro but I have had difficulty researching the proper coding to end the loop.

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Would you consider an alternative, non-looping approach to do them all at once?

For the moment I am assuming

1. #N/A values in the column will be the only values that look like an error value. That is, the column will not also include text like "#VALUE!"
2. There will be at least one #N/A value in the column
3. The column does not contain any formulas.

If any of those assumptions may be incorrect, please give details about that.

Try this with a copy of your data.

Sub ReplaceNA()
  With Range("D1", Range("D" & Rows.Count).End(xlUp))
    With .SpecialCells(xlConstants, xlErrors)
      .FormulaR1C1 = "=IF(R[-1]C<>"""",R[-1]C,IF(R[1]C<>"""",R[1]C,""Filler""))"
    End With
    .Value = .Value
  End With
End Sub

 

[kvsrinivasamurthy]

Try this code. Change the range (D1:D20) as required.

Sub FindNA()
Dim M, T&

M = Filter(Evaluate("transpose(IF(ISERROR(Sheet1!D1:D20),Row(D1:D20),false))"), False, False)
For T = 0 To UBound(M)
Cells(M(T), "D") = Cells(M(T) - 1, "D")
Next T
End Sub

 

[MARK858]

Try this code. Change the range (D1:D20) as required.

That only covers the first condition it doesn't cover the below conditions

If the line above does not have data, I want to copy the data from the cell below and paste it over the "#N/A" cell. If neither the cell above nor the cell below has data, I want it to just input "Filler" over the "#N/A" cell.

 

[kvsrinivasamurthy]

Sorry here is the modified code.

Sub FindNA()
Dim M, T&

M = Filter(Evaluate("transpose(IF(ISERROR(Sheet1!D1:D20),Row(D1:D20),false))"), False, False)
For T = 0 To UBound(M)
If Cells(M(T) - 1, "D") <> "" Then
Cells(M(T), "D") = Cells(M(T) - 1, "D")
ElseIf Cells(M(T) + 1, "D") Then
Cells(M(T), "D") = Cells(M(T) + 1, "D")
Else
Cells(M(T), "D") = "Filter"
End If
Next T
End Sub

 

[MARK858]

I think you meant

Sub FindNA()
    Dim M, T&

    M = Filter(Evaluate("transpose(IF(ISERROR(Sheet1!D1:D20),Row(D1:D20),false))"), False, False)
    For T = 0 To UBound(M)
        If Cells(M(T) - 1, "D") <> "" Then
            Cells(M(T), "D") = Cells(M(T) - 1, "D")
        ElseIf Cells(M(T) + 1, "D") <> "" Then
            Cells(M(T), "D") = Cells(M(T) + 1, "D")
        Else
            Cells(M(T), "D") = "Filler"
        End If
    Next T
End Sub

 

[kvsrinivasamurthy]

Yes. You are absolutely correct.

Sub FindNA()
Dim M, T&

M = Filter(Evaluate("transpose(IF(ISERROR(Sheet1!D1:D20),Row(D1:D20),false))"), False, False)
For T = 0 To UBound(M)
If Cells(M(T) - 1, "D") <> "" Then
Cells(M(T), "D") = Cells(M(T) - 1, "D")
ElseIf Cells(M(T) + 1, "D") <> "" Then
Cells(M(T), "D") = Cells(M(T) + 1, "D")
Else
Cells(M(T), "D") = "Filter"
End If
Next T
End Sub

 

[jbjetmore]

Yes. You are absolutely correct.

Sub FindNA()
Dim M, T&

M = Filter(Evaluate("transpose(IF(ISERROR(Sheet1!D1:D20),Row(D1:D20),false))"), False, False)
For T = 0 To UBound(M)
If Cells(M(T) - 1, "D") <> "" Then
Cells(M(T), "D") = Cells(M(T) - 1, "D")
ElseIf Cells(M(T) + 1, "D") <> "" Then
Cells(M(T), "D") = Cells(M(T) + 1, "D")
Else
Cells(M(T), "D") = "Filter"
End If
Next T
End Sub

Thank you for the quick response-

I can't manage to get this to work properly.

I changed the worksheet to the proper name, and then changed the range to just D:D since I need it to run on a varying length of data, but I am getting a Run-time error '13'. What is the best way to correct this?

Sub FindNA()
    Dim M, T&

    M = Filter(Evaluate("transpose(IF(ISERROR('AGED AR DATA'!D:D),Row(D:D),false))"), False, False)
    For T = 0 To UBound(M)
        If Cells(M(T) - 1, "D") <> "" Then
            Cells(M(T), "D") = Cells(M(T) - 1, "D")
        ElseIf Cells(M(T) + 1, "D") <> "" Then
            Cells(M(T), "D") = Cells(M(T) + 1, "D")
        Else
            Cells(M(T), "D") = "Filler"
        End If
    Next T
End Sub