Solved

VBA Excel - Find value in another sheet if

Posted on 2013-01-11
5
345 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 19

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

749 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