VBA to search range and return corresponding values in neighboring column based on further conditions

[morbenforsen]

Hello, I'm trying to use VBA to replace numerous formulas. I am hoping someone might be able to help me with a quick and clean way to do the following based on my example:

I want to;

-search current worksheet in range (column c starting at c3:c13)
-if 'NO' found then mark 'NO' in cell on same row in column D and continue searching down the range in col C.
-if 'YES' found, then search ('some_other_column' in 'a_different_worksheet) for value found in column b (on this worksheet) on the same row. (in example below $37.50)
-if this value ($37.50 or whatever) is found in the different worksheet, then mark 'YES' in cell on same row in column D of THIS worksheet.
-if this value is NOT found in the other worksheet, then mark 'NO' in cell on same row in column D of THIS worksheet.

I hope I am explaining this clearly. Please go easy because while i have a small amount of experience with VBA it has been several years since I have visited this. Any help is most graciously appreciated

<tbody>
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]c[/TD]
[TD="class: xl65, width: 61"]d[/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl65"]Entity[/TD]
[TD="class: xl65"] cost[/TD]
[TD="class: xl65, width: 40"] Paid[/TD]
[TD="class: xl65, width: 61"] Cleared[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl66"]bucket 1[/TD]
[TD="class: xl67, align: right"]$269.00[/TD]
[TD="class: xl66"] No[/TD]
[TD="class: xl66"] No[/TD]

[TD="class: xl66"]4[/TD]
[TD="class: xl66"]bucket 2[/TD]
[TD="class: xl67, align: right"]$37.50[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"] Yes[/TD]

[TD="class: xl66"]5[/TD]
[TD="class: xl66"]bucket 3[/TD]
[TD="class: xl67, align: right"]$100.06[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"] No[/TD]

[TD="class: xl66"]6[/TD]
[TD="class: xl66"]bucket 4[/TD]
[TD="class: xl67, align: right"]$51.71[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]7[/TD]
[TD="class: xl66"]bucket 5[/TD]
[TD="class: xl67, align: right"]$167.19[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]8[/TD]
[TD="class: xl66"]bucket 6[/TD]
[TD="class: xl67, align: right"]$180.00[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]9[/TD]
[TD="class: xl66"]bucket 7[/TD]
[TD="class: xl67, align: right"]$53.75[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]10[/TD]
[TD="class: xl66"]bucket 8[/TD]
[TD="class: xl67, align: right"]$266.00[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]11[/TD]
[TD="class: xl66"]bucket 9[/TD]
[TD="class: xl67, align: right"]$106.00[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]12[/TD]
[TD="class: xl66"]bucket 10[/TD]
[TD="class: xl67, align: right"]$212.34[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]13[/TD]
[TD="class: xl66"]bucket 11[/TD]
[TD="class: xl67, align: right"]$19.95[/TD]
[TD="class: xl66"] Yes[/TD]
[TD="class: xl66"][/TD]

</tbody>

 

[igold]

I would be happy to try and help you, but first you need to provide better information for your requirements (read that as actual sheet names and columns) for the parts I have highlighted in red...

-search current worksheet in range (column c starting at c3:c13)
-if 'NO' found then mark 'NO' in cell on same row in column D and continue searching down the range in col C.
-if 'YES' found, then search ('some_other_column' in 'a_different_worksheet) for value found in column b (on this worksheet) on the same row. (in example below $37.50)
-if this value ($37.50 or whatever) is found in the different worksheet, then mark 'YES' in cell on same row in column D of THIS worksheet.
-if this value is NOT found in the other worksheet, then mark 'NO' in cell on same row in column D of THIS worksheet.

 

[morbenforsen]

sure no problem;

('some_other_column' in 'a_different_worksheet) = let's say 'Sheet2'!D:D
different worksheet = 'Sheet2'
other worksheet = 'Sheet2'

thanks for your assistance!

 

[igold]

Does this get you close to what you are looking for:

Sub test()


    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    Dim lRow As Long, i As Long, dlRow As Long
    Dim sOs As Range
    Dim srch As String
    
    lRow = Cells(Rows.Count, 3).End(xlUp).Row
    dlRow = ws2.Cells(Rows.Count, 4).End(xlUp).Row
    
    For i = 2 To lRow
        If Not Cells(i, 3).Value = "No" Then
            srch = Cells(i, 2).Value
            Set sOs = ws2.Range("D2:D" & dlRow).Find(what:=srch)
            If Not sOs Is Nothing Then
                Cells(i, 4).Value = "Yes"
            Else
                Cells(i, 4).Value = "No"
            End If
            GoTo fnd
        End If
        Cells(i, 4).Value = "No"
fnd:
    Next
    
End Sub

 

[morbenforsen]

This seems to work perfectly in my sample. I will check it tomorrow on my larger workbook to make certain. Thank you so much for your help! Cheers!

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback!