Nested Ifs VBA, How to

[cg_tor]

Hello, Would anyone show me how to put the ifs statements below in VBA. I
would like the results to show in column AQ:


IF(AND(U2>0,V2=0),"ORF "&R2&" to "&W2,

IF(AND(O2>1,P2>1),"At "&Q2,

IF(AND(O2>1,P2=0),"ORF "&L2&" to "&Q2,

IF(AND(O2=0,P2=0),"Awaiting PUD from "&L2,

IF(AND(O2>1,P2>1),"At "&Q2,

IF(AND(AG2>0,AH2>0),"At "&AI2,

IF(AND(AG2>0,AH2=0),"ORF "&AD2&" to "&AI2,

IF(AND(AA2>0,AB2>0), IF(AND(AA2>0,AB2=0),"ORF "&X2&" to "&AC2,

IF(AND(U2>0,V2>0),"At "&W2,

IF(AND(AM2>0,AN2>0),"At "&AO2,

IF(AND(AM2>0,AN2=0),"ORF "&AJ2&" to "&AO2,"Check"


Thanks in advance

 

[joel]

Can yo uexplain what this function is really doing. I think there are
errors in this formula and changing the function to VBA won't correct
the errors. the dependencies between the different input don't seem
correct.

Looking at the formula my first question is "Do you actually have
results less than 0?" If yoi udon't then you won't get most of the
conditions and the formula will simplify a lot.

Can you post a sample of the actual data? Seeing the data will give me
a chance to make a very simple formula that will be easier to maintain.

 

[Barb Reinhardt]

One way.

Dim aWS as excel.worksheet
set aWS = Acitvesheet

With aWS.Range("AQ2")
IF aws.range(U2).value = 0 and _
aws.range("V2").value = 0 then
.value = "ORF "&aws.range("R2").value &" to "&aws.range("W2"),value
elseif aws.range(U2).value = 0 and _ 'Repeated the above
aws.range("V2").value = 0 then
.value = "ORF "&aws.range("R2").value &" to "&aws.range("W2"),value
end if
end if


end with

 

[p45cal]

Hello, Would anyone show me how to put the ifs statements below in VBA
I

would like the results to show in column AQ:


IF(AND(U2>0,V2=0),"ORF "&R2&" to "&W2,

IF(AND(O2>1,P2>1),"At "&Q2,

IF(AND(O2>1,P2=0),"ORF "&L2&" to "&Q2,

IF(AND(O2=0,P2=0),"Awaiting PUD from "&L2,

IF(AND(O2>1,P2>1),"At "&Q2,

IF(AND(AG2>0,AH2>0),"At "&AI2,

IF(AND(AG2>0,AH2=0),"ORF "&AD2&" to "&AI2,

IF(AND(AA2>0,AB2>0), IF(AND(AA2>0,AB2=0),"ORF "&X2&" to "&AC2,

IF(AND(U2>0,V2>0),"At "&W2,

IF(AND(AM2>0,AN2>0),"At "&AO2,

IF(AND(AM2>0,AN2=0),"ORF "&AJ2&" to "&AO2,"Check"


Thanks in advance

Select a single block of cells which covers the rows that you wan
procesing, it doesn't matter whether it has multiple columns, or if i
doesn't include column AQ, the results will still appear in AQ, and ru
this macro:


VBA Code:
--------------------


Sub blah()
For Each Cll In Selection.EntireRow.Columns("AQ").Cells
rw = Cll.Row
Select Case True
'IF(AND(U2>0,V2=0),"ORF "&R2&" to "&W2,
Case Cells(rw, "U") > 0 And Cells(rw, "U") = 0
Cll.Value = "ORF " & Cells(rw, "R") & " to " & Cells(rw, "W")

'IF(AND(O2>1,P2>1),"At "&Q2,
Case Cells(rw, "O") > 1 And Cells(rw, "P") > 1
Cll.Value = "At " & Cells(rw, "Q")

'IF(AND(O2>1,P2=0),"ORF "&L2&" to "&Q2,
Case Cells(rw, "O") > 1 And Cells(rw, "P") = 0
Cll.Value = "ORF " & Cells(rw, "L") & " to " & Cells(rw, "Q")

'IF(AND(O2=0,P2=0),"Awaiting PUD from "&L2,
Case Cells(rw, "O") = 0 And Cells(rw, "P") = 0
Cll.Value = "Awaiting PUD from " & Cells(rw, "L")

'IF(AND(O2>1,P2>1),"At "&Q2,
'already catered for above - it's a repeat.

'IF(AND(AG2>0,AH2>0),"At "&AI2,
Case Cells(rw, "AG") > 0 And Cells(rw, "AH") > 0
Cll.Value = "At " & Cells(rw, "AI")

'IF(AND(AG2>0,AH2=0),"ORF "&AD2&" to "&AI2,
Case Cells(rw, "AG") > 0 And Cells(rw, "AH") = 0
Cll.Value = "ORF " & Cells(rw, "AD") & " to " & Cells(rw, "AI")

'IF(AND(AA2>0,AB2>0),
'this was on the same line as the if statement below - they're mutually exclusive so it makes no sense.
'so I've commented out possible code:
'Case Cells(rw, "AA") > 0 And Cells(rw, "AB") > 0
'cll.Value = ????

'IF(AND(AA2>0,AB2=0),"ORF "&X2&" to "&AC2,
Case Cells(rw, "AA") > 0 And Cells(rw, "AB") = 0
Cll.Value = "ORF " & Cells(rw, "X") & " to " & Cells(rw, "AC")

'IF(AND(U2>0,V2>0),"At "&W2,
Case Cells(rw, "U") > 0 And Cells(rw, "V") > 0
Cll.Value = "At " & Cells(rw, "W")

'IF(AND(AM2>0,AN2>0),"At "&AO2,
Case Cells(rw, "AM") > 0 And Cells(rw, "AN") > 0
Cll.Value = "At " & Cells(rw, "AO")

'IF(AND(AM2>0,AN2=0),"ORF "&AJ2&" to "&AO2,
Case Cells(rw, "AM") > 0 And Cells(rw, "AN") = 0
Cll.Value = "ORF " & Cells(rw, "AJ") & " to " & Cells(rw, "AO")

'"Check"
Case Else
Cll.Value = "Check"
End Select
Next Cll
End Sub