Hi H,

Try something like:

'================
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rcell As Range
Dim LRow As Long
Const sStr As String = "Fox" '<<===== CHANGE

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

LRow = Cells(Rows.Count, "A").End(xlUp).Row

Set rng = SH.Range("A1:A" & LRow)

For Each rcell In rng.Cells
With rcell
If InStr(1, .Value, sStr, vbTextCompare) Then
.Offset(0, 5) = .Offset(0, 2)
Else
.Offset(0, 5) = .Offset(0, 2) * -1
End If
End With
Next rcell

End Sub
'<<================


---
Regards,
Norman



"hfazal" wrote in message
...
hello,

I am writing a macro in which I would like to search column A (which is a
long string) and see if it contains a word in there somewhere. If it does
then I would like column F to return the number in column C as a positive
number and if it does not then return the number in colum C as a negative
number.

thanks,
H