Solved

VBA Excel - Find value in another sheet if

Posted on 2013-01-11
5
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

726 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