Decrease a value by 1 every week

[init4themoney]

Hi,

I currently have a workbook with 30 or so rows of data (constantly increasing) and in one of the columns there would be a value between 1 and 35. Every Tuesday I have to decrease each one of those by 1 and as you can probably guess, it gets quite tedious after a while.

So I was wondering if there would be a formula that would automatically do this for me every Tuesday..?

(Visual explanation)

Week 1:

A1
7
6
6
8
12
20

Week 2:
A1
6
5
5
7
11
19

Would save me a lot of time!

Cheers

 

[lenze]

Hi and welcome to the Board!!
What is the date of your 1st Tuesday?? To establish the 1st week!
What happens when a value gets to 1? Where do the values come from and how are then entered?

lenze

 

[init4themoney]

Thanks!

Ermmm, I think it's going to be quite hard for me to explain, so I've linked the file in the link below (sorry if this isn't the correct way to upload file's)

http://www.box.net/shared/kntj9illvn

You need to go on the "youth overview" tab. Look at the "D" Column (named Bloom sessions). You'll see the values.

Every Tuesday I want it to decrease by 1. Once it reaches 0 I'll just delete the corresponding row manually.

That's all really...

Hope this gives you a better idea.

 

[lenze]

Would a macro work for you that you can run every "Tuesday"?

lenze

 

[init4themoney]

Yeah, if it does it's job then that would be great!

Not that clued up on macros though... :D.

 

[lenze]

Sub Test()
Dim cl As Range
Dim LR As Long
LR = Cells(Rows.Count, "D").End(xlUp).Row
On Error Resume Next
For i = LR To 2 Step -1
Set cl = Cells(i, "D")
cl = cl - 1
If cl = 0 Then
   rspn = MsgBox(Cells(cl.Row, "A") & " has reached zero." & Chr(10) & "Do you wish to delete the record?", vbYesNo)
   If rspn = vbYes Then cl.EntireRow.Delete
End If
Next i
End Sub

In a regular module. Open the VBE(ALT+F11) and choose Insert>Module
If needed, you could flag a cell when the macro is run so you don't forget and run it twice in one week!!

lenze

 

[init4themoney]

I've inserted it, but what do I then?

(As said before, don't really know what to do with VBA, etc...)

Cheers

 

[lenze]

With the sheet active, Open the Macro dialog (ALT+F8) and select the macro. Click Run
IMPORTANT: DO THIS ON A COPY OF YOUR WORKBOOK UNTIL YOU SEE IF IT DOES WHAT YOU NEED

lenze

 

[init4themoney]

Ahhhhhh, perfect! :D.

Thank you so much! I've been looking around for an answer to that problem for quite a while now.

I'm slowly getting better and better, and I hope this site can help me progress!

Daniel.