Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calc. an anniversary date(50 years) after marriage date in excel | Excel Worksheet Functions | |||
ND TO CALC FROM ORIG DATE TO NEXT DATE RECEIVED? | Excel Discussion (Misc queries) | |||
Calc End Date | Excel Worksheet Functions | |||
Date Calc. HELP | New Users to Excel | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions |