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

morbenforsen

New Member
Joined
Jul 4, 2015
Messages
20
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


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

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 4, 2015
Messages
20
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Does this get you close to what you are looking for:

Code:
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

New Member
Joined
Jul 4, 2015
Messages
20
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You're welcome. I was happy to help. Thanks for the feedback!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,814
Messages
5,525,050
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top