Finding Yes and No valves and pasting them

[AK_Excel_13]

The first column in my spreadsheet has four different types of values
"yes" , "no" , "see above" & "" (blank values)

I would like excel to look at the first column and find values that say "see above". Then I want it to look at the cells above it and copy the first "yes" or "no" that it finds and paste it in the "see above" see. I'm not sure how to do this because there are random blanks in between the "yes" "no" & the "see above" values.

Here is the code that I have so far.

Sub yes_no()


For i = 2 To 1000


If Cells(i, 1).Value = "see above" Then


Next i


End Sub

Thanks!

 

[bbott]

Welcome to the board!

Try this:

Sub yes_no()

    Dim i As Long
    Dim x As Long
    
    For i = 2 To 1000
        x = 1
        If Cells(i, 1).Value = "see above" Then
            Do Until Cells(i, 1).Offset(-x, 0) = "yes" Or Cells(i, 1).Offset(-x, 0) = "no"
                x = x + 1
            Loop
        Cells(i, 1) = Cells(i, 1).Offset(-x, 0)
        End If
    Next i

End Sub

 

[AK_Excel_13]

It worked! Thanks!
This is cool site to learn excel.

 

[bbott]

You're welcome.

 

[Gavin T]

You can also use this.

Sub Macro1()
    Range("A:A").Replace "see above", "=VLOOKUP(REPT(""z"",50),INDIRECT(""A2:A"" & Row()-1),1)"
End Sub