PDA

View Full Version : Solved: Date as an Index



Steve538
07-22-2010, 11:59 AM
I'm trying to write a macro to copy one column of data to another column indexed by date.

what I have so far is:

j = cells(4,1) - "07/21/2010" ' A4 contains "=TODAY()"
k = j + 11

I also tried DATEDIF("07/21/2010", Cells(4, 1), "d")

I get errors when running the macro.

I'm hoping to get a number "j" that is the number of days from "07/21/2010". If I put the formula "=A4 - "07/21/2010" in a cell
I do get the number of days.

austenr
07-22-2010, 12:10 PM
Please post the entire macro.

Steve538
07-22-2010, 12:22 PM
It never gets past the number of days cal (j =). The rest is just moving data, and I'm still writting that part.

gcomyn
07-22-2010, 01:32 PM
Ok... I tested what code you have... and you are trying to subtract a string from a date... if you use


j = Cells(4, 1) - CDate("07/21/2010") ' A4 contains "=TODAY()"


it works, returning 1 for j and 12 for k

GComyn
:sleuth:

Steve538
07-22-2010, 01:52 PM
Thanks for the solution. When I tested in a cell I didn't need the CDate
function.

gcomyn
07-22-2010, 01:57 PM
yeah... within a cell, Excel will automatically check the string, sees that it is a date, then transforms it...

BTW... will that date always be 07/21/2010?? If not, you should think about putting it in a cell somewhere (hidden on the same sheet, or on another sheet) so that you can change it if necessary, without having to go to the code... then, say you put it in cell AA1, the code would look like:


j = Cells(4, 1) - cells(1,27)


then you can change that arbritrary date anytime needed.

GComyn
:sleuth:

Steve538
07-22-2010, 02:16 PM
Thanks for the suggestion. I was planning to do as I refined my macro. I wanted to keep it simple to help in troubleshooting (I guess by using "" instead CDate"" I didn't make it any easier).

Again Thanks for the help.

austenr
07-30-2010, 06:55 AM
If your issue is solved please mark it as such by using the Thread Tools.

Steve538
07-30-2010, 07:19 AM
Thanks for the Heads-up. Marked.