date calc returning #name?
 

I am trying to figure out what is wrong with my Else formula. The
"begrevdate" is the name of a cell which contains a date with format
"MM/DD/YY".
The intermediate window shows:
?lastdate
3/31/05
?begrevdate
12:00:00 AM

I don't understand why begredate is not returning a date when a date is
present in the cell.

The result of the code in the sheet is #name?

With Range("a" & numberrows + 2)
.NumberFormat = "0.00"
.Name = "revtime"
If ans = vbNo Then
'calculates review period based on real-time date
.FormulaR1C1 = "=(today()-begrevdate)/365"
Else: .Formula = "=(lastdate-begrevdate)/365"
End If
End With

Thanks for your thoughts

date calc returning #name?
 

If 'begrevdate' is a named range of single cell size, then your reference to
it is incorrect. You are referring to the name as though it were a VBA
variable. If you want to retrieve the value from the 'begrevdate' named
range then replace 'begrevdate' in your code with the following:

Range("begrevdate").Value

You will also need to make sure that the datatype matches those variables to
which it is being compared.

HTH,
VBA Dabbler

"Papa Jonah" wrote:

I am trying to figure out what is wrong with my Else formula. The
"begrevdate" is the name of a cell which contains a date with format
"MM/DD/YY".
The intermediate window shows:
?lastdate
3/31/05
?begrevdate
12:00:00 AM

I don't understand why begredate is not returning a date when a date is
present in the cell.

The result of the code in the sheet is #name?

With Range("a" & numberrows + 2)
.NumberFormat = "0.00"
.Name = "revtime"
If ans = vbNo Then
'calculates review period based on real-time date
.FormulaR1C1 = "=(today()-begrevdate)/365"
Else: .Formula = "=(lastdate-begrevdate)/365"
End If
End With

Thanks for your thoughts

date calc returning #name?
 

I don't understand. You say that begrevdate is a cell name. But ?begrevdate
is printing a VBA variable, if you want the cell you use
?Range("begrevdate").

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Papa Jonah" wrote in message
...
I am trying to figure out what is wrong with my Else formula. The
"begrevdate" is the name of a cell which contains a date with format
"MM/DD/YY".
The intermediate window shows:
?lastdate
3/31/05
?begrevdate
12:00:00 AM

I don't understand why begredate is not returning a date when a date is
present in the cell.

The result of the code in the sheet is #name?

With Range("a" & numberrows + 2)
.NumberFormat = "0.00"
.Name = "revtime"
If ans = vbNo Then
'calculates review period based on real-time date
.FormulaR1C1 = "=(today()-begrevdate)/365"
Else: .Formula = "=(lastdate-begrevdate)/365"
End If
End With

Thanks for your thoughts

date calc returning #name?
 

I understand. I suspected as much. So I rewrote the line to be
Else: .Formula = "=(lastdate-reviewbeg)/365"

In this case, both lastdate and reviewbeg come directly from input boxes and
are both declared as public ... as date.
Fore example:
reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning of the
review period?", , "10/1/2004")

At the point that this line is to run, the immediate window reveals:
?lastdate
3/31/2005
?reviewbeg
10/1/2004

After all of that the result is still the same: #name?

"VBA Dabbler" wrote:

If 'begrevdate' is a named range of single cell size, then your reference to
it is incorrect. You are referring to the name as though it were a VBA
variable. If you want to retrieve the value from the 'begrevdate' named
range then replace 'begrevdate' in your code with the following:

Range("begrevdate").Value

You will also need to make sure that the datatype matches those variables to
which it is being compared.

HTH,
VBA Dabbler

"Papa Jonah" wrote:

I am trying to figure out what is wrong with my Else formula. The
"begrevdate" is the name of a cell which contains a date with format
"MM/DD/YY".
The intermediate window shows:
?lastdate
3/31/05
?begrevdate
12:00:00 AM

I don't understand why begredate is not returning a date when a date is
present in the cell.

The result of the code in the sheet is #name?

With Range("a" & numberrows + 2)
.NumberFormat = "0.00"
.Name = "revtime"
If ans = vbNo Then
'calculates review period based on real-time date
.FormulaR1C1 = "=(today()-begrevdate)/365"
Else: .Formula = "=(lastdate-begrevdate)/365"
End If
End With

Thanks for your thoughts

date calc returning #name?
 

Bob,
I'm not sure I understand what you mean. I have responded to dabbler though
with my revised approach. Maybe that addresses your question.

Thanks for your thoughts


"Bob Phillips" wrote:

I don't understand. You say that begrevdate is a cell name. But ?begrevdate
is printing a VBA variable, if you want the cell you use
?Range("begrevdate").

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Papa Jonah" wrote in message
...
I am trying to figure out what is wrong with my Else formula. The
"begrevdate" is the name of a cell which contains a date with format
"MM/DD/YY".
The intermediate window shows:
?lastdate
3/31/05
?begrevdate
12:00:00 AM

I don't understand why begredate is not returning a date when a date is
present in the cell.

The result of the code in the sheet is #name?

With Range("a" & numberrows + 2)
.NumberFormat = "0.00"
.Name = "revtime"
If ans = vbNo Then
'calculates review period based on real-time date
.FormulaR1C1 = "=(today()-begrevdate)/365"
Else: .Formula = "=(lastdate-begrevdate)/365"
End If
End With

Thanks for your thoughts

date calc returning #name?
 

The Dazzler's advice would be good if you are trying to get the value of the
named range begrevdate and use it in VBA, but this doesn't appear to be the
case.

Whatever, your previous problem is probably just the opposite.

assuming Lastdate is a VBA variable containing a string like 10/1/2004 as
it would if you entered 10/1/2004 in an inputbox and assigned the return
value to LastDate, then

..formula = "=(DateValue(""" & lastdate & """)-begrevdate)/365"

would produce something like
=(DateValue("10/1/2004")-begrevdate)/365

Just to check it in the immediate window:

lastDate = "10/1/2004"
? "=(DateValue(""" & lastdate & """)-begrevdate)/365"
=(DateValue("10/1/2004")-begrevdate)/365

This would work if begrevdate is a named range

This may not be exactly what you want, but hopefully it will give you some
insight into building what you do want.

--
Regards,
Tom Ogilvy


"Papa Jonah" wrote in message
...
I understand. I suspected as much. So I rewrote the line to be
Else: .Formula = "=(lastdate-reviewbeg)/365"

In this case, both lastdate and reviewbeg come directly from input boxes
and
are both declared as public ... as date.
Fore example:
reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning of
the
review period?", , "10/1/2004")

At the point that this line is to run, the immediate window reveals:
?lastdate
3/31/2005
?reviewbeg
10/1/2004

After all of that the result is still the same: #name?

"VBA Dabbler" wrote:

If 'begrevdate' is a named range of single cell size, then your
reference to
it is incorrect. You are referring to the name as though it were a VBA
variable. If you want to retrieve the value from the 'begrevdate' named
range then replace 'begrevdate' in your code with the following:

Range("begrevdate").Value

You will also need to make sure that the datatype matches those
variables to
which it is being compared.

HTH,
VBA Dabbler

"Papa Jonah" wrote:

I am trying to figure out what is wrong with my Else formula. The
"begrevdate" is the name of a cell which contains a date with format
"MM/DD/YY".
The intermediate window shows:
?lastdate
3/31/05
?begrevdate
12:00:00 AM

I don't understand why begredate is not returning a date when a date
is
present in the cell.

The result of the code in the sheet is #name?

With Range("a" & numberrows + 2)
.NumberFormat = "0.00"
.Name = "revtime"
If ans = vbNo Then
'calculates review period based on real-time date
.FormulaR1C1 = "=(today()-begrevdate)/365"
Else: .Formula = "=(lastdate-begrevdate)/365"
End If
End With

Thanks for your thoughts

date calc returning #name?
 

Tom,
I don't understand why, but my first test worked like a charm!
Thanks!!
Papa

"Tom Ogilvy" wrote:

The Dazzler's advice would be good if you are trying to get the value of the
named range begrevdate and use it in VBA, but this doesn't appear to be the
case.

Whatever, your previous problem is probably just the opposite.

assuming Lastdate is a VBA variable containing a string like 10/1/2004 as
it would if you entered 10/1/2004 in an inputbox and assigned the return
value to LastDate, then

..formula = "=(DateValue(""" & lastdate & """)-begrevdate)/365"

would produce something like
=(DateValue("10/1/2004")-begrevdate)/365

Just to check it in the immediate window:

lastDate = "10/1/2004"
? "=(DateValue(""" & lastdate & """)-begrevdate)/365"
=(DateValue("10/1/2004")-begrevdate)/365

This would work if begrevdate is a named range

This may not be exactly what you want, but hopefully it will give you some
insight into building what you do want.

--
Regards,
Tom Ogilvy


"Papa Jonah" wrote in message
...
I understand. I suspected as much. So I rewrote the line to be
Else: .Formula = "=(lastdate-reviewbeg)/365"

In this case, both lastdate and reviewbeg come directly from input boxes
and
are both declared as public ... as date.
Fore example:
reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning of
the
review period?", , "10/1/2004")

At the point that this line is to run, the immediate window reveals:
?lastdate
3/31/2005
?reviewbeg
10/1/2004

After all of that the result is still the same: #name?

"VBA Dabbler" wrote:

If 'begrevdate' is a named range of single cell size, then your
reference to
it is incorrect. You are referring to the name as though it were a VBA
variable. If you want to retrieve the value from the 'begrevdate' named
range then replace 'begrevdate' in your code with the following:

Range("begrevdate").Value

You will also need to make sure that the datatype matches those
variables to
which it is being compared.

HTH,
VBA Dabbler

"Papa Jonah" wrote:

I am trying to figure out what is wrong with my Else formula. The
"begrevdate" is the name of a cell which contains a date with format
"MM/DD/YY".
The intermediate window shows:
?lastdate
3/31/05
?begrevdate
12:00:00 AM

I don't understand why begredate is not returning a date when a date
is
present in the cell.

The result of the code in the sheet is #name?

With Range("a" & numberrows + 2)
.NumberFormat = "0.00"
.Name = "revtime"
If ans = vbNo Then
'calculates review period based on real-time date
.FormulaR1C1 = "=(today()-begrevdate)/365"
Else: .Formula = "=(lastdate-begrevdate)/365"
End If
End With

Thanks for your thoughts