# Problem executing code vba



## sofas (Dec 27, 2022)

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
```


----------



## rollis13 (Dec 27, 2022)

Since I have no idea about the layout of your data I'll take a guess. Change the search line to this where 3 = column C :
	
	
	
	
	
	



```
Set c = Columns(3).Find("yes", After:=Cells(lr, 3), LookIn:=xlValues, LookAt:=xlWhole)
```


----------



## sofas (Dec 27, 2022)

rollis13 said:


> Since I have no idea about the layout of your data I'll take a guess. Change the search line to this where 3 = column C :
> 
> 
> 
> ...


I want to repeat the value provided there is a word  yes


----------



## rollis13 (Dec 27, 2022)

So what? What's wrong with my suggestion? 
A screenshot is of no help if you don't show at least the whole used range and say what goes where.


----------



## sofas (Dec 28, 2022)

rollis13 said:


> So what? What's wrong with my suggestion?
> A screenshot is of no help if you don't show at least the whole used range and say what goes where.


Sorry for not being able to communicate the idea. I just want when checking for the presence of the word Yes in column A, the corresponding value in column L is repeated several times until reaching the same word Yes, then re-execute the same command on the corresponding value, the other word yes, and so on


----------



## RoryA (Dec 28, 2022)

sofas said:


> I just want when checking for the presence of the word Yes in column A, the corresponding value in column L is repeated


Your current code appears to do exactly that. If you want to look in column C instead of column A, then you have been given code for that. If that code is not doing what you want, then you need to explain _in what way_ it is not working.


----------



## Alex Blakenburg (Dec 28, 2022)

See if this helps:


```
Sub test()
    Dim lr As Long, rng As Range, StartRow As Long, EndRow As Long, StartVal, c as Range    
    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 = Cells(StartRow, "E").Value
            Set c = rng.FindNext(c)
            EndRow = IIf(c.Row > StartRow, c.Row, lr)
            Range(Cells(StartRow - 0, "L"), Cells(EndRow - 0, "L")) = StartVal
        Loop While c.Address <> firstAddress
    End If
End Sub
```


----------



## sofas (Dec 28, 2022)

Alex Blakenburg said:


> See if this helps:
> 
> 
> ```
> ...


Thank you, the code works fine when copying the values from column E. I want the same values as those in column L It is repeated with an existing condition in column C


----------



## Alex Blakenburg (Dec 28, 2022)

I have logged off for the night. If you are saying that you want to copy the value from the yes row and L column down the L column, then just change the StartVal line from column E to column L

```
StartVal = Cells(StartRow, "L").Value
```


----------



## sofas (Dec 28, 2022)

Alex Blakenburg said:


> I have logged off for the night. If you are saying that you want to copy the value from the yes row and L column down the L column, then just change the StartVal line from column E to column L
> 
> ```
> StartVal = Cells(StartRow, "L").Value
> ```











						Loop to Fill Down to Next Specified Value and Repeat
					

I have qualitative values in column A and quantitative values in column B associated with them. I want to look for a specific value in column A, starting with A1. Once the value is found, I want to...




					stackoverflow.com


----------



## sofas (Dec 27, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

rollis13 said:


> 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
> ...


----------



## rollis13 (Dec 28, 2022)

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 (Dec 28, 2022)

rollis13 said:


> Maybe:
> 
> 
> 
> ...


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


----------



## rollis13 (Dec 28, 2022)

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


----------

