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 |
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 |
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 |
All times are GMT +1. The time now is 12:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com