Using If, Then, Else in a UDF

[Rumpkin]

Hello and Happy New Year

I would like to change this formula to a UDF or Sub if possible:
IF($D16=0,"0",IF(INDEX(STDRATE,MATCH($C16,Resource_Code_LIST,0))>$AV16,
INDEX(STDRATE,MATCH($C16,Resource_Code_LIST,0)),IF(OR($C16=0,INDEX(
TOTAL_WAGES,MATCH($C16,Resource_Code_LIST,0),MATCH(
WAGE,WAGE_NAME,0))=0),"0",INDEX(TOTAL_WAGES,MATCH(
$C16,Resource_Code_LIST,0),MATCH(WAGE,WAGE_NAME,0)))))

Monster, right?
I need the Zero result to be in quotes as it copies to another program.
I tried this code but get a #Value

  [TABLE]
<colgroup><col style="width:60pt" width="80">  </colgroup><tbody>[TR]
   [TD="width: 80"]Function   BasePay(rng1 As Range, val1 As Variant, rng2 As Range, val2 As Variant, rng3   As Range, rng4 As Range, rng5 As Range)[/TD]
  [/TR]
  [TR]
   [TD]BasePay = 0[/TD]
  [/TR]
  [TR]
   [TD]On Error GoTo End1[/TD]
  [/TR]
  [TR]
   [TD]ElseIf WorksheetFunction.Index(rng1,   WorksheetFunction.Match(val1, rng2, 0)) > val2 Then[/TD]
  [/TR]
  [TR]
   [TD]      BasePay = WorksheetFunction.Index(rng1, WorksheetFunction.Match(val1,   rng2, 0))[/TD]
  [/TR]
  [TR]
   [TD]If WorksheetFunction.Index(rng1,   WorksheetFunction.Match(val1, rng2, 0)) = 0 Then[/TD]
  [/TR]
  [TR]
   [TD]      BasePay = WorksheetFunction.Index(rng1, WorksheetFunction.Match(val1,   rng2, 0), WorksheetFunction.Match(WorksheetFunction.Index(rng3,   WorksheetFunction.Match(val2, rng4, 0)), rng5, 0))[/TD]
  [/TR]
  [TR]
   [TD]End1:[/TD]
  [/TR]
  [TR]
   [TD]End Function[/TD]
  [/TR]
 </tbody>[/TABLE]

 

[ranman256]

your IF statement is out of order....use IF then the ELSE....
(this assumes your WorksheetFunction is correct)

Function  BasePay(rng1 As Range, val1 As Variant, rng2 As Range, val2 As Variant, rng3 As Range, rng4 As Range, rng5 As Range)


BasePay = 0
On Error GoTo End1


If WorksheetFunction.Index(rng1, WorksheetFunction.Match(val1, rng2, 0)) > val2 Then
      BasePay = WorksheetFunction.Index(rng1, WorksheetFunction.Match(val1,   rng2, 0))
elseIf WorksheetFunction.Index(rng1,   WorksheetFunction.Match(val1, rng2, 0)) = 0 Then
      BasePay = WorksheetFunction.Index(rng1, WorksheetFunction.Match(val1, rng2, 0), WorksheetFunction.Match(WorksheetFunction.Index(rng3, WorksheetFunction.Match(val2, rng4, 0)), rng5, 0))
endif


End1:
End Function

 

[Rumpkin]

Thanks. I still have something missing. I get a #value .
This is my entry:
=BasePay(STDRATE,$C16,Resource_Code_LIST,$AV16,STDRATE,$C16,Resource_Code_LIST,$C16,TOTAL_WAGES,$C16,Resource_Code_LIST,WAGE,WAGE_NAME,TOTAL_WAGES,$C16,Resource_Code_LIST,WAGE,WAGE_NAME)
I'll be off line for a while. I recheck it when i get back.

Regards