Need help to convert formula

[sylink]

Am new into vba. I have this task which is a little difficult for me. i
need to translate this excel formula into program:
=IF(C2="FSOP","P","S").

Iwas on column "I". and developing a macro for an undetermined no of
rows, i got this but couldn't achieve the objective.



Dim z As Integer
Dim str As String
Dim tt As String
Dim cy As String
str = "A1"
Range(str).Select
z = 2

Do While str <> " "
cy = "C" & z
Range(cy).Select

If cy = "FSOP" Then
tt = "i" & z
Range(tt).Select
tt = "P"
z = z + 1
str = "A" & z
Else
tt = "i" & z
Range(tt).Select
tt = "S"
str = "A" & z
z = z + 1
End If
Loop

 

[Norie]

Does this work?

Code:
--------------------

Dim rng As Range

Set rng = Range("C2")
Do While rng.Value <> ""

If rng.Value = "FSOP" Then
rng.Offset(0, 7) = "P"
Else
rng.Offset(0, 7) = "S"
End If
Set rng = rng.Offset(1, 0)
Loop

 

[David McRitchie]

Perhaps it would be better to say that you want the macro
to run as long as there is a value in Column A starting from
cell A1 because that is how your macro was coded.

Your problem is that you set z at then end and used a
different string at the top to keep track of the row.

It is bad practice to change the selection within a macro
except for upon exit -- if the purpose was to change the selection.

You can write your macro to work from a selection of Column A
and stop as soon as it tests for a null string, or as EMPTY.
Since it is only working from Column A you do not need to
select anything before or during the macro.

Sub FSOP_macro()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Range("A:A")
If cell.row = 1 Then 'you are skipping checing of row 1
ElseIf cell = "" Then GoTo done
ElseIf UCase(cell.Offset(0, 2).Value) = "FSOP" Then
cell.Offset(0, 8) = "P"
Else
cell.Offset(0, 8) = "S"
End If
Next cell
done:

Some helpful information in
http://www.mvps.org/dmcritchie/excel/slowresp.htm#slowmacros
http://www.mvps.org/dmcritchie/excel/proper.htm

And after you have your data in order you might want to use
a Change Event macro for future changes to take place
automatically if the value changed is in Column C
and there is a value in column A
http://www.mvps.org/dmcritchie/excel/event.htm

FWIW, in your macro you treating str with a value of "A1"
the same as RANGE("A1")
since you set str to A1 it cannot be equal to "" likewise
for other tests.

Sub sylink01()
Dim z As Long 'do not use Integer when referencing rows
z = 2
Do While Range("A" & z) <> ""
If Range("C" & z) = "FSOP" Then
Range("I" & z) = "P"
Else
Range("I" & z) = "S"
End If
z = z + 1
Loop
End Sub

 

[sylink]

I am deeply grateful for the prompt response. I shall try out
immediately.
sylink