Range and Cells method

[Guest]

I am trying to build a range using the following method;

CntRef4 = xlApp.Range(xlApp.Cells(RowRef, ColRef), xlApp.Cells(z, ColRef))

Where CntRef is Range, RowRef,ColRef and z are String. RowRef,ColRef and z
all contain values when I do a mouse over, however CntRef =Nothing. If I
Change CntRef to String it reads =""

How should I look to clear upo this problem

 

[Robin Hammond]

Jenny,

You are missing a set statement for one thing. See the example below which
has made the data typing a bit more obvious and uses numeric data types.

Sub SetRange()
Dim XL As Excel.Application
Dim rngWanted As Range
Dim lRow As Long
Dim nCol As Integer
Dim lRow2 As Long

Set XL = GetObject(, "Excel.Application")
lRow = 1
nCol = 1
lRow2 = 2

With XL
Set rngWanted = .Range(.Cells(lRow, nCol), .Cells(lRow2, nCol))
End With
Debug.Print rngWanted.Address
End Sub

Robin Hammond
www.enhanceddatasystems.com