Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Help with code for Macro!
Hello,
I'm trying to create a Macro that does the following: 1. Copies the values of 12 cells from Sheet1 to Sheet2. 2. Perform an import of data from a database that will over-write the values in the 12 copied cells. 3. Paste the copied values back to their original cells on Sheet1 in 11 columns. The only column that cannot be copied back is the column for the current month. I've got #1-#2 completed, but am having problems with #3 because of one catch. The "monthly" periods run from the 21st of the previous month to the 20th of the current month. So for example, when we get to August 21st, the current system date should tell the Macro to NOT copy over the September column, but DO copy over the August column. In the Macro VB editor I've tried using many functions to do this but the data comparison's all seem to work incorrectly. For instance, when I use the [Date] function to return the current system date (8/3/2006) and compare it like the following, it evaluates True! If Date "8/29/2006" Then So it thinks 8/3/2006 is greater than 8/29/2006 because it is looking at the value, not the dates! I've tried to format it as smalldate and such but it keeps failing. Any ideas/tips/tricks you could offer would be most appreciated. Thanks! |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Help with code for Macro!
Jason
try: If Date DateSerial(2006, 8, 29) Then Regards Trevor "Jason" wrote in message ... Hello, I'm trying to create a Macro that does the following: 1. Copies the values of 12 cells from Sheet1 to Sheet2. 2. Perform an import of data from a database that will over-write the values in the 12 copied cells. 3. Paste the copied values back to their original cells on Sheet1 in 11 columns. The only column that cannot be copied back is the column for the current month. I've got #1-#2 completed, but am having problems with #3 because of one catch. The "monthly" periods run from the 21st of the previous month to the 20th of the current month. So for example, when we get to August 21st, the current system date should tell the Macro to NOT copy over the September column, but DO copy over the August column. In the Macro VB editor I've tried using many functions to do this but the data comparison's all seem to work incorrectly. For instance, when I use the [Date] function to return the current system date (8/3/2006) and compare it like the following, it evaluates True! If Date "8/29/2006" Then So it thinks 8/3/2006 is greater than 8/29/2006 because it is looking at the value, not the dates! I've tried to format it as smalldate and such but it keeps failing. Any ideas/tips/tricks you could offer would be most appreciated. Thanks! |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Help with code for Macro!
Thanks! That works great...
Jason "Trevor Shuttleworth" wrote in message ... Jason try: If Date DateSerial(2006, 8, 29) Then Regards Trevor "Jason" wrote in message ... Hello, I'm trying to create a Macro that does the following: 1. Copies the values of 12 cells from Sheet1 to Sheet2. 2. Perform an import of data from a database that will over-write the values in the 12 copied cells. 3. Paste the copied values back to their original cells on Sheet1 in 11 columns. The only column that cannot be copied back is the column for the current month. I've got #1-#2 completed, but am having problems with #3 because of one catch. The "monthly" periods run from the 21st of the previous month to the 20th of the current month. So for example, when we get to August 21st, the current system date should tell the Macro to NOT copy over the September column, but DO copy over the August column. In the Macro VB editor I've tried using many functions to do this but the data comparison's all seem to work incorrectly. For instance, when I use the [Date] function to return the current system date (8/3/2006) and compare it like the following, it evaluates True! If Date "8/29/2006" Then So it thinks 8/3/2006 is greater than 8/29/2006 because it is looking at the value, not the dates! I've tried to format it as smalldate and such but it keeps failing. Any ideas/tips/tricks you could offer would be most appreciated. Thanks! |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Help with code for Macro!
You're welcome. Thanks for the feedback
"Jason" wrote in message ... Thanks! That works great... Jason "Trevor Shuttleworth" wrote in message ... Jason try: If Date DateSerial(2006, 8, 29) Then Regards Trevor "Jason" wrote in message ... Hello, I'm trying to create a Macro that does the following: 1. Copies the values of 12 cells from Sheet1 to Sheet2. 2. Perform an import of data from a database that will over-write the values in the 12 copied cells. 3. Paste the copied values back to their original cells on Sheet1 in 11 columns. The only column that cannot be copied back is the column for the current month. I've got #1-#2 completed, but am having problems with #3 because of one catch. The "monthly" periods run from the 21st of the previous month to the 20th of the current month. So for example, when we get to August 21st, the current system date should tell the Macro to NOT copy over the September column, but DO copy over the August column. In the Macro VB editor I've tried using many functions to do this but the data comparison's all seem to work incorrectly. For instance, when I use the [Date] function to return the current system date (8/3/2006) and compare it like the following, it evaluates True! If Date "8/29/2006" Then So it thinks 8/3/2006 is greater than 8/29/2006 because it is looking at the value, not the dates! I've tried to format it as smalldate and such but it keeps failing. Any ideas/tips/tricks you could offer would be most appreciated. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting code from a macro (by a macro) | Excel Discussion (Misc queries) | |||
Macro code to remove a macro after it executes | Excel Programming | |||
read macro code by vb code | Excel Programming | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming | |||
Macro copying macro code | Excel Programming |