Unsure of how to construct loop

[Belair58]

Hello,

I have thousands of records that came out with really crappy formatting.

BESBAN Best Banner Sign
LAURA TUCKER Yes
LAURA TUCKER-2 Yes
ROCHELLE BROGLER Yes
ROCHELLE BROGLER-2 Yes
BESEEN BeSeen Today.Com
BESNEO Best Neon
STEVE GOLDZWEIG Yes

I need to look for the first record that have Yes and then look above it for the first record that isn't yes. Copy that record and paste that value over the Yes. So in the example Laura Tucker thru Rochelle Brogler-2 would be Best Banner Signs in column 2, and Steve GoldWeig would have Best Neon in column 2.

Private Sub CopyNames()

For i = 2 to a
If Worksheets("Worksheet1".cells(i,2).Value = "Yes" Then

Here's where I need help, how do I tell it to look for the next cell above that doesn't contain "Yes"?

Thanks for any help you can give me.

 

[JLGWhiz]

try this

Sub t()
Dim c As Range
With ActiveSheet
    For Each c In .Range("B3", .Cells(Rows.Count, 2).End(xlUp))
        If LCase(c.Value) = "yes" And LCase(c.Offset(-1).Value) <> "yes" Then
            c.Replace "yes", c.Offset(-1).Value
        End If
    Next
End With
End Sub

 

[gsistek]

Private Sub CopyNames()
    Dim strLastNonYes As String
    Dim rngCurrent As Range
    
    For Each rngCurrent In Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(2))
        If rngCurrent = "Yes" Then
            rngCurrent = strLastNonYes
        Else
            strLastNonYes = rngCurrent
        End If
    Next rngCurrent
End Sub

 

[Belair58]

JLGWhiz,

Thanks for the code, but it didn't work for me. I have to get this moved on to the next department, but I plan to look at it and see if I can figure out why.

Thanks again.

 

[Belair58]

GSISTEK,

Thank you so much, that worked great. I'm looking at the code, and I think I understand how it's working. It's cycling through column 2 looking for a Yes, if it finds one (here's where it's a little grey) What is causing it to change value. I see rngCurrent = strLastNonYes. Is it capturing the value as a string in column 2 if it's not a Yes, and then substituting that value?

Thanks again,

 

[JLGWhiz]

JLGWhiz,

Thanks for the code, but it didn't work for me. I have to get this moved on to the next department, but I plan to look at it and see if I can figure out why.

Thanks again.

Probably would work better as

Sub t()
Dim c As Range
With ActiveSheet
    For Each c In .Range("B3", .Cells(Rows.Count, 2).End(xlUp))
        If LCase(c.Value) = "yes" And LCase(c.Offset(-1).Value) <> "yes" Then
            [COLOR=#daa520]c = c.Offset(-1).Value
[/COLOR]      End If
    Next
End With
End Sub

same priniciple as @gsistek code but expressed differently.