Nested Ifs VBA, How to
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 |
Nested Ifs VBA, How to
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 |
Nested Ifs VBA, How to
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 |
Nested Ifs VBA, How to
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 |
All times are GMT +1. The time now is 10:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com