Actually, the Range method is acting as it should. Your code

Set rClient = wsTribalHist.Range(Cells(3, lHistDataFinalCol + 1))

is passing the *value* of Cells(3,lHistDataFinalCol+1) to Range. If
lHistDataFinalCol = 0, this is the same as

Set rClient = wsTribalHist.Range(Cells(3, 1))

and if Cells(3,1).Value is any value that does not specify a range,
the Range method will fail. If Cells(3,1).Value specifies a range
(e.g., it contains the text "A1:C10"), rClient will refer to that
range.

With your second syntax, you're passing range references to the Range
method. The difference is that Cells can take numeric parameters (row
and/or column numbers) and Range cannot. Range requires either other
Range objects or Strings as inputs.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)






On Thu, 9 Oct 2008 15:27:22 -0600, salgud
wrote:

I entered a line of code that seemed to me it should work, since the cue
that appears when you enter a range using "Cells" says that "Cell2" is
optional (in brackets), but it's not.

This should be, if the cue were correct, an acceptable line of code:

Set rClient = wsTribalHist.Range(Cells(3, lHistDataFinalCol + 1))

But isn't.
But this line is ok:

Set rClient = wsTribalHist.Range(Cells(3, lHistDataFinalCol + 1), Cells(3,
lHistDataFinalCol + 1))

So the cue indicates that the Cell2 after the comma is optional, when it is
really required.

What I'm wondering is are these false cues listed anywhere, or do we just
learn them as we go? I'm starting to make notes about these things because
I find it hard to remember all these little glitches. Has anyone attempted
to compile a list?