VBA Range and Cells syntax

[rcarr]

This code -
Set varRange = oSheet.Range(Cells(66, 5), Cells(69, 5))
returns the error, "Microsoft VBScript runtime error: Type mismatch:'Cells'"

If I add the dots -
Set varRange = oSheet.Range(.Cells(66, 5), .Cells(69, 5))
it returns the error, "Microsoft VBScript runtime error: Invalid or unqualified reference.

What am I missing? I need to use the cells property to pass the worksheet range as a variable.
Randy Carr
randy.carr@carteretcraven.ncemcs.com

 

[Databaseguy]

Fully qualify it:

is oSheet a subset of an instanciated Excel object. I'll assume so, and call it objExcel

Set varRange = objExcel.Range(objExcel.Cells(66, 5), objExcel.Cells(69, 5))

Automation doesn't like unquaified references, as you found out. Also, make sure you've set your references to the MS Excel V.X object library.

 

[rcarr]

Thanks, that was the problem. I had overlooked the unqualified reference.
Randy Carr
randy.carr@carteretcraven.ncemcs.com

 

[DaleWatson123321]

Hi guys,

Your level of VBA is beyond mine, BUT, is it possible that the solution could be as simple as the following:

IF the application is such that the Range Name HAS been assigned, or could be assigned PRIOR to running the code,
could you not use:

varRange = Worksheets("SheetName&quot.Range("S_varRange&quot

...where "S_varRange" is the Range Name previously assigned.


Second Alternative...

If the Range Name CANNOT be assigned PRIOR, can the Range Name be created using a variation of the following:

Dim S_varRange As Range

Sub Create_Range()
vr = "A2" & ":" & "C20"
Range(vr).Name = "S_varRange"
End Sub

If this second alternative is workable, you could THEN pass the "S_varRange" to the variable "varRange".

I hope my "simple" solution is not TOO simple, and can be of use.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca