|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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!
|
|
Jason
try:
If Date > DateSerial(2006, 8, 29) Then
Regards
Trevor
"Jason" <jason[ at ]nospam.com> wrote in message
news:%23D3PUa0tGHA.2224[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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!
>
>
|
|
Thanks! That works great...
Jason
"Trevor Shuttleworth" <Trevor[ at ]Shucks.demon.co.uk> wrote in message
news:OAWTIu4tGHA.2224[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Jason
>
> try:
>
> If Date > DateSerial(2006, 8, 29) Then
>
> Regards
>
> Trevor
>
>
> "Jason" <jason[ at ]nospam.com> wrote in message
> news:%23D3PUa0tGHA.2224[ at ]TK2MSFTNGP06.phx.gbl...
>> 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!
>>
>>
>
>
|
|
You're welcome. Thanks for the feedback
"Jason" <jason[ at ]nospam.com> wrote in message
news:eNqjA78tGHA.4336[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Thanks! That works great...
>
> Jason
> "Trevor Shuttleworth" <Trevor[ at ]Shucks.demon.co.uk> wrote in message
> news:OAWTIu4tGHA.2224[ at ]TK2MSFTNGP06.phx.gbl...
>> Jason
>>
>> try:
>>
>> If Date > DateSerial(2006, 8, 29) Then
>>
>> Regards
>>
>> Trevor
>>
>>
>> "Jason" <jason[ at ]nospam.com> wrote in message
>> news:%23D3PUa0tGHA.2224[ at ]TK2MSFTNGP06.phx.gbl...
>>> 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!
>>>
>>>
>>
>>
>
>
|
|
|