Macro - Fill formula down until adjacent cell contains "string"

[JoseJones]

I've created the following Macro based on some of the posts on this great forum! It works great, but I'd like to tweak it for another purpose where it would fill the formula down until a cell in column AF had the value "#N/A" or stopped having the value "clean". The #N/A is from a formula result so I'm not sure if or how the Macro would treat this.

Thank you!

Sub Y_CleanUp3()
'
' Clean Up Bad Data Macro 3
    Dim LR As Integer
    Range("AH2").Formula = "=VLOOKUP(X2,'[Territory by Zip Code.xlsx]Sheet1'!$A$2:$B$135000,2,TRUE)"
    LR = Range("AF" & Rows.Count).End(xlUp).Row
    Range("AH2").AutoFill Destination:=Range("AH2:AH" & LR), Type:=xlFillDefault
    With Range("AH2:AH" & LR)
    End With
End Sub

 

[NeonRedSharpie]

On "N/A", do you want it to stop filling or just skip that one value? Also, how many rows are we looking at? I'd look into a for loop unless it's a large dataset.

 

[JoseJones]

On "N/A", do you want it to stop filling or just skip that one value? Also, how many rows are we looking at? I'd look into a for loop unless it's a large dataset.

Thank you for replying so fast!

I would like it to stop. Right now the macro fills down the column until it sees an empty cell in AF. So, instead of empty empty I'd like it to stop when it either sees "#N/A" or doesn't see "clean" in AF, whatever is easier. The rows are variable length and can have dozens to hundreds of results based on this formula.

Not sure this helps, but my goal is to eventually piece together a few macros to help speed up data cleaning of these records. Right now I'm making specific steps as I'm trying to undstand how to make the macros. Here's the Macro that precedes the one I need help with, and puts "clean" in the cells and then sorts the column to have the records with "clean" at the top. The next step is to do a VLOOKUP and replace values based on rows with "clean" in them.

Sub Y_CleanBadZips()
'
' Clean Up Bad Zips macro
    Dim LR As Integer
    DateRange = Application.InputBox(Prompt:="What is the date range?")
    Range("AG2").Formula = "=VLOOKUP(AF2,'[" & DateRange & "LATLONG.xlsx]zipcodes'!$B$2:$D$12000,3,FALSE)"
    LR = Range("AF" & Rows.Count).End(xlUp).Row
    Range("AG2").AutoFill Destination:=Range("AG2:AG" & LR), Type:=xlFillDefault
    With Range("AG2:AG" & LR)
    Columns("AG:AG").Select
    Selection.Sort Key1:=Range("AG2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End With
End Sub