Adding a value based on time

[siddharth_kale]

Hi,

I would like to add a value of 7.5 every 15 days to a column with 30 rows.

Please let me know if it is possible.

Regards,
Siddharth

 

[MisterBate5]

Most things are possible, but right now you may not have given us enough information about your data - what's in the rows? how do you determine start date?

Maybe you could provide a little more (or even use one of the tools available to post a small snapshot of your excel into the forum)?

 

[siddharth_kale]

hey,

thanks for replying. I basically want to increment the values in a column by 7.5 every 15days starting on the 1st.

<TABLE style="WIDTH: 68pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=91><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20 width=91>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 height=20>270</TD></TR></TBODY></TABLE>

 

[siddharth_kale]

Hi,

would like to know if this would help in achieving the above:

Sub Up_One()
If (Day(Now) Mod 1) = 0 Then
Range("B2").Value = Range("B2").Value + 7.5
Range("B3").Value = Range("B3").Value + 7.5
Range("B4").Value = Range("B4").Value + 7.5
End If
End Sub

check current system date and run after 1 day, and increment the value of ranges B2:B4 by 7.5.

I would also like to know if the scheduled macro would run even if the workbook is closed?

Regards,
Siddharth

 

[MisterBate5]

Scheduled macros ... never heard of such a thing. Macros can only work when the workbook containing them is open. Which means your solution must not depend on being scheduled or that the workbook will be opened every day (or at least on every 15th day)...

Where do the values come from? Are they pulled from another file/DB?

One thought from my side is to "fix" the start date, then calculate (in the sheet or in a macro) the difference between now and start date, rounded down, and add multiples of 7.5 based on the result.