Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to convert formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to convert formula
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 -------------------- -- Norie ------------------------------------------------------------------------ Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362 View this thread: http://www.excelforum.com/showthread...hreadid=379725 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to convert formula
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...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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "sylink" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to convert formula
I am deeply grateful for the prompt response. I shall try out
immediately. sylink |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert vlookup formula to link formula | Excel Worksheet Functions | |||
How to convert a static formula to dynamic formula ? | Excel Worksheet Functions | |||
Excell convert formula row to formula column | Excel Worksheet Functions | |||
how to convert a formula into text in order to display the formula | Excel Discussion (Misc queries) | |||
Convert Normal formula to array formula | Excel Programming |