VBA loop - dates plus 1

[relahni]

Hi,

I'm very new to VBA coding and have relied on macro recording for the code below.

There are three dates in at workbook called "DATES" located in Cells B1:D1 - the code copies those dates and pastes them into a file called "DATA" in cells EU1:EW1

The file called DATA crunches the numbers and pastes to the last available row in a file called "RESULTS"

What I want to do is loop this process - but - add a day to the cells in B1:D1.

For example, if B1 = 25/10/2021 and C1 = 25/10/2020 and D1 = 24/10/2021

I want the next loop to be B1 = 26/10/2021 and C1 to be 26/10/2020 and D1 = 25/10/2021

Then it takes these dates, sends them to get crunched in DATA file then it pastes the data returned into the RESULTS file.

And it repeats this process on and on.

As a work around I am simply copying and pasting - the code below but changing the range to the row below (where I have inputted the dates required).

However, as a VBA rookie, I am interested in learning how I can loop this process to add a day on to the initial dates and repeat this process. All responses very gratefully recieved.

Code below

Range("B1:D1").Select
Selection.Copy
Windows("DATA).xlsx").Activate

Range("EU1:EW1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A755:EK755").Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlNormal
Windows("Results.xlsx").Activate
ActiveWindow.SmallScroll Down:=119
Range("A1").End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("DATES.xlsm").Activate

 

[Joe4]

Welcome to the Board!

You can get and store your date in a variable, like this:

Dim dt1 as Date
dt1 = Range("B1").Value

Then, let's say that you wanted to loop 10 times through, adding 1 each time.
You could do that like this (this example simply shows the value of the date, and then adds one each time):

Dim i as Long
For i = 1 to 10
    MsgBox "Current date value is: " & dt1
    dt1 = dt1 + 1
Next i

Hopefully, this shows you how to do what you want.
If you have more questions about it, please let us know.

 

[relahni]

Welcome to the Board!

You can get and store your date in a variable, like this:

Dim dt1 as Date
dt1 = Range("B1").Value

Then, let's say that you wanted to loop 10 times through, adding 1 each time.
You could do that like this (this example simply shows the value of the date, and then adds one each time):

Dim i as Long
For i = 1 to 10
    MsgBox "Current date value is: " & dt1
    dt1 = dt1 + 1
Next i

Hopefully, this shows you how to do what you want.
If you have more questions about it, please let us know.

Thanks Joel.

That is very helpful, but how can I encorporate that into my code?

 

[Joe4]

Thanks Joel.

That is very helpful, but how can I encorporate that into my code?

Not sure who "Joel" is, but you would need to provide us with a little more information, such as, what controls how many times we should loop?

 

[relahni]

Not sure who "Joel" is, but you would need to provide us with a little more information, such as, what controls how many times we should loop?

So sorry, Joe! I have recently had my eyes tested as well. Apologies.

The number of times it loops could be in a cell - A1 in the DATES file. Thanks again for the response and thanks very much for the warm welcome. I find VBA codes fascinating but as a newbie, I am a bit in the dark.

 

[Joe4]

The number of times it loops could be in a cell - A1 in the DATES file. Thanks again for the response and thanks very much for the warm welcome. I find VBA codes fascinating but as a newbie, I am a bit in the dark.

You could capture that number in a variable, like this:

Dim n as Long
Windows("DATA).xlsx").Activate
n = Range("A1").Value

You can then loop using that variable later in your code, i.e.

Dim i as Long
For i = 1 to n
    MsgBox "Current date value is: " & dt1
    dt1 = dt1 + 1
Next i

(This just shows the progression of dates as the loop changes, for demonstration purposes. Obviously, you will want to replace the MsgBox with your code).