Check the value and return the result Closed

Question

Hello,
Please help me out.

I have followig values in "Sheet1",
Eg:
          A
  1   Apple          
  2   Orange           
  3   Lemon           

In "Sheet2",Payments are made for those values present in "Sheet1" and for others.
Eg:
        A                B
1   Apple            $20
2   Tiger             $50
3   Lemon           $14
4   Apple            $36 
5   Giraf              $54

If value present in "Sheet1" matches in "Sheet2", then the entire row should be retrieved and displayed in "Sheet3". Others should be ignored.

Eg:
        A                B
1   Apple            $20
2   Lemon           $14
3   Apple            $36 

Thanks !

venkat1926 · Answer

try

Sub test()
Dim rng As Range, c As Range, x As String, cfind As Range
With Worksheets("sheet2")
Set rng = Range(.Range("A1"), .Range("A1").End(xlDown))
For Each c In rng
x = c.Value
Set cfind = Worksheets("sheet1").UsedRange.Cells.Find _
    (what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
c.EntireRow.Copy
With Worksheets("sheet3")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End If
Next c
End With
Application.CutCopyMode = False
End Sub

Check the value and return the result

1 response

Similar discussions

Subscribe To Our Newsletter!