Case Of Statement
 

Hello. I would like a case of statement that will:
- look at every used cell in column A
- depending on it's content return the number in column C as either positive
or negative in column F.

Would really appreciate any help.

Thanks,
Hussein

Case Of Statement
 

Hi Hussein,

As suggested in your previous post, try:
'================
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 would like a case of statement that will:
- look at every used cell in column A
- depending on it's content return the number in column C as either
positive
or negative in column F.

Would really appreciate any help.

Thanks,
Hussein

Case Of Statement
 

Thank you very much Norman, it worked perfectly.


--
H


"Norman Jones" wrote:

Hi Hussein,

As suggested in your previous post, try:
'================
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 would like a case of statement that will:
- look at every used cell in column A
- depending on it's content return the number in column C as either
positive
or negative in column F.

Would really appreciate any help.

Thanks,
Hussein