UDF not returning correct information
For some reason my UDF only returns a number if the "CustPartNo"
variable is in the VOEMprodn range; otherwise it gives #VALUE!. However I need to address the case(s) that the CustPartNo variable is not contained in that range. I keep looking at it and I can't seem to find where it goes wrong. Any suggestions? Here's my code . . . Option Explicit Function SHIPREQ(CustPartNo As Range, tDate As Range) Dim VOEMprodn As Range Dim VOEMSPO As Range Dim HOEMprodn As Range Dim HOEMSPO As Range Dim ROEMprodn As Range Dim ROEMSPO As Range Dim MatchVprodn As Integer Dim MatchVSPO As Integer Dim MatchHprodn As Integer Dim MatchHSPO As Integer Set VOEMprodn = Workbooks("OEM Shipping Schedule.xls").Worksheets("Production").Range("A4: A150") Set VOEMSPO = Workbooks("OEM Shipping Schedule.xls").Worksheets("Service Parts").Range("A4:A150") Set HOEMprodn = Workbooks("OEM Shipping Schedule.xls").Worksheets("Production").Range("A4: AH4") Set HOEMSPO = Workbooks("OEM Shipping Schedule.xls").Worksheets("Service Parts").Range("A4:AH4") Set ROEMprodn = Workbooks("OEM Shipping Schedule.xls").Worksheets("Production").Range("A4: AH150") Set ROEMSPO = Workbooks("OEM Shipping Schedule.xls").Worksheets("Service Parts").Range("A4:AH150") MatchVprodn = Application.WorksheetFunction.Match(CustPartNo, VOEMprodn, 0) MatchHprodn = Application.WorksheetFunction.Match(tDate, HOEMprodn, 0) MatchVSPO = Application.WorksheetFunction.Match(CustPartNo, VOEMSPO, 0) MatchHSPO = Application.WorksheetFunction.Match(tDate, HOEMSPO, 0) 'OLD DEBUG TEST STUFF 'SHIPREQ = Application.WorksheetFunction.CountIf(HOEMprodn, tDate) 'SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn, 5, 5) '<works 'SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn, MatchVprodn, MatchHprodn) If Application.WorksheetFunction.CountIf(HOEMprodn, tDate) 0 Then 'If date is in HOEM set' If Application.WorksheetFunction.CountIf(VOEMprodn, CustPartNo) 0 _ And Application.WorksheetFunction.CountIf(VOEMSPO, CustPartNo) 0 = True Then 'and If # is in prodn & SPO set' SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn, Application.WorksheetFunction.Match(CustPartNo, VOEMprodn, 0), Application.WorksheetFunction.Match(tDate, HOEMprodn, 0)) _ + Application.WorksheetFunction.Index(ROEMSPO, Application.WorksheetFunction.Match(CustPartNo, VOEMSPO, 0), Application.WorksheetFunction.Match(tDate, HOEMSPO, 0)) 'return reqs from prodn and SPO' ElseIf Application.WorksheetFunction.CountIf(VOEMprodn, CustPartNo) 0 Then 'else If # is in prodn set' SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn, Application.WorksheetFunction.Match(CustPartNo, VOEMprodn, 0), Application.WorksheetFunction.Match(tDate, HOEMprodn, 0)) ElseIf Application.WorksheetFunction.CountIf(VOEMSPO, CustPartNo) 0 Then 'else If # is in SPO set' SHIPREQ = Application.WorksheetFunction.Index(ROEMSPO, Application.WorksheetFunction.Match(CustPartNo, VOEMSPO, 0), Application.WorksheetFunction.Match(tDate, HOEMSPO, 0)) 'return reqs from prodn' Else SHIPREQ = 0 'if # not in prodn or SPO set return 0' End If Else SHIPREQ = 0 'if date not in set return 0' End If End Function |
UDF not returning correct information
try like: Function SHIPREQ(CustPartNo As Range, tDate As Range) As Double Dim rPro As Range, rSpo As Range Dim rowPro, colPro, rowSpo, colSpo 'variants With Workbooks("OEM Shipping Schedule.xls") Set rPro = .Worksheets("Production").Range("A4:AH150") Set rSpo = .Worksheets("Service Parts").Range("A4:AH150") End With 'use a variant to catch Match result 'use application w/o worksheetfunction to avoid R/T errors With Application rowPro = .Match(CustPartNo, rPro.Columns(1), 0) colPro = .Match(tDate, rPro.Rows(1), 0) rowSpo = .Match(CustPartNo, rSpo.Columns(1), 0) colSpo = .Match(tDate, rSpo.Rows(1), 0) End With If Not IsError(rowPro) And Not IsError(colPro) Then SHIPREQ = rPro(rowPro, colPro).Value End If If Not IsError(rowSpo) And Not IsError(colSpo) Then SHIPREQ = SHIPREQ + rSpo(rowSpo, colSpo).Value End If End Function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote : For some reason my UDF only returns a number if the "CustPartNo" variable is in the VOEMprodn range; otherwise it gives #VALUE!. However I need to address the case(s) that the CustPartNo variable is not contained in that range. I keep looking at it and I can't seem to find where it goes wrong. Any suggestions? Here's my code . . . |
All times are GMT +1. The time now is 02:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com