| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting IF Formul To VBA
Hi everybody, I have this IF Formula and I want to convert it to VBA code to get the same purpose, Code: -------------------- IF((AND(S43,V40,U4="A3")),"A1",(IF((OR(W4<AM4,$A E$3<12,AA49)),"a1",IF((AND(S410,AA41)),"A1",IF( S43,"A2",""))))) -------------------- Actually, I tried this code but it didn't work properly with me I don't know the error exactly. Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) If (Range("S" & Target.Row).Value 3 And Range("V" & Target.Row).Value 0 And Range("U" & Target.Row).Value = "A3") _ Or (Range("W" & Target.Row).Value < Range("AM" & Target.Row).Value Or Range("AE" & Target.Row).Value < 12 Or Range("AA" & Target.Row).Value 9) _ Or (Range("S" & Target.Row).Value 10 And Range("AA" & Target.Row).Value 1) Then Range("B" & Target.Row).Value = "A1" ElseIf Range("S" & Target.Row).Value 3 Then Range("B" & Target.Row).Value = "A2" Else: Range("B" & Target.Row).Value = "" Exit Sub End If End Sub -------------------- So, I would like you please to help me to get the formuls working in range B3:B102 only. Thanks for all. -- LoveCandle ------------------------------------------------------------------------ LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612 View this thread: http://www.excelforum.com/showthread...hreadid=486751 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Converting IF Formul To VBA
I'm not sure what your trying to achieve but this should convert the
formula, where you wish to place the result I'm not sure? To place the result in the same cell that was changed... Target.Value = Ans instead of ... MsgBox Ans Private Sub Worksheet_Change(ByVal Target As Range) Dim Ans Application.EnableEvents = False If Not Intersect(Target, Range("B3:B102")) Is Nothing And Target.Count = 1 Then If Range("S4") 3 And Range("V4") 0 And Range("U4") = "A3" Then Ans = "A1" ElseIf Range("W4") < Range("AM4") Or Range("AE3") < 12 Or Range("AA4") 9 Then Ans = "A1" ElseIf Range("s4") 10 And Range("AA4") 1 Then Ans = "A1" ElseIf Range("S4") 3 Then Ans = "A2" Else Ans = "" End If End If MsgBox Ans Application.EnableEvents = True End Sub -- Regards, Rocky McKinley "LoveCandle" wrote in message ... Hi everybody, I have this IF Formula and I want to convert it to VBA code to get the same purpose, Code: -------------------- IF((AND(S43,V40,U4="A3")),"A1",(IF((OR(W4<AM4,$A E$3<12,AA49)),"a1",IF((AND(S410,AA41)),"A1",IF( S43,"A2",""))))) -------------------- Actually, I tried this code but it didn't work properly with me I don't know the error exactly. Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) If (Range("S" & Target.Row).Value 3 And Range("V" & Target.Row).Value 0 And Range("U" & Target.Row).Value = "A3") _ Or (Range("W" & Target.Row).Value < Range("AM" & Target.Row).Value Or Range("AE" & Target.Row).Value < 12 Or Range("AA" & Target.Row).Value 9) _ Or (Range("S" & Target.Row).Value 10 And Range("AA" & Target.Row).Value 1) Then Range("B" & Target.Row).Value = "A1" ElseIf Range("S" & Target.Row).Value 3 Then Range("B" & Target.Row).Value = "A2" Else: Range("B" & Target.Row).Value = "" Exit Sub End If End Sub -------------------- So, I would like you please to help me to get the formuls working in range B3:B102 only. Thanks for all. -- LoveCandle ------------------------------------------------------------------------ LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612 View this thread: http://www.excelforum.com/showthread...hreadid=486751 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Converting IF Formul To VBA
For what it's worth, the following is a direct translation of your formula
into VBA pseudo code. This is just intended to depict the logic structure only and does not work. Rocky's code uses an If/ElseIf/End If construct that has no worksheet function equivalent and is more efficient. I concur with the logic structure of Rocky's code. I think a UDF is the way to go here that receives the cell's row and or column as agruments. Note that with the exception of the "$AE$3" range reference, all other range references are relative. Without knowing the source cell of the formula I don't think this can be resolved. 'IF And(S4 3, V4 0, U4 = "A3") Then ' Cell Value = "A1" 'Else ' If OR(W4 < AM4, $AE$3 < 12, AA4 9) Then ' Cell Value = "a1" ' Else ' If AND(S4 10, AA4 1) Then ' Cell Value = "A1" ' Else ' If S4 3 Then ' Cell Value = "A2" ' Else ' Cell Value = "" ' End If ' End If ' End If 'End If Regards, Greg "LoveCandle" wrote: Hi everybody, I have this IF Formula and I want to convert it to VBA code to get the same purpose, Code: -------------------- IF((AND(S43,V40,U4="A3")),"A1",(IF((OR(W4<AM4,$A E$3<12,AA49)),"a1",IF((AND(S410,AA41)),"A1",IF( S43,"A2",""))))) -------------------- Actually, I tried this code but it didn't work properly with me I don't know the error exactly. Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) If (Range("S" & Target.Row).Value 3 And Range("V" & Target.Row).Value 0 And Range("U" & Target.Row).Value = "A3") _ Or (Range("W" & Target.Row).Value < Range("AM" & Target.Row).Value Or Range("AE" & Target.Row).Value < 12 Or Range("AA" & Target.Row).Value 9) _ Or (Range("S" & Target.Row).Value 10 And Range("AA" & Target.Row).Value 1) Then Range("B" & Target.Row).Value = "A1" ElseIf Range("S" & Target.Row).Value 3 Then Range("B" & Target.Row).Value = "A2" Else: Range("B" & Target.Row).Value = "" Exit Sub End If End Sub -------------------- So, I would like you please to help me to get the formuls working in range B3:B102 only. Thanks for all. -- LoveCandle ------------------------------------------------------------------------ LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612 View this thread: http://www.excelforum.com/showthread...hreadid=486751 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Converting IF Formul To VBA
Hi everybody, The code delivered by Mr. Rocky didn't work with me .. I don't know why??? More over I think it work with one row only .. while I want it to work with 100 rows. I would like u please Mr. Rocky to attach a file contains the code. Thank you, -- LoveCandle ------------------------------------------------------------------------ LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612 View this thread: http://www.excelforum.com/showthread...hreadid=486751 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Converting IF Formul To VBA
Is this what you want? I had to do some guessing because your formula has
relative references and you didn't mention the source cell. Sub XYZ() Dim rng As Range, c As Range Dim i As Integer Set rng = Range("B3:B102") i = 2 For Each c In rng.Cells i = i + 1 If Range("S" & i) 3 And Range("V" & i) 0 And _ Range("U" & i) = "A3" Then c = "A1" ElseIf Range("W" & i) < Range("AM" & i) Or _ Range("AE3") < 12 Or Range("AA" & i) 9 Then c = "A1" ElseIf Range("S" & i) 10 And Range("AA" & i) 1 Then c = "A1" ElseIf Range("S" & i) 3 Then c = "A2" Else c = "" End If Next End Sub Regards, Greg "LoveCandle" wrote: Hi everybody, The code delivered by Mr. Rocky didn't work with me .. I don't know why??? More over I think it work with one row only .. while I want it to work with 100 rows. I would like u please Mr. Rocky to attach a file contains the code. Thank you, -- LoveCandle ------------------------------------------------------------------------ LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612 View this thread: http://www.excelforum.com/showthread...hreadid=486751 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Converting IF Formul To VBA
Why do you want to change it to VBA?
When you say apply it to B3:B102, how does each of those cells relate to that formula. For example, if that formula is in B3, the formula in B4 would be IF((AND(S53,V50,U5="A3")),"A1",(IF((OR(W5<AM5,$A E$3<12,AA59)),"a1",IF((AN D(S510,AA51)),"A1",IF(S53,"A2",""))))) -- HTH RP (remove nothere from the email address if mailing direct) "LoveCandle" wrote in message ... Hi everybody, I have this IF Formula and I want to convert it to VBA code to get the same purpose, Code: -------------------- IF((AND(S43,V40,U4="A3")),"A1",(IF((OR(W4<AM4,$A E$3<12,AA49)),"a1",IF((AN D(S410,AA41)),"A1",IF(S43,"A2",""))))) -------------------- Actually, I tried this code but it didn't work properly with me I don't know the error exactly. Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) If (Range("S" & Target.Row).Value 3 And Range("V" & Target.Row).Value 0 And Range("U" & Target.Row).Value = "A3") _ Or (Range("W" & Target.Row).Value < Range("AM" & Target.Row).Value Or Range("AE" & Target.Row).Value < 12 Or Range("AA" & Target.Row).Value 9) _ Or (Range("S" & Target.Row).Value 10 And Range("AA" & Target.Row).Value 1) Then Range("B" & Target.Row).Value = "A1" ElseIf Range("S" & Target.Row).Value 3 Then Range("B" & Target.Row).Value = "A2" Else: Range("B" & Target.Row).Value = "" Exit Sub End If End Sub -------------------- So, I would like you please to help me to get the formuls working in range B3:B102 only. Thanks for all. -- LoveCandle ------------------------------------------------------------------------ LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612 View this thread: http://www.excelforum.com/showthread...hreadid=486751 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to not get '0' or '#DIV/0!' in a cell with a percentage formul | Excel Worksheet Functions | |||
| formul | Excel Discussion (Misc queries) | |||
| creating a formul | Excel Discussion (Misc queries) | |||
| IF Formul | Excel Worksheet Functions | |||
| formul that add 1.5 day, every end of month | Excel Discussion (Misc queries) | |||
![LoveCandle[_12_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
