What you're asking for can be accomplished without a macro.In Cell A2, you'd
have your starting date (or the date for the first record).Then in Cell A3, put:
=IF(B3="","",IF(B3=0,A2+1,A2))and drag it down the column.
Basically, this makes use of the fact that Excel stores dates as numbers.The
number of days since 1/1/1900.
So, to day (August 13, 2015) is 42229
That's why you can change the display format to show the date in any way you
want.8/13/1513-Aug-2015Aug-20152015-08-132015Thursday
So, basically adding (1) to any number is like adding 1 day to a dateAugust 12,
2015 (42228) + 1 = 42229 = August 13, 2015
The other thing is that Excel stores TIME as a fractional part of a day.6:00am
is 6/24 of a day or .25 daysso, today at 6:00am is 42229.25
At this exact second:8/13/2015 at 1:55:47, Excel stores ad 42229.5804053241
So, in the function, If(B3=0,is testing to see if the time is midnight (0:00)If
it is, then it adds 1 to the previous date.If not, then it copies the previous
date.
the nested IF statement portion:IF(B3="","",
simply checks to see if the time value is blank.If it is, then it puts a blank
in the date cell.
------------
Now, if you want a macro to REALLY automate the collection,then you can write a
macro that loops through all of the .csvfiles in the folder, reads them and
enters the data.(I'm assuming the filename has the date?)
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Ian Truslove <truslo...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Thursday, August 13, 2015 11:36 AM
Subject: $$Excel-Macros$$ Macro from non specific cell
Hi
I have a monitor which records in 10 minute intervals the output from my
rooftop solar panels.
I transfer the daily data to an Excel 365 day ‘Master Sheet’. Unfortunately,
the monitor supplies a CSV (read only file) which ONLY contains two columns
with 10 minute intervals and kWh output.
13:00 1.44
13:10 1.896
13:20 1.926
13:30 2.148
13:40 2.106
13:50 2.106
I copy and paste the data into columns ‘B’ and ‘C’ and then add the relevant
date into column ‘A’ 144 times.
I would like to add a macro which takes the date in the LAST DATE CELL, (in the
attached sheet A145) and increment it by one day. I would then like the macro
to paste the same date into the next 144 cells.
I’ve created a macro which does this, but it only works on the specific cell
that was originally selected (A2) rather than carrying on from the new required
cell (A146).
I’m using Excel for Mac 2011.
Any help or suggestions would be much appreciated.
Best regards
Ian--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
