Range name as varible

[mgray]

I am having trouble understanding why this sub works when used
Range("name") but not Range(NameAsVar). See below. any ideas how to
make this work? Do I have to convert the strings passed to a range
object? How would i do that?


Sub SubstituteVals(var1name As String, var2name As String, _
var1val As Double, var2val As Double)

'Works with specific named ranges

'Range("PurchasePrice") = var1val
'Range("GrossSalesTotal") = var2val

'Does not work if range name is a varible
'Method 'Range' of Object '_Global' failed

Range(var1name) = var1val
Range(var2name) = var2val

End Sub

 

[Tim Williams]

Perhaps you need to specify the worksheet when using variables instead of
hard-coded range names?

Tim

 

[mgray]

It works fine with hard-coaded range names without a worksheet name.
Range("PurchasePrice") = var1val
Range("GrossSalesTotal") = var2val


The problem is it does not work when I replace the range name with a
string variable.
Range(var1name) = var1val
Range(var2name) = var2val

Any other ideas?

 

[Tim Williams]

Works for me (XL 2002 SP3)

Sub tester()

Const S_RNG As String = "test"
Dim sRange As String

sRange = "test"

Range("test").Value = "one"
Range(S_RNG).Value = "two"
Range(sRange).Value = "three"
End Sub

Tim