Formula to VBA function (if and lookup)

[brihyanna]

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)),IF(R44="spare",(LOOKUP(Q44,WUC!A:A,WUC!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), 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!

 

[SeanC UK]

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.

 

[Joel]

a = WorksheetFunction.Lookup(Range("Q44"), _
Sheets("WUC").Columns("A:A"), Sheets("WUC").Columns("D"))

 

[Charles Williams]

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),"????"))

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

 

[Greg Wilson]

Try this instead:

=IF(OR(A14={"nc","jnc","wce","cnd", "rfs"}),"",IF(OR(R44={"spare","no
spare"}),LOOKUP(Q44,WUC!A:A,WUC!D)))

Greg

 

[Greg Wilson]

Change A14 to R44. Testing artifact.

Greg

Try this instead:

=IF(OR(A14={"nc","jnc","wce","cnd", "rfs"}),"",IF(OR(R44={"spare","no
spare"}),LOOKUP(Q44,WUC!A:A,WUC!D)))

Greg

 

[Rick Rothstein \(MVP - VB\)]

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)))

Rick