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

1	a	b	c	d
2	Entity	cost	Paid	Cleared
3	bucket 1	$269.00	No	No
4	bucket 2	$37.50	Yes	Yes
5	bucket 3	$100.06	Yes	No
6	bucket 4	$51.71	Yes
7	bucket 5	$167.19	Yes
8	bucket 6	$180.00	Yes
9	bucket 7	$53.75	Yes
10	bucket 8	$266.00	Yes
11	bucket 9	$106.00	Yes
12	bucket 10	$212.34	Yes
13	bucket 11	$19.95	Yes

<tbody>
</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!