Search cells in VBA for value

[andrewb90]

Hello all,

I feel like there should be a way to successfully do this, but I can't quite wrap my head around doing it properly. (I know I can do a very very long version to achieve what I want, but I'm trying to get better at writing more efficient code.)

If Sheets("Period" & P).Range("R" & R [COLOR=#ff0000]- 1[/COLOR]).Value = "yes" Then

Sheets("PDFPage").Range("L23").Value = Sheets("Period" [B][COLOR=#000080]& P-?)[/COLOR][/B].Range("B" & R).Value

Basically when the current row (defined as R) does not have the value of "yes" then I want to go backwards to find the next row (indicated in red) that has the value. Then I would need to find the corresponding date which would be in column b of the row. I wouldn't need to go back more than 10 rows, as a yes would appear well before that. In the event that a yes is not found, we could have a phrase "not found"

Now, there is one other condition, that I'm sure I could create separately, but if there is an elegant way to incorporate this, that'd be great.

If Sheets("PDFPage").Range("M1").Value <= 7 Then

If that value is 7 or less, then in the event that yes can't be found (which will only go back as far as 7 rows), then an alternate Else phrase would be needed.

Hopefully that makes sense. My thoughts were to try to do a loop that would step backwards by line, but as mentioned, I couldn't quite figure out how to incorporate what I wanted.

 

[AlphaFrog]

This searches for the first "yes" value starting from row R and upwards 10 cells. If a match is found, it returns the date from column B of the matched row. I didn't understand the "other" condition.

    [color=darkblue]Dim[/color] rngYes  [color=darkblue]As[/color] Range
    
    [color=darkblue]With[/color] Sheets("Period" & P).Range("R" & R - 9).Resize(10)
        [color=darkblue]Set[/color] rngYes = .Find(What:="Yes", _
                           After:=.Cells(1), _
                           LookIn:=xlValues, _
                           LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious, _
                           MatchCase:=False)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
                           
    [color=darkblue]If[/color] [color=darkblue]Not[/color] rngYes [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        MyDate = rngYes.Offset(, -16).Value
        MsgBox MyDate
    [color=darkblue]Else[/color]
        MsgBox "No 'Yes' found.", , ""
    [color=darkblue]End[/color] [color=darkblue]If[/color]

This uses the .Find method. See the link for an explanation.

 

[andrewb90]

Perfect thank you!
That other condition, probably isn't important. It was intended just to provide a different message, if M1 had a value of 7 or less, as that would indicate the beginning of the sheet. But i made it work!

 

[andrewb90]

One other question. I noticed that if the original row variable "R" has a value of yes, it will return with that, I need to start from the row previous to that. How would I adapt the code?

 

[AlphaFrog]

With Sheets("Period" & P).Range("R" & R - 9).Resize([COLOR="#FF0000"]9[/COLOR])