Search cell in rows and copy paste

**nicolaforni** · 07-25-2011, 01:14 PM

Hi,

I have following data in Sheet2 in column A to C (starting A1):

Apples France 100
Pears Italy 50
Bananas Guatemala 20
Kiwis New Zealand 60
Grapes Spain 10
Figs Italy 80
Melon Italy 20

I would like to do a search through column B and if Italy is found then if number is higher than 50 (in column C) I would like the text in column A to be copied in Sheet1 starting from Cell A1 going down.

Thanks,
Nix

**davegugg** · 07-25-2011, 03:05 PM

Sub nicolaforni()

    Dim i As Integer
    
    With Sheets("Sheet2")
        For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
            If .Cells(i, 2).Value = "Italy" Then
                If .Cells(i, 3).Value > 50 Then Sheets("Sheet1").Cells(Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Value = .Cells(i, 1).Value
            End If
        Next i
    End With

End Sub

**nicolaforni** · 07-26-2011, 11:34 AM

Great.
How would the code change if instead of looking for "Italy" I wanted to look for what is in cell E1 of Sheet1?
Basically this way I could change search by typing different country in E1 (eg. France, Spain etc.) instead of amending code each time.

Thanks,
NIc

**davegugg** · 07-26-2011, 02:03 PM

Sub nicolaforni()

    Dim i As Integer
    
    With Sheets("Sheet2")
        For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
            If .Cells(i, 2).Value = Sheets("Sheet1").Cells(1,"E").Value Then
                If .Cells(i, 3).Value > 50 Then Sheets("Sheet1").Cells(Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Value = .Cells(i, 1).Value
            End If
        Next i
    End With

End Sub