Excel: Update data in exact location

[deltaair]

I have a file that processes data for the current week, and has a set of numbers, say two columns, one with date and one with the number.

However, every Monday and every Thursday, I have to use this data processing file and copy the numbers for this week and paste them in an archive Excel Sheet, which has these numbers from the beginning of the year. So I tried to record a Macro, that "Finds" the Current Date in the column of dates, but I can't successfuly do so.

I need a function or a Macro or any VB Code, that "Finds" the cell, next to the cell where the "Current Date" is found , and pastes information over there.

p.s. - I could do a VLOOKUP, but that doesn't work because the location of the cell always changes. Moreover the Monday and Thursday causes a problem.

 

[SkipVought]

Hi,

How are you recording your dates?

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[deltaair]

The date is just recorded in the cell as -> Format(Today(),"dd-mmm-yy"), and the next 6 cells are incremented from that cell.

The problem isn't really the date, its finding the date and placing the data right there.

 

[AnotherHiggins]

Format is not an Excel function. Are you using
=[red]TEXT[/red](Today(),"dd-mmm-yy")
instead? Or was it determined by "Format(Today(),"dd-mmm-yy")" in another application prior to being imported into excel?

What Skip is getting at is that how the 'date' is stored in Excel matters. It could be a number (38691 for today) or a text string. Either will be displayed to LOOK like today's date, but they are treated differently by Excel.

Also: "The problem [is] finding the date and placing the data right there." Do you want to overwrite the date with whatever data is found, or do you want to place the found data next to the date?

Lastly, you said that "I tried to record a Macro, that "Finds" the Current Date in the column of dates, but I can't successfuly do so." Please provide the code that was generated. To be quite honest, I can't tell exactly what it is you want to do by reading your post. Posting the code, and any additional info you can provide will help us to figure out what you want.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Format(Today(),"dd-mmm-yy")

is not a spreadsheet formula???

I have a list of REAL DATES.

in D1 I have =TODAY()

in E1 I have =Match(D1,MyDateRange,0)

It returns the relative row containing the current date.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[AnotherHiggins]

I beat ya, Skip!!!

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Geeeeez, must be loosin' my touch, John! ;-)

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[deltaair]

Clarification Sorry I must have gotten it confused, it is stored as Text(Today(),"dd-mmm-yyyy")

The problem is more properly defined as this...

I have an archive of dates and numbers that is updated for a week in advance on every Monday and Thursday. Say today is Monday - 5th Dec, so it has information from the 1st January till the (5th + 6) = 11th Dec. Now come Thursday, on the 8th December, I will have information from the 8th through the 14th December.

Now I thought, I could just add the information at the top and sort it by date, to make sure the data is ok... However, there is overlap of 8th through 11th december, when I add the new information. So I don't know how to get rid of that. So I need an efficient way of just updating this archive.

Referring to the previous post, I thought if a macro could find the current date, then according to the above example I could just paste the information at 8th December (Thursday's date), so I needed to paste the rows at that exact same location.

If you guys can figure out any other way of doing this I would appreciate it.

 

[SkipVought]

The date is just recorded in the cell as -> Format(Today(),"dd-mmm-yy"), and the next 6 cells are incremented from that cell.

WARNING:
[tt]
Text(Today(),"dd-mmm-yyyy")
[/tt]
does NOT return a real date that can be manipultated by date functions, lookups and other calculations. However, Excel is "smart" enough to determine that the FIRST TEXT STRING is a date and when you "incriment from that cell," Excel "CONVERTS" the text string to a real date and proceeds.

Here's the clincher -- you can CHANGE the Date Format of the following 6 cells via Format/Cells... BUT you cannot change the format of the FIRST cell.

Solution: Change the expression in the FIRST cell to
[tt]
=TODAY()
[/tt]
and FORMAT the cell as desired.


Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}