how to cast a Range value to a Date value?

[Mark Dvorkin]

The right hand side of the expression below returns a Range that in my
case will always be the value of the last cell in the column DueDateColumn
.....
Dim DueDate as Date

Set DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp)
.....

How can I stuff this value into the DueDate variable???????

I appreciate your patience with me and
thanks for you help in advance,
/mark

 

[Norman Jones]

Hi Mark,

Try:

DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp).Value


---
Regards,
Norman



The right hand side of the expression below returns a Range that in my
case will always be the value of the last cell in the column DueDateColumn
.....
Dim DueDate as Date

Set DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp)
.....

How can I stuff this value into the DueDate variable???????

I appreciate your patience with me and
thanks for you help in advance,
/mark

 

[Mark Dvorkin]

Norman,
first of all: thanks for your patience!

the call

DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp).Value

is within a function which knows which sheet is active, i.e. I'm passing in
the sheet name to it. For some reason at this line my function blows up.

If I declare

Dim DueDateR as Range
and

Set DueDateR = Cells(Rows.Count, DueDateColumn).End(xlUp)


the value I find in the DueDateR is the name of the last Sheet in that
Workbook! Do I need (and if so then how)
to explicitly tell the Cells method which Sheet it has to act upon?

Thanks a lot,
/mark

 

[Norman Jones]

Hi Mark,

If the Cells property is not explicitly qualified, it will be implicitly
qualified to refer to the active sheet (unless the code is in a sheet
module, in which case the qualification will be to the sheet holding the
code).

Try, therefore, something like:

Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets(ShName)

DueDate =SH.Cells(Rows.Count, DueDateColumn).End(xlUp).Value

where ShName is the passed sheet name.


---
Regards,
Norman



Norman,
first of all: thanks for your patience!

the call

DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp).Value
is within a function which knows which sheet is active, i.e. I'm passing in
the sheet name to it. For some reason at this line my function blows up.

If I declare

Dim DueDateR as Range
and

Set DueDateR = Cells(Rows.Count, DueDateColumn).End(xlUp)


the value I find in the DueDateR is the name of the last Sheet in that
Workbook! Do I need (and if so then how)
to explicitly tell the Cells method which Sheet it has to act upon?

Thanks a lot,
/mark

Norman Jones wrote:

Hi Mark,

Try:

DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp).Value

 

[Tom Ogilvy]

Public Function MyFunction(shName as String, DueDateColumn as Long)
Dim DueDateR as Range
Dim DueDate as Date
Set DueDateR = Worksheets(shName).Cells( _
Rows.Count, DueDateColumn).End(xlUp)
DueDate = DueDateR.Value

--
Regards,
Tom Ogilvy



Norman,
first of all: thanks for your patience!

the call

DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp).Value
is within a function which knows which sheet is active, i.e. I'm passing in
the sheet name to it. For some reason at this line my function blows up.

If I declare

Dim DueDateR as Range
and

Set DueDateR = Cells(Rows.Count, DueDateColumn).End(xlUp)


the value I find in the DueDateR is the name of the last Sheet in that
Workbook! Do I need (and if so then how)
to explicitly tell the Cells method which Sheet it has to act upon?

Thanks a lot,
/mark

Norman Jones wrote:

Hi Mark,

Try:

DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp).Value


---
Regards,
Norman



The right hand side of the expression below returns a Range that in my
case will always be the value of the last cell in the column DueDateColumn
.....
Dim DueDate as Date

Set DueDate = Cells(Rows.Count, DueDateColumn).End(xlUp)
.....

How can I stuff this value into the DueDate variable???????

I appreciate your patience with me and
thanks for you help in advance,
/mark

 

[Mark Dvorkin]

the suggested qualification did the job!

As a C/C++ programmer I'm a beginner here.
I believed my code is in the WorkBook module,
not any more ...

Where it supposed to be, what is the right place for
it or what is the best place for it.
How to check where is my code?

Thanks for all your help,
deeply indebted
/mark

 

[Norman Jones]

Hi Mark,

I would refer you to Chip Pearson's comprehensive treatment of this precise
issue at:

http://www.cpearson.com/excel/codemods.htm

 

[Mark Dvorkin]

Man!

you have answers for everything and right on the button ...

Chip Pearson's site is great.
Thanks again, I'm all set for the rest of the night.
/mark.