Solved

VBA Excel - Find value in another sheet if

Posted on 2013-01-11
5
333 Views
Last Modified: 2013-01-13
Hello all

I'm trying to build a excel macro that would search each value from Sheet1 if it exist in column B from sheet2.

If it exist, it would say YES in column E if sheet1 row.

How can i do that?

Thanks again for your help.

What i have for now:
Private Sub CommandButton1_Click()
Dim lngRow As Long
lngRow = 2
Do While Sheets("Location extract").Range("A" & lngRow) <> ""
If Sheets("Location extract").Range("A" & lngRow) = Sheets("Rates extract").Range("C" & lngRow) Then
Sheets("Location extract").Range("E" & lngRow) = "YES"
Else
Sheets("Location extract").Range("E" & lngRow) = "No"
End If
lngRow = lngRow + 1
Loop
End Sub

Open in new window

0
Comment
Question by:Wilder1626
5 Comments
 
LVL 18

Assisted Solution

by:Richard Daneke
Richard Daneke earned 100 total points
ID: 38769189
You can use a formula to do this and do not need a macro.

Research MATCH  function.

For example,   where the Column E on the first worksheet enter;

 =IF(IFERROR(Match(A4,Sheet2!B1:B600,0),"NO")="NO","","Yes")

Match returns the row where there is a match
IFERROR returns a "NO" when there is "NO" match
IF statement checks for "NO", and shows nothing ("") when no match and "Yes" on matches.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38769229
hello

but if i really want to use a macro cause at the end, the macro will delete the ones at no match.

How can i do that?

Thanks
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 400 total points
ID: 38769426
Try this macro. I have not tested it. If it does not work the upload a sample file for testing.
Private Sub CommandButton1_Click()
    Dim lngRow As Long
    lngRow = 2
    For Each Lcel In Sheets("Location extract").Range("A2:A" & Sheets("Location extract").Range("A2").End(xlDown).Row)
        matchfound = False
        For Each Rcel In Sheets("Location extract").Range("C2:C" & Sheets("Location extract").Range("C2").End(xlDown).Row)
            If Lcel = Rcel Then
                matchfound = True
                Exit For
            End If
        Next Rcel
        If matchfound Then
            Lcel.Offset(0, 4) = "YES"
        Else
            Lcel.Offset(0, 4) = "No"
        End If
    Next Lcel
End Sub

Open in new window

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38769896
Thanks to both of you

All good now.
0
 
LVL 10

Expert Comment

by:broro183
ID: 38771691
Hi,

I wrote the following macro  yesterday but didn't get a chance to post it. It is a combination of formulae & macro. This is not for points esp as they are already assigned, but since I wrote the macro I thought I may as well share it :-)

Sub CheckFilterAndDelete()
Dim Loctn_LRow As Long
Dim Rates_LRow As Long
Dim Loctn_Ws As Worksheet
Dim Rates_Ws As Worksheet

    With ThisWorkbook
        Set Loctn_Ws = .Worksheets("Location Extract")
        Set Rates_Ws = .Worksheets("Rates Extract")
    End With

    Rates_LRow = Rates_Ws.Range("c2").End(xlDown).Row

    With Loctn_Ws
        Loctn_LRow = .Range("a2").End(xlDown).Row
        .Range("E2").Resize(Loctn_LRow - 1).FormulaR1C1 = "=MATCH(RC[-4],'Rates extract'!R2c3:R" & Rates_LRow & "C3,0)"

        If .AutoFilterMode Then
            .ShowAllData
        Else
            .UsedRange.AutoFilter
        End If

        With .AutoFilter.Range
            .AutoFilter Field:=5, Criteria1:="<>#N/A"
            'or should it be  the opposite of the above (see next line)?
            '.AutoFilter Field:=5, Criteria1:="#N/A"

            Stop
            .Offset(1, 0).Resize(.Rows.Count - 1, 1).EntireRow.SpecialCells(xlCellTypeVisible).Select
            MsgBox "When testing, check the selection before running the next line of code. When the testing is finished, the below & above lines of code can be deleted."
            Stop

            .Offset(1, 0).Resize(.Rows.Count - 1, 1).EntireRow.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
            .AutoFilter Field:=5
        End With
    End With

    Set Loctn_Ws = Nothing
    Set Rates_Ws = Nothing

End Sub

Open in new window


hth
Rob
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question