?
Solved

VBA Excel - Find value in another sheet if

Posted on 2013-01-11
5
Medium Priority
?
366 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 400 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 1600 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

765 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