cg_tor;653933 Wrote:
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(U20,V2=0),"ORF "&R2&" to "&W2,
IF(AND(O21,P21),"At "&Q2,
IF(AND(O21,P2=0),"ORF "&L2&" to "&Q2,
IF(AND(O2=0,P2=0),"Awaiting PUD from "&L2,
IF(AND(O21,P21),"At "&Q2,
IF(AND(AG20,AH20),"At "&AI2,
IF(AND(AG20,AH2=0),"ORF "&AD2&" to "&AI2,
IF(AND(AA20,AB20), IF(AND(AA20,AB2=0),"ORF "&X2&" to "&AC2,
IF(AND(U20,V20),"At "&W2,
IF(AND(AM20,AN20),"At "&AO2,
IF(AND(AM20,AN2=0),"ORF "&AJ2&" to "&AO2,"Check"
Thanks in advance
Select a single block of cells which covers the rows that you want
procesing, it doesn't matter whether it has multiple columns, or if it
doesn't include column AQ, the results will still appear in AQ, and run
this macro:
VBA Code:
--------------------
Sub blah()
For Each Cll In Selection.EntireRow.Columns("AQ").Cells
rw = Cll.Row
Select Case True
'IF(AND(U20,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(O21,P21),"At "&Q2,
Case Cells(rw, "O") 1 And Cells(rw, "P") 1
Cll.Value = "At " & Cells(rw, "Q")
'IF(AND(O21,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(O21,P21),"At "&Q2,
'already catered for above - it's a repeat.
'IF(AND(AG20,AH20),"At "&AI2,
Case Cells(rw, "AG") 0 And Cells(rw, "AH") 0
Cll.Value = "At " & Cells(rw, "AI")
'IF(AND(AG20,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(AA20,AB20),
'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(AA20,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(U20,V20),"At "&W2,
Case Cells(rw, "U") 0 And Cells(rw, "V") 0
Cll.Value = "At " & Cells(rw, "W")
'IF(AND(AM20,AN20),"At "&AO2,
Case Cells(rw, "AM") 0 And Cells(rw, "AN") 0
Cll.Value = "At " & Cells(rw, "AO")
'IF(AND(AM20,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
--------------------
This is simply a direct translation of what I think you want, without
any attempt to streamline the logic/code.
--
p45cal
*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=182497
Microsoft Office Help