[RESOLVED] Weather Recording help

[Laser1984]

I'm new to working with Visual Basic and macros and would like some help getting an excel spreadsheet I have setup and working, so I can make a growing degree day chart for work to better time applications made to the golf course.

The existing excel sheet is setup with a weather underground api key to update my worksheet with real-time current weather conditions and the 10-day forecast. (The history function of weather underground api doesn't work for some reason.) I would like for the 10-day forecast to be recorded so that once the day, for example today March 22, 2018, has come and gone it records the date, daytime high temperature, nighttime low temperature, and conditions and saves it for a year.

My worksheet "Current Conditions" contains the 10-day forecast in range M7:P16 and I would like to start a new worksheet "Annual Weather Summary" to record the conditions for each day after they have past.

Hope my description of what I'm shooting for makes sense, any help would be greatly appreciated.

Thanks!

[westconn1]

you could automate the saving of the data using application.ontime so that the recorded data is saved to history at the same time every day, this assumes that the workbook is always open and the computer always on, it can still work (or reset) when the workbook is opened if that is the required case

from your description i would think you need to copy M7:P16 to some range in a new row in a different sheet /workbook? would that be on a single row or how do you want the format of the annual worksheet?

[Laser1984]

The workbook may not be open at all times, however once complete it will be opened if not left open everyday. Currently in my "Current Conditions" worksheet I have the "Date" in column M, "Daytime High" in column N, "Nighttime Low" in column O, and "Conditions" in column P. Ideally I would like for the 10-day forecast to record past/forecasted weather to my "Annual Weather Summary" sheet, with each date and forecast in its own row using columns A-D.
For example, if the entire 10-day forecast can be transferred to that sheet and have days 2-10 update as the weather forecast changes that would be great, but if it is only possible to record day 1 of 10 to the sheet I can make it work with that as well. I would like for this data to be saved for a year and once the same date the following year comes around it will replace that information with the old.
Thanks

[westconn1]

have days 2-10 update as the weather forecast changes that would be great

so each day you copy 10 rows to the annual sheet which would overwrite the future 9 days from the previous day forecast, that should be easy enough to do

set fnd = sheets("annual").range("a:a").find(date)     ' find todays date, should already be in the sheet from yesterdays data
fnd.resize(10,4).value = sheets("current").range("m2:p12")   ' copy the 10 days

this is just typed in the browser, so may contain typos or code errors and has not been tested at all
for the first use, if todays date is not already in the annual sheet, you will need to add it
some modification will be required for the last 10 days of the year as some of the future data will need to be back at the top of the sheet, i will look at that later, will the sheet be january to december? or other?

once the same date the following year comes around it will replace that information with the old.

is that correct or should it replace with the new data from today?

if i have misunderstood your requirement, post a sample workbook (zip first) to test with

[Laser1984]

Correct, each day I would like to copy the 10-day forecast to the annual sheet and overwrite the previous 9 days. The annual sheet will be January 1 - December 31.

I would like the annual sheet to only be a years worth of data so when a new year comes along it will replace the weather data from March 23, 2018 to the new data for March 23, 2019.

When I get home I will try what you have provided so far and I can then post a sample workbook to test with if you would like.

Thanks

[westconn1]

then post a sample workbook to test with

makes it so much easier, to test what works

[Laser1984]

Here is a sample
Thanks

[westconn1]

mydate = Date '  could also be cell M7
    Set fnd = Sheets("annual weather summary").Range("a:a").Find(mydate)   
    eoy = DateSerial(Year(Now), 12, 31) - mydate + 1
    If Not eoy < 10 Then
    fnd.Resize(10, 4).Value = Sheets("current conditions").Range("m7:p16").Value ' copy the 10 days
    Else
    fnd.Resize(eoy, 4).Value = Sheets("current conditions").Range("m7").Resize(eoy, 4).Value
    Sheets("annual weather summary").Range("a2").Resize(10 - eoy, 4).Value = Sheets("current conditions").Range("m7").Offset(eoy).Resize(10 - eoy, 4).Value
End If

dimension variables to suit
this is marginally tested, and does not take into account leap years, though a leap year will automatically be added in by overwriting the next date, you would end up with second line of dec 31 on following years

[Laser1984]

You're the best!
Thanks