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!

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!

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!

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!

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!

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!

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!