PAG,
Rather than "hard code" your searches, you could prompt the user with an
InputBox to enter the value being searched for. Following suggested code
prompts user for a numeric value & then will search Col B only €“ if found it
adds the data you require to Col E.

Sub FindValue()
Dim FoundCell As Range
Dim myws As Worksheet
Dim Search As Single
Dim MyTitle As String

MyTitle = "Search Number"
Set myws = Worksheets("Sheet1") '<< change as required

Search = Application.InputBox(prompt:="Enter Number To Find",
Title:=MyTitle, Type:=1)
If Search < 0 Then
Set FoundCell = myws.Columns("B").Find _
(Search, LookIn:=xlValues, LookAt:=xlWhole)

If FoundCell Is Nothing = False Then
FoundCell.Offset(0, 3).Value = "European Trade"
Else
msg = MsgBox("Value " & Search & " Not Found", vbInformation,
MyTitle)
End If
End If
End Sub

You will note that I have referenced a worksheet named €œSheet1€� you will
need to amend this to name of sheet you are searching.

Hope Useful

--
JB


"PAG" wrote:

Hi

Can someone please help me create a macro to search an Excel worksheet(in
column B) for a cell value("42285") and return a statement("European Trade")
in column "E". The data can always change and the rows of data can be up to
3,000 rows.

I have the below code but it needs alot of work.

Dim rowCount As Integer
rowCount = ActiveSheet.UsedRange.Rows.Count

Dim i As Integer

For i = 1 To rowCount
Range("B" & i).Select
If ActiveCell.Value = "42285" Then
GoTo loopAgain
Else

ActiveCell.Offset(0, 4) = Sheets("Sheet1").ActiveCell.Offset(6, 4)


' write code to do whatever(return your activecell.offset value)

End If

loopAgain:
Next i

End Sub

Thank You
Peter