Reputation: 3250
I'm trying to use a helper function to get a range to store it into a variant, but I'm running into some issues.
At first, I just simply tried the following:
Function GetRange(RangeLetter As String, Optional LastUniqueLine As Long = 1048576) As Varient
Static LastUniqueLineStr As String
If LastUniqueLineStr = "" Then
LastUniqueLineStr = LastUniqueLine
End If
Set GetRange = Range(RangeLetter + "2:" + LastUniqueLineStr)
End Function
But that didn't seem to work. Range()
seemed to be out of scope here or something, so I figured I had to pass in the worksheet to get it working:
Function GetRange(RangeLetter As String, Optional LastUniqueLine As Long = 1048576, Optional ActiveSheet As Worksheet) As Variant
Static LastUniqueLineStr As String
Static CurrentSheet As Worksheet
'If CurrentSheet = Nothing Then
Set CurrentSheet = ActiveSheet
'End If
If LastUniqueLineStr = "" Then
LastUniqueLineStr = LastUniqueLine
End If
Set GetRange = CurrentSheet.Range(RangeLetter + "2:" + LastUniqueLineStr) ' This is the line where I get the error.
End Function
And that's not working either. I get the following error:
Run-time error '1004':
Method 'Range' of object 'Worksheet' failed
How do I get the range I want out of this when I call it?
Upvotes: 0
Views: 1105
Reputation: 21
It looks to me like the error is in the line where your setting Your "GetRange" variable. It looks like the result of .Range(RangeLetter + "2:" + Last....) would create a range with an address of "a2:#" which will fail. A range in that format needs to be "a2:e7" or similar. Your range reference has to be symmetrical. "RC:RC", "R:R", or "C:C". Hope that helps.
Upvotes: 1
Reputation: 53663
Try:
GetRange = Range(RangeLetter + "2:" + RangeLetter + LastUniqueLineStr).Value
You were missing a RangeLetter
which was resulting in a malformed address. Also, use the .Value
property to return a variant/array, and omit the Set
keyword.
I continue to get the error when qualifying as CurrentSheet.Range...
there is no ActiveSheet
within the context of the Function, so you could pass a worksheet as a variable:
Sub Test()
Dim var As Variant
var = GetRange(ActiveSheet, "A")
End Sub
Function GetRange(sh As Worksheet, RangeLetter As String, Optional LastUniqueLine As Long = 1048576, Optional ActiveSheet As Worksheet) As Variant
Static LastUniqueLineStr As String
Dim myRange As Range
If LastUniqueLineStr = "" Then
LastUniqueLineStr = LastUniqueLine
End If
Set myRange = sh.Range(RangeLetter + "2:" + RangeLetter + LastUniqueLineStr)
GetRange = myRange.Value ' This is the line where I get the error.
End Function
Upvotes: 1