Problem executing code vba

[sofas]

hi how can i modify the code to search in column (C )And put the result in an column (L)

Sub test()
    Dim lr As Long, rng As Range, StartRow As Long, EndRow As Long, StartVal, c
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range(Cells(1, 1), Cells(lr, 1))
    
    Set c = Columns(1).Find("yes", after:=Cells(lr, 1), LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            StartRow = c.Row
            StartVal = c.Offset(0, 5).Value
            Set c = rng.FindNext(c)
            EndRow = IIf(c.Row > StartRow, c.Row, lr)
            Range(Cells(StartRow - 0, 12), Cells(EndRow - 0, 12)) = StartVal
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End Sub

 

[Alex Blakenburg]

As both Rory and Rollis have tried to explain that doesn’t tell us anything we need to know.
We are looking in column C for “yes”
My understanding is that you want to fill down column “L” if that is the case make the change I suggested.
Your picture shows column E, what are we supposed to do with E

Please show the results of the macro AND the results you are expecting.

 

[rollis13]

This is what I elaborated with what I understood (your statement in post #1 and screenshot post #3). To be helpful I changed the column references in letters instead of numbers as Alex Blakenburg did.

Option Explicit
Sub test()
    Dim lr As Long, rng As Range, StartRow As Long, EndRow As Long, StartVal, c
    Dim firstAddress As String
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = Range(Cells(1, "C"), Cells(lr, "C"))
    Set c = Columns("C").Find("yes", After:=Cells(lr, "C"), LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            StartRow = c.Row
            StartVal = c.Offset(0, 2).Value   'offset here means same row, 2 columns right = column "E"
            Set c = rng.FindNext(c)
            EndRow = IIf(c.Row > StartRow, c.Row, lr)
            Range(Cells(StartRow, "L"), Cells(EndRow, "L")) = StartVal
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End Sub

 

[sofas]

This is what I elaborated with what I understood (your statement in post #1 and screenshot post #3). To be helpful I changed the column references in letters instead of numbers as Alex Blakenburg did.

Option Explicit
Sub test()
    Dim lr As Long, rng As Range, StartRow As Long, EndRow As Long, StartVal, c
    Dim firstAddress As String
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = Range(Cells(1, "C"), Cells(lr, "C"))
    Set c = Columns("C").Find("yes", After:=Cells(lr, "C"), LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            StartRow = c.Row
            StartVal = c.Offset(0, 2).Value   'offset here means same row, 2 columns right = column "E"
            Set c = rng.FindNext(c)
            EndRow = IIf(c.Row > StartRow, c.Row, lr)
            Range(Cells(StartRow, "L"), Cells(EndRow, "L")) = StartVal
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End Sub

 

[rollis13]

Maybe:

Option Explicit
Sub test()
    Dim lr As Long, rng As Range, StartRow As Long, EndRow As Long, StartVal, c
    Dim firstAddress As String
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = Range(Cells(1, "C"), Cells(lr, "C"))
    Set c = Columns("C").Find("YES", After:=Cells(lr, "C"), LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            StartRow = c.Row
            StartVal = c.Offset(0, 9).Value   'offset here means same row, 9 columns right = column "L"
            Set c = rng.FindNext(c)
            EndRow = IIf(c.Row > StartRow, c.Row - 1, lr)
            Range(Cells(StartRow, "L"), Cells(EndRow, "L")) = StartVal
        Loop While c.Address <> firstAddress
    End If
End Sub

 

[sofas]

Maybe:

Option Explicit
Sub test()
    Dim lr As Long, rng As Range, StartRow As Long, EndRow As Long, StartVal, c
    Dim firstAddress As String
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = Range(Cells(1, "C"), Cells(lr, "C"))
    Set c = Columns("C").Find("YES", After:=Cells(lr, "C"), LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            StartRow = c.Row
            StartVal = c.Offset(0, 9).Value   'offset here means same row, 9 columns right = column "L"
            Set c = rng.FindNext(c)
            EndRow = IIf(c.Row > StartRow, c.Row - 1, lr)
            Range(Cells(StartRow, "L"), Cells(EndRow, "L")) = StartVal
        Loop While c.Address <> firstAddress
    End If
End Sub

Indeed, this is what is required, thank you and thank you all for providing assistance.

 

[rollis13]

Thanks for the positive feedback, glad we were able to help.