| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to VBA function (if and lookup)
I am trying to turn this formula into a VBA function (this formula works
fine, but I have some other IF's I need to add that will take it over the limit of 7): =IF(R44="nc","",IF(R44="jcn","",IF(R44="wce","",IF (R44="cnd","",IF(R44="no spare",(LOOKUP(Q44,WUC!A:A,WUC!D:D)),IF(R44="spare ",(LOOKUP(Q44,WUC!A:A,WUC!D:D)))))))) So far I have this: Function Narrative(Reason As Range, Alias As Range) If Reason = "nc" Then Narrative = "" ElseIf Reason = "jcn" Then Narrative = "" ElseIf Reason = "wce" Then Narrative = "" ElseIf Reason = "cnd" Then Narrative = "" ElseIf Reason = "rfs" Then Narrative = "" Else After the else I want the Lookup part of the formula - (LOOKUP(Q44,WUC!A:A,WUC!D:D), but I can't get it to work. The alias as defined above is what I want the Lookup formula to look up in the other sheet. Any suggestions will be greatly appreciated, I'm very new to VBA code. Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to VBA function (if and lookup)
Hi Brihyanna,
The IFs you are using in the worksheet are different to the ones you are using in VBA in that VBA has its own IF function. It works in VBA because it is an available function in VBA. However, the LOOKUP function is a funtion that is only available to the worksheet. If you want to use a worksheet function within VBA then you have to use something like: myVal = WorksheetFucntion.Lookup(arg1, arg2...) Hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "brihyanna" wrote: I am trying to turn this formula into a VBA function (this formula works fine, but I have some other IF's I need to add that will take it over the limit of 7): =IF(R44="nc","",IF(R44="jcn","",IF(R44="wce","",IF (R44="cnd","",IF(R44="no spare",(LOOKUP(Q44,WUC!A:A,WUC!D:D)),IF(R44="spare ",(LOOKUP(Q44,WUC!A:A,WUC!D:D)))))))) So far I have this: Function Narrative(Reason As Range, Alias As Range) If Reason = "nc" Then Narrative = "" ElseIf Reason = "jcn" Then Narrative = "" ElseIf Reason = "wce" Then Narrative = "" ElseIf Reason = "cnd" Then Narrative = "" ElseIf Reason = "rfs" Then Narrative = "" Else After the else I want the Lookup part of the formula - (LOOKUP(Q44,WUC!A:A,WUC!D:D), but I can't get it to work. The alias as defined above is what I want the Lookup formula to look up in the other sheet. Any suggestions will be greatly appreciated, I'm very new to VBA code. Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to VBA function (if and lookup)
You dont really need VBA, you can use something like this as a formula
=IF(OR(R44="nc",R44="jcn",R44="wce",R44="cnd"),"", IF(OR(R44="no spare",R44="spare"),LOOKUP(Q44,WUC!A:A,WUC!D:D),"? ???")) But if you want to use VBA then something like this (although its not quite the same as your Excel formula?) Function Narrative(Reason as range,Alias as range,LookupRange as range,col_Index_Num as variant) Select Case Reason.Value Case "nc","jcn","wce","cnd","rfs" Narrative="" Case else Narrative=Application.VLOOKUP(Alias.Value,LookupRa nge,col_Index_Num,False) end select end function Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "brihyanna" wrote in message ... I am trying to turn this formula into a VBA function (this formula works fine, but I have some other IF's I need to add that will take it over the limit of 7): =IF(R44="nc","",IF(R44="jcn","",IF(R44="wce","",IF (R44="cnd","",IF(R44="no spare",(LOOKUP(Q44,WUC!A:A,WUC!D:D)),IF(R44="spare ",(LOOKUP(Q44,WUC!A:A,WUC!D:D)))))))) So far I have this: Function Narrative(Reason As Range, Alias As Range) If Reason = "nc" Then Narrative = "" ElseIf Reason = "jcn" Then Narrative = "" ElseIf Reason = "wce" Then Narrative = "" ElseIf Reason = "cnd" Then Narrative = "" ElseIf Reason = "rfs" Then Narrative = "" Else After the else I want the Lookup part of the formula - (LOOKUP(Q44,WUC!A:A,WUC!D:D), but I can't get it to work. The alias as defined above is what I want the Lookup formula to look up in the other sheet. Any suggestions will be greatly appreciated, I'm very new to VBA code. Thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to VBA function (if and lookup)
a = WorksheetFunction.Lookup(Range("Q44"), _
Sheets("WUC").Columns("A:A"), Sheets("WUC").Columns("D:D")) "brihyanna" wrote: I am trying to turn this formula into a VBA function (this formula works fine, but I have some other IF's I need to add that will take it over the limit of 7): =IF(R44="nc","",IF(R44="jcn","",IF(R44="wce","",IF (R44="cnd","",IF(R44="no spare",(LOOKUP(Q44,WUC!A:A,WUC!D:D)),IF(R44="spare ",(LOOKUP(Q44,WUC!A:A,WUC!D:D)))))))) So far I have this: Function Narrative(Reason As Range, Alias As Range) If Reason = "nc" Then Narrative = "" ElseIf Reason = "jcn" Then Narrative = "" ElseIf Reason = "wce" Then Narrative = "" ElseIf Reason = "cnd" Then Narrative = "" ElseIf Reason = "rfs" Then Narrative = "" Else After the else I want the Lookup part of the formula - (LOOKUP(Q44,WUC!A:A,WUC!D:D), but I can't get it to work. The alias as defined above is what I want the Lookup formula to look up in the other sheet. Any suggestions will be greatly appreciated, I'm very new to VBA code. Thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to VBA function (if and lookup)
Try this instead:
=IF(OR(A14={"nc","jnc","wce","cnd", "rfs"}),"",IF(OR(R44={"spare","no spare"}),LOOKUP(Q44,WUC!A:A,WUC!D:D))) Greg "brihyanna" wrote: I am trying to turn this formula into a VBA function (this formula works fine, but I have some other IF's I need to add that will take it over the limit of 7): =IF(R44="nc","",IF(R44="jcn","",IF(R44="wce","",IF (R44="cnd","",IF(R44="no spare",(LOOKUP(Q44,WUC!A:A,WUC!D:D)),IF(R44="spare ",(LOOKUP(Q44,WUC!A:A,WUC!D:D)))))))) So far I have this: Function Narrative(Reason As Range, Alias As Range) If Reason = "nc" Then Narrative = "" ElseIf Reason = "jcn" Then Narrative = "" ElseIf Reason = "wce" Then Narrative = "" ElseIf Reason = "cnd" Then Narrative = "" ElseIf Reason = "rfs" Then Narrative = "" Else After the else I want the Lookup part of the formula - (LOOKUP(Q44,WUC!A:A,WUC!D:D), but I can't get it to work. The alias as defined above is what I want the Lookup formula to look up in the other sheet. Any suggestions will be greatly appreciated, I'm very new to VBA code. Thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to VBA function (if and lookup)
Change A14 to R44. Testing artifact.
Greg "Greg Wilson" wrote: Try this instead: =IF(OR(A14={"nc","jnc","wce","cnd", "rfs"}),"",IF(OR(R44={"spare","no spare"}),LOOKUP(Q44,WUC!A:A,WUC!D:D))) Greg "brihyanna" wrote: I am trying to turn this formula into a VBA function (this formula works fine, but I have some other IF's I need to add that will take it over the limit of 7): =IF(R44="nc","",IF(R44="jcn","",IF(R44="wce","",IF (R44="cnd","",IF(R44="no spare",(LOOKUP(Q44,WUC!A:A,WUC!D:D)),IF(R44="spare ",(LOOKUP(Q44,WUC!A:A,WUC!D:D)))))))) So far I have this: Function Narrative(Reason As Range, Alias As Range) If Reason = "nc" Then Narrative = "" ElseIf Reason = "jcn" Then Narrative = "" ElseIf Reason = "wce" Then Narrative = "" ElseIf Reason = "cnd" Then Narrative = "" ElseIf Reason = "rfs" Then Narrative = "" Else After the else I want the Lookup part of the formula - (LOOKUP(Q44,WUC!A:A,WUC!D:D), but I can't get it to work. The alias as defined above is what I want the Lookup formula to look up in the other sheet. Any suggestions will be greatly appreciated, I'm very new to VBA code. Thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to VBA function (if and lookup)
Just correcting your typing error (the A14 should be R44)...
=IF(OR(R44={"nc","jnc","wce","cnd", "rfs"}),"",IF(OR(R44={"spare","no spare"}),LOOKUP(Q44,WUC!A:A,WUC!D:D))) Rick "Greg Wilson" wrote in message ... Try this instead: =IF(OR(A14={"nc","jnc","wce","cnd", "rfs"}),"",IF(OR(R44={"spare","no spare"}),LOOKUP(Q44,WUC!A:A,WUC!D:D))) Greg "brihyanna" wrote: I am trying to turn this formula into a VBA function (this formula works fine, but I have some other IF's I need to add that will take it over the limit of 7): =IF(R44="nc","",IF(R44="jcn","",IF(R44="wce","",IF (R44="cnd","",IF(R44="no spare",(LOOKUP(Q44,WUC!A:A,WUC!D:D)),IF(R44="spare ",(LOOKUP(Q44,WUC!A:A,WUC!D:D)))))))) So far I have this: Function Narrative(Reason As Range, Alias As Range) If Reason = "nc" Then Narrative = "" ElseIf Reason = "jcn" Then Narrative = "" ElseIf Reason = "wce" Then Narrative = "" ElseIf Reason = "cnd" Then Narrative = "" ElseIf Reason = "rfs" Then Narrative = "" Else After the else I want the Lookup part of the formula - (LOOKUP(Q44,WUC!A:A,WUC!D:D), but I can't get it to work. The alias as defined above is what I want the Lookup formula to look up in the other sheet. Any suggestions will be greatly appreciated, I'm very new to VBA code. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
| Can I create a multiple LOOKUP formula with 'then' function??? | Excel Worksheet Functions | |||
| Whats better Lookup or Function Formula | Excel Worksheet Functions | |||
| I need to use a lookup function in an if formula. | Excel Discussion (Misc queries) | |||
| Array Formula Using LOOKUP function. | Excel Worksheet Functions | |||
Hybrid Mode
