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!

 

[kvsrinivasamurthy]

For which line you are getting error. When you debug line will be highlighted.

 

[jbjetmore]

It appears to be the ElfseIf line

ElseIf Cells(M(T) + 1, "D") <> "" Then

 

[jbjetmore]

For which line you are getting error. When you debug line will be highlighted.

It appears to be the ElfseIf line

ElseIf Cells(M(T) + 1, "D") <> "" Then

 

[kvsrinivasamurthy]

You have used code in Post#7. Don't use that. Use the code in Post #9. Change sheet name.

 

[MARK858]

It appears to be the ElfseIf line

ElseIf Cells(M(T) + 1, "D") <> "" Then

I cant see why you would get that error on on that line (if that is the line you are using), but if you are running the code when you are on a sheet other than AGED AR DATA you'll need to use the code below or it will act on the wrong sheet.

Sub FindNAx()
    Dim M, T&

    M = Filter(Evaluate("transpose(IF(ISERROR('AGED AR DATA'!D:D),Row(D:D),false))"), False, False)
 
    With Sheets("AGED AR DATA")
    
        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 With
 
End Sub

 

[jbjetmore]

I cant see why you would get that error on on that line (if that is the line you are using), but if you are running the code when you are on a sheet other than AGED AR DATA you'll need to use the code below or it will act on the wrong sheet.

Sub FindNAx()
    Dim M, T&

    M = Filter(Evaluate("transpose(IF(ISERROR('AGED AR DATA'!D:D),Row(D:D),false))"), False, False)
 
    With Sheets("AGED AR DATA")
   
        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 With
 
End Sub

It still appears to be having an error, but now it is showing a different error message associated with the same line of the code.
"Run-time error '13'': Type mismatch."

It seems to get choked up in an instance where there are two "#N/A"s in a row, and no data above the first one. Perhaps these are niche instances where I need to manually fix them unless there is a way of identifying those instances in the code and adjusting them to also be "Filler"

 

[Tupe77]

"I changed the worksheet to the proper name, and then changed the range to just D:D"
I'm not comfort with evaluate, but could it be because of that: there is D:D inside transpose ?

 

[MARK858]

I'm not comfort with evaluate, but could it be because of that: there is D:D inside transpose ?

No it is because the 2nd #N/A returns an Error 2042

 

[Peter_SSs]

Given that multiple #N/A values can occur together, I am unsure of the logic for a situation like this

jbjetmore.xlsm
	D
1	Hdr
2	abc
3
4	#N/A
5	#N/A
6	def
Sample

It would seem that when cell D4 is dealt with, since it has no value above and another #N/A below then D4 would get populated with "Filler"
When D5 is considered, it now has a text value above so does it get "Filler" from above or should it be considered in its original state with #N/A above and since it has a non-error value below should it get the "def" from below?

Anyway, you could try these to see if either suits your purpose

Sample data

jbjetmore.xlsm
	D
1	Hdr
2	abc
3	#N/A
4	#N/A
5
6
7	#N/A
8	xx
9	23
10
11	#N/A
12	#N/A
13	asdas
14
15
16	#N/A
17	#N/A
18	#N/A
19
20
21	#N/A
22	#N/A
23	#N/A
24	xvc
AGED AR DATA

Sub ReplaceNA_v2()
  Dim rFound As Range
  
  With Sheets("AGED AR DATA").Columns("D")
    Set rFound = .Find(What:="#N/A")
    If Not rFound Is Nothing Then
      Do
        Select Case True
          Case rFound.Offset(-1).Value <> ""
            rFound.Value = rFound.Offset(-1).Value
          Case Not IsError(rFound.Offset(1).Value)
            If rFound.Offset(1).Value <> "" Then
              rFound.Value = rFound.Offset(1).Value
            Else
              rFound.Value = "Filler"
            End If
          Case Else
            rFound.Value = "Filler"
        End Select
        Set rFound = .Find(What:="#N/A")
      Loop Until rFound Is Nothing
    End If
  End With
End Sub

Result of above code

jbjetmore.xlsm
	D
1	Hdr
2	abc
3	abc
4	abc
5
6
7	xx
8	xx
9	23
10
11	Filler
12	Filler
13	asdas
14
15
16	Filler
17	Filler
18	Filler
19
20
21	Filler
22	Filler
23	Filler
24	xvc
AGED AR DATA

Sub ReplaceNA_v3()
  Dim rFound As Range
  
  With Sheets("AGED AR DATA").Columns("D")
    Set rFound = .Find(What:="#N/A")
    If Not rFound Is Nothing Then
      Do
        Select Case True
          Case rFound.Offset(-1).Value <> "" And rFound.Offset(-1).Value <> "Filler"
            rFound.Value = rFound.Offset(-1).Value
          Case Not IsError(rFound.Offset(1).Value)
            If rFound.Offset(1).Value <> "" Then
              rFound.Value = rFound.Offset(1).Value
            Else
              rFound.Value = "Filler"
            End If
          Case Else
            rFound.Value = "Filler"
        End Select
        Set rFound = .Find(What:="#N/A")
      Loop Until rFound Is Nothing
    End If
  End With
End Sub

Result of above code

jbjetmore.xlsm
	D
1	Hdr
2	abc
3	abc
4	abc
5
6
7	xx
8	xx
9	23
10
11	Filler
12	asdas
13	asdas
14
15
16	Filler
17	Filler
18	Filler
19
20
21	Filler
22	Filler
23	xvc
24	xvc
AGED AR DATA