| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Date with VBA
Hello,
I need to know if it is possible in a VBA macro to do the following: In Worksheet # 1 I have the following text values in rows: K1 30.11.2004 k2 24.12.2004 k3 30.10.2004 k4 25.11.2004 I would like to have a macro that would go through each line and determine the oldest date and copy that date into Worksheet #2 with the format mn-yy. In the example above: it'd be Oct-04 Thanks for any help, JF |
|
#2
|
|||
|
|||
|
Hi Jeff
do you really need to use VBA or would the following formula in Sheet 2 work for you? =MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,".","/"))) entered using control & shift & enter instead of just enter Cheers JulieD "Jeff" wrote in message ... Hello, I need to know if it is possible in a VBA macro to do the following: In Worksheet # 1 I have the following text values in rows: K1 30.11.2004 k2 24.12.2004 k3 30.10.2004 k4 25.11.2004 I would like to have a macro that would go through each line and determine the oldest date and copy that date into Worksheet #2 with the format mn-yy. In the example above: it'd be Oct-04 Thanks for any help, JF |
|
#3
|
|||
|
|||
|
Thank you Julie,
Sorry, but I need a VBA macro to complete an existing macro. Thanks, JF "JulieD" wrote: Hi Jeff do you really need to use VBA or would the following formula in Sheet 2 work for you? =MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,".","/"))) entered using control & shift & enter instead of just enter Cheers JulieD "Jeff" wrote in message ... Hello, I need to know if it is possible in a VBA macro to do the following: In Worksheet # 1 I have the following text values in rows: K1 30.11.2004 k2 24.12.2004 k3 30.10.2004 k4 25.11.2004 I would like to have a macro that would go through each line and determine the oldest date and copy that date into Worksheet #2 with the format mn-yy. In the example above: it'd be Oct-04 Thanks for any help, JF |
|
#4
|
|||
|
|||
|
Hi
well then how about Sub findoldest() Sheets("Sheet2").Range("c5").FormulaArray = "=MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,""."", ""/"")))" End Sub Cheers JulieD "Jeff" wrote in message ... Thank you Julie, Sorry, but I need a VBA macro to complete an existing macro. Thanks, JF "JulieD" wrote: Hi Jeff do you really need to use VBA or would the following formula in Sheet 2 work for you? =MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,".","/"))) entered using control & shift & enter instead of just enter Cheers JulieD "Jeff" wrote in message ... Hello, I need to know if it is possible in a VBA macro to do the following: In Worksheet # 1 I have the following text values in rows: K1 30.11.2004 k2 24.12.2004 k3 30.10.2004 k4 25.11.2004 I would like to have a macro that would go through each line and determine the oldest date and copy that date into Worksheet #2 with the format mn-yy. In the example above: it'd be Oct-04 Thanks for any help, JF |
|
#5
|
|||
|
|||
|
Hi,
I have the following error: #VALUE! Thanks, "JulieD" wrote: Hi well then how about Sub findoldest() Sheets("Sheet2").Range("c5").FormulaArray = "=MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,""."", ""/"")))" End Sub Cheers JulieD "Jeff" wrote in message ... Thank you Julie, Sorry, but I need a VBA macro to complete an existing macro. Thanks, JF "JulieD" wrote: Hi Jeff do you really need to use VBA or would the following formula in Sheet 2 work for you? =MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,".","/"))) entered using control & shift & enter instead of just enter Cheers JulieD "Jeff" wrote in message ... Hello, I need to know if it is possible in a VBA macro to do the following: In Worksheet # 1 I have the following text values in rows: K1 30.11.2004 k2 24.12.2004 k3 30.10.2004 k4 25.11.2004 I would like to have a macro that would go through each line and determine the oldest date and copy that date into Worksheet #2 with the format mn-yy. In the example above: it'd be Oct-04 Thanks for any help, JF |
|
#6
|
|||
|
|||
|
Hi Jeff
could be a line wrap problem - try Sub findoldest() Sheets("Sheet2").Range("c5").FormulaArray = _ "=MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,""."", ""/"")))" End Sub if that doesn't work, could you copy and paste in the code you're using Cheers JulieD "Jeff" wrote in message ... Hi, I have the following error: #VALUE! Thanks, "JulieD" wrote: Hi well then how about Sub findoldest() Sheets("Sheet2").Range("c5").FormulaArray = "=MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,""."", ""/"")))" End Sub Cheers JulieD "Jeff" wrote in message ... Thank you Julie, Sorry, but I need a VBA macro to complete an existing macro. Thanks, JF "JulieD" wrote: Hi Jeff do you really need to use VBA or would the following formula in Sheet 2 work for you? =MIN(DATEVALUE(SUBSTITUTE(Sheet1!K1:K4,".","/"))) entered using control & shift & enter instead of just enter Cheers JulieD "Jeff" wrote in message ... Hello, I need to know if it is possible in a VBA macro to do the following: In Worksheet # 1 I have the following text values in rows: K1 30.11.2004 k2 24.12.2004 k3 30.10.2004 k4 25.11.2004 I would like to have a macro that would go through each line and determine the oldest date and copy that date into Worksheet #2 with the format mn-yy. In the example above: it'd be Oct-04 Thanks for any help, JF |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
| Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
| Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
| Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
| Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions | |||
Linear Mode
