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?

If you only want to reference 1 cell rather than a range just use cells!
Set rClient = wsTribalHist.Cells(3, lHistDataFinalCol + 1)

Regards,
The Code Cage Team
www.thecodecage.com/forumz


"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?

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?

hi
i think your problem is that you are using the range object and the cell
object togeather.
range("A1")
and
Cells(1,1)
usually that are not needed togeather unless your are selecting a range of
cells instead of a single cell. what are you trying to do?
you could probably get by with this (assuming that IHIstDataFinalCol refers
to a column. not sure what your variables are)......
Set rClient = wsTribalHist.Cells(3, lHistDataFinalCol + 1)


regards
FSt1

"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?

On Thu, 09 Oct 2008 16:48:07 -0500, Chip Pearson wrote:

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?

Thanks for your reply, Chip. I'm still confused. I tried this:

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

and it didnt't work. But this:

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

does, as you said. There are no strings or ranges in either, but when I
repeat the same cells command, it works. I don't understand why sending
numbers one time fails, and sending numbers 2 times works. Or, how sending
it in the first example is sending the value of Cells(1,3) but by repeating
it, it sends a range. Does the comma somehow change what Range sees inside
the parenthesis?

On Thu, 9 Oct 2008 14:43:03 -0700, The Code Cage Team wrote:

If you only want to reference 1 cell rather than a range just use cells!
Set rClient = wsTribalHist.Cells(3, lHistDataFinalCol + 1)

Regards,
The Code Cage Team
www.thecodecage.com/forumz

Thanks for your reply. That worked!