Search Cells

[hfazal]

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

[Norman Jones]

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" <hfazal@discussions.microsoft.com> wrote in message
news:36A5549D-B266-4082-AE6C-5687FB0A9B6C@microsoft.com...
> 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