Wilder1626
asked on
VBA Excel - Find value in another sheet if
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:
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both of you
All good now.
All good now.
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 :-)
hth
Rob
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
hth
Rob
ASKER
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