Solved

VBA Excel - Find value in another sheet if

Posted on 2013-01-11
5
311 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks to both of you

All good now.
0
 
LVL 10

Expert Comment

by:broro183
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now