Converting IF Formul To VBA

[LoveCandle]

Hi everybody,

I have this IF Formula and I want to convert it to VBA code to get the
same purpose,



Code:
--------------------
IF((AND(S4>3,V4>0,U4="A3")),"A1",(IF((OR(W4<AM4,$AE$3<12,AA4>9)),"a1",IF((AND(S4>10,AA4>1)),"A1",IF(S4>3,"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.

 

[Rocky McKinley]

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

 

[Guest]

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]

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,

 

[Guest]

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

 

[Bob Phillips]

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(S5>3,V5>0,U5="A3")),"A1",(IF((OR(W5<AM5,$AE$3<12,AA5>9)),"a1",IF((AN
D(S5>10,AA5>1)),"A1",IF(S5>3,"A2","")))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi everybody,

I have this IF Formula and I want to convert it to VBA code to get the
same purpose,



Code: