PDA

View Full Version : Code using date not saving the result of output



tyval
07-03-2008, 09:55 AM
I am trying to use a code to update status every friday. It works fine, the code is pulling the info on the date required. The only problem is that when the date changes, it doesn't hold the values that it was supposed to. Every friday, the number comes up where it is supposed to, but will not save or stay in the cell. The nest day, the value is returned to zero. Here is a copy of part of the code.


Dim holdDate As Date
holdDate = Date
'
If Range("B11").Value = holdDate Then
Range("B1").Select
Selection.Copy
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False,

It just repeats after that for every other day of the week. I need to have the history to track trends and such. Any help you could offer would be great.

Bob Phillips
07-03-2008, 10:09 AM
What do you mean by ... it doesn't hold the values it is supposed to.

The code you give, unnecessary selects apart, shows us nothing.

tyval
07-03-2008, 10:15 AM
Okay.. Let me explain it again. Every friday, this code takes a value from a cell, and pastes it into another.


Dim holdDate As Date
holdDate = Date
'
If Range("B11").Value = holdDate Then ' B11 is where the friday date is for the rest of the year*
Range("B1").Select 'B1 is where the sum value is located*
Selection.Copy
Range("B14").Select 'B14 is where it outputs to*
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False,

The code works great for that day. Once I go through and run all the updates on each worksheet, they are all correct. Then, the day changes, and poof... all the info is gone because it does not hold the values.

I don't really know what you would like. That is the code that I have made to run this update, after that, that same code repeats itself for the next friday, with a new output cell location.

Simon Lloyd
07-03-2008, 11:22 AM
xld is right!, it does show us nothing!, the code says that if B11 = to the date today then copy the value of B1 to B14. You say B11 is the friday date for the rest of the year - how is that achieved? really you need to supply the rest of the code! a sample workbook would be better!

tyval
07-03-2008, 12:48 PM
xld is right!, it does show us nothing!, the code says that if B11 = to the date today then copy the value of B1 to B14. You say B11 is the friday date for the rest of the year - how is that achieved? really you need to supply the rest of the code! a sample workbook would be better!

No... You misunderstood. A1 is where is date is held. If the date (b11, c11, d11.... etc) equals the date today, then it copies the value of b1 to b14, c14, d14... etc... Cell A1's date is created using =today(). As for the dates it compares to, they are created by typing in the numbers, 12/1/08, 12/8/08, etc....

Here's the ret of the code.... I am not able to post a sample worksheet right now, it links to too many tabs, and it would be too big. But, here is the rest of the code:


Dim holdDate As Date
holdDate = Date
'
If Range("B11").Value = holdDate Then
Range("B1").Select
Selection.Copy
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("C11").Value = holdDate Then
Range("B1").Select
Selection.Copy
Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("d11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("d14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("e11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("e14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("f11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("f14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("g11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("g14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("h11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("h14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("i11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("i14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("j11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("j14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("k11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("k14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("l11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("l14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("m11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("m14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("n11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("n14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("o11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("o14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("p11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("p14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("q11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("q14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("r11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("r14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("s11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("s14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("t11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("t14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("u11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("u14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("v11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("v14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("w11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("b14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("x11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("x14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("y11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("y14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("z11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("z14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("aa11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("aa14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("ab11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("ab14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("ac11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("ac14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("ad11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("ad14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
If Range("ae11").Value = holdDate Then
Range("b1").Select
Selection.Copy
Range("ae14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False, holdvalues:=True
End If
End Sub


The B1 cell is created using a sum function of the total numbers of completed items, which is added everytime a user updates the worksheet. Hopefully this will help.

Bob Phillips
07-03-2008, 01:18 PM
I still have no idea what your problem is, but the code can be simplified a tad



Dim holdDate As Date
Dim i As Long

holdDate = Date
'
For i = 2 To 31

If Cells(11, 2).Value = holdDate Then

Range("B1").Copy Cells(14, i)
End If
Next i
Range("B14:AE14").Value = Range("B14:AE14").Value

Simon Lloyd
07-03-2008, 01:28 PM
No... You misunderstood. A1 is where is date is held. If the date (b11, c11, d11.... etc) equals the date today, then it copies the value of b1 to b14, c14, d14... etc... Cell A1's date is created using =today(). As for the dates it compares to, they are created by typing in the numbers, 12/1/08, 12/8/08, etc....

The B1 cell is created using a sum function of the total numbers of completed items, which is added everytime a user updates the worksheet. Hopefully this will help.A1 doesnt pass a value to your code because you specify holdDate as Date then you reference this in your code Date is the date today! so A1 is a red herring!

You can probably replace all that code with this:

Dim holdDate As Date
Dim i As Integer
holdDate = Date
For i = 2 To 31 Step 1
If Cells(11, i).Value = holdDate Then
Cells(14, i).Value = Range("B1").Value
End If
Next i
The code above checks the value of row 11 column 2 to start with (B11) then if it is the date then row 14 column 2 (B14) will show the value of B1, if its not it will move to the next and so on until all columns up to AE have been processed. It's probably how you reach the calculation that is causing the problem

Simon Lloyd
07-03-2008, 01:29 PM
I still have no idea what your problem is, but the code can be simplified a tad



Dim holdDate As Date
Dim i As Long

holdDate = Date
'
For i = 2 To 31

If Cells(11, 2).Value = holdDate Then

Range("B1").Copy Cells(14, i)
End If
Next i
Range("B14:AE14").Value = Range("B14:AE14").Value
Bob a small Faux Pas i think as it isn't always B11, unless i've misread your code.

Bob Phillips
07-03-2008, 01:47 PM
No, but it always row 11 and row 14. i refers to the column.

Simon Lloyd
07-03-2008, 01:51 PM
Sorry Bob its just you posted If Cells(11, 2).Value = holdDate Then
instead of If Cells(11, i).Value = holdDate Then But i knew what you meant ;) , am i being too picky? i'm tired and have 'til tomorrow morning to go!

Bob Phillips
07-03-2008, 02:11 PM
Oh right, sorry, I was reading your correct verion not my error version.

Yeah thanks, a bit of a blow-out!

tyval
07-04-2008, 03:43 AM
Thanks for the help guys. I put the code in, and it works just like the one I had, just a lot shorter and easier. But, the underlying problem is that it does not hold the data in the output cell when the date changes. That is the main problem, it outputs the data into the proper cell (b14, c14, d14), but it does not save it there when the date changes. I need it to take the number from b1, when the date in b11 matches a1, and hold it there.

Bob Phillips
07-04-2008, 07:44 AM
You keep saying that, but I understand it no more than when you first said it. You either need to explain it better, or wait to someone who thinks like you comes along because I still have no idea what the problem is, and just repeating the same words is not going to make it any clearer.

Simon Lloyd
07-04-2008, 11:29 AM
I need it to take the number from b1, when the date in b11 matches a1, and hold it there.Your code has nothing whatsoever to do with the value of A1!!!!!!

Simon Lloyd
07-04-2008, 11:31 AM
Dim i As Integer
For i = 2 To 31 Step 1
If Cells(11, i).Value = Range("A1").Value Then
Cells(14, i).Value = Range("B1").Value
End If
Next i this references A1