Formula to VBA function (if and lookup)

  • Thread starter Thread starter brihyanna
  • Start date Start date
B

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: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!
 
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.
 
a = WorksheetFunction.Lookup(Range("Q44"), _
Sheets("WUC").Columns("A:A"), Sheets("WUC").Columns("D:D"))
 
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,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
 
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
 
Change A14 to R44. Testing artifact.

Greg

Greg Wilson said:
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
Click to expand...
 
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
 
Back
Top