Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem building Function with worksheet name and range
hello,
I have a list of worksheets and would like to return certain values from different ranges that I have named on each sheet I have created this function by it does not work =getvalue(G283,Parameter1,1,2) would return the value of row 1 column 2 in range "Parameter1" in Sheetname written in G283. Any idea? thanks a lot Public Function GetValue(Ws As String, Pr As String, X As Double, Y As Double) As Variant Application.Volatile Dim Ob1 As Object Ob1 = Worksheets(Ws) Dim Rn As Range Set Rn = Ob1.Range(Pr) GetValue = Rn(X, Y).Value End Function -- caroline |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem building Function with worksheet name and range
Hi
This worked for me Public Function GetValue(Ws As String, Pr As String, X As Double, Y As Double) As Variant GetValue = Worksheets(Ws).Range(Pr).Cells(X, Y).Value End Function No need to create the intermediate objects. You can't do Rn(X, Y). The Cells(X,Y) refer to row X and column Y in Range(Pr). regards Paul On Oct 26, 5:28*pm, caroline wrote: hello, I have a list of worksheets and would like to return certain values from different ranges that I have named on each sheet I have created this function by it does not work =getvalue(G283,Parameter1,1,2) would return the value of row 1 column 2 in range "Parameter1" in Sheetname written in G283. Any idea? thanks a lot Public Function GetValue(Ws As String, Pr As String, X As Double, Y As Double) As Variant Application.Volatile Dim Ob1 As Object Ob1 = Worksheets(Ws) Dim Rn As Range Set Rn = Ob1.Range(Pr) GetValue = Rn(X, Y).Value End Function -- caroline |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem building Function with worksheet name and range
I think you're going to have to make a choice.
You're either going to have to pass strings to your function: =getvalue(G283,"Parameter1",1,2) And use something like: Option Explicit Public Function GetValue(Ws As String, _ Pr As String, _ X As Double, _ Y As Double) As Variant Application.Volatile Dim Ob1 As Worksheet Dim Rn As Range Dim myCell As Range Set Ob1 = Nothing On Error Resume Next Set Ob1 = Worksheets(Ws) On Error GoTo 0 If Ob1 Is Nothing Then GetValue = "Missing worksheet!" Else Set Rn = Nothing On Error Resume Next Set Rn = Ob1.Range(Pr) On Error GoTo 0 If Rn Is Nothing Then GetValue = "Missing Range Name" Else Set myCell = Nothing On Error Resume Next Set myCell = Rn(X, Y).Cells(1) On Error GoTo 0 If myCell Is Nothing Then GetValue = "Invalid offset's" Else GetValue = myCell.Value End If End If End If End Function ============ Or pass it a range (like an address). But since a range already knows its parent (the worksheet that owns it), you don't need to (and can't!) specify the worksheet. (Well, I guess you could pass it as a parameter and ignore it if you wanted...) Option Explicit Public Function GetValue(Pr As Range, _ X As Double, _ Y As Double) As Variant Application.Volatile Dim myCell As Range Set myCell = Nothing On Error Resume Next Set myCell = Pr(X, Y).Cells(1) On Error GoTo 0 If myCell Is Nothing Then GetValue = "Invalid offset's" Else GetValue = myCell.Value End If End Function Then you'd call your function like this if Parameter1 is a workbook level name: =getvalue(Parameter1,3,5) or if Parameter1 were a sheet level name: =getvalue(Sheet2!Parameter1,3,5) caroline wrote: hello, I have a list of worksheets and would like to return certain values from different ranges that I have named on each sheet I have created this function by it does not work =getvalue(G283,Parameter1,1,2) would return the value of row 1 column 2 in range "Parameter1" in Sheetname written in G283. Any idea? thanks a lot Public Function GetValue(Ws As String, Pr As String, X As Double, Y As Double) As Variant Application.Volatile Dim Ob1 As Object Ob1 = Worksheets(Ws) Dim Rn As Range Set Rn = Ob1.Range(Pr) GetValue = Rn(X, Y).Value End Function -- caroline -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building a table from a list on another worksheet | Excel Discussion (Misc queries) | |||
Excel "Worksheet Name" Building Function for Summary Sheet | Excel Discussion (Misc queries) | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
Building a Named Range in a formula with VBA | Excel Programming | |||
Building a dropdown from a worksheet | Excel Programming |