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

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.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=182497

Microsoft Office Help

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
--
HTH,

Barb Reinhardt



"cg_tor" 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

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