Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a Range from Function
I am trying to return a range from a function and I am getting an error:
Application Definded of Object-Defined Error, any ideas why? I then want to use the Col D portion of the Range that is returned in my msgbox. Is my code correct for this? Sub TEST() Dim aryPartDes(0 To 25) As Variant Dim aryPartQty(0 To 25) As Variant If CheckBox1 = True Then ' TopDoor aryPartDes(0) = PartInfo("EXT00011742") aryPartQty(0) = tbxCabSizeWft + tbxCabSizeWins / 12 MsgBox aryPartDes(0).Cells(3).Value End If End Sub Public Function PartInfo(PartNumber As String) As Range Dim lngRow As Long lngRow = WorksheetFunction.Match(PartNumber, Sheets("Parts List").Range("A:A"), 0) Error Set PartInfo = Sheets("Parts List").Range(Cells(lngRow, "A"), Cells(lngRow, "D")) End Function End Sub -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a Range from Function
Maybe ... Code: -------------------- Sub TEST() Dim aryPartDes(0 To 25) As Range Dim aryPartQty(0 To 25) As Variant If ActiveSheet.CheckBox1 = True Then ' TopDoor Set aryPartDes(0) = PartInfo("EXT00011742") aryPartQty(0) = tbxCabSizeWft + tbxCabSizeWins / 12 MsgBox aryPartDes(0).Cells(3).Value End If End Sub Public Function PartInfo(PartNumber As String) As Range Dim lngRow As Long With Sheets("Parts List") lngRow = WorksheetFunction.Match(PartNumber, .Range("A:A"), 0) Set PartInfo = .Range(.Cells(lngRow, "A"), .Cells(lngRow, "D")) End With End Function -------------------- -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=25569 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function like INDIRECT to return a range? | Excel Worksheet Functions | |||
I would like a function to return a value that fits its range | Excel Worksheet Functions | |||
return range of data from lookup function | Excel Worksheet Functions | |||
custom function - return range | Excel Programming | |||
return used letters in a range function - an example | Excel Programming |