Macro For Different Cell Depending on the Date

[MCLARK1]

Hi All,

Iam new to VBA and have run into a few problems when trying to write a small code.

Baisically what Iam trying to do is when a user click's on a button, a macro adds the value of 1 to whatever number is in the cell. This is what I have come upwith that seems to work:

Range("u43").Select
ActiveCell.Value = ActiveCell.Text + 1
MsgBox "You have added 1 to your score"

What I want to do though is make it date dependant so for example when a user clicks the button to add 1, it adds it to a different cell for each day of the month.

I have used the =TODAY() and the =DAY(S37) to display the day of the month as I thought I could get the macro to read this cell with the day of the month so it knows which cell to add it to.

I cannot seem to get any further than this and would really appricate it if someone could assit me?

Thanks In Advance
Marc

 

[DonkeyOte]

can you elaborate on the layout of your data -- ie where are the date dependent cells housed

 

[MCLARK1]

Sure, Ive got the following layout:

So you would click Burger for example. If its the 02 Jun it would add 1 to the 02 jun coloum if it was 03 jun it would add 1 to the 03 jun coloum and so on.

Please let me know if you need any more info?

Thanks In Advance
Marc

 

[DonkeyOte]

OK couple more things... in which rows/columns do the dates appear and presumably they are entered as date value

 

[MCLARK1]

Sure, Ok. The date's are in date value and are in U40 through to AY40 to take into account 31 Days of the month.

Thanks
Marc

 

[DonkeyOte]

Presumably you are going to need a routine for each command button ?
In which case I would go along the lines of making one *common* routine which is invoked by all command button routines parsing the caption of the command button to the main routine... command button does not have a linked cell so you need to use a select case based on the caption name to determine correct row (though you could type the caption in the cell *underneath* the button) and use a MATCH function to find row rather than having to write out the SELECT CASE for each option -- this is up to you of course.

However, hopefully the below will help.

Obviously if your command bars aren't called CommandButton1,2,3 etc you must change those references accordingly.

Private Sub CB_ROUTINE(cb_caption As String)
Dim co As Long: co = Application.WorksheetFunction.Match(CLng(Date), ActiveSheet.Rows(40), 0)
Dim rw As Long
Select Case UCase(cb_caption)
    Case "BURGER"
        rw = 41
    Case "CHIPS"
        rw = 42
    Case "TEST"
        rw = 43
End Select
Cells(rw, co) = Cells(rw, co) + 1
End Sub

Private Sub CommandButton1_Click()
Call CB_ROUTINE(CommandButton1.Caption)
End Sub

Private Sub CommandButton2_Click()
Call CB_ROUTINE(CommandButton2.Caption)
End Sub

Private Sub CommandButton3_Click()
Call CB_ROUTINE(CommandButton3.Caption)
End Sub

 

[MCLARK1]

Hi,

That looks great. Thanks for that.

Could you confirm how I would need to enter this into excel.

Do I just need to create a new macro and copy and paste all into one macro?

Currently I have 3 Macros set up like below and assigned each button to each macro:

Sub burger()
'
' Macro2 Macro
' Macro recorded 26/06/2008 by Marc
'
Range("u43").Select
ActiveCell.Value = ActiveCell.Text + 1
MsgBox "You have added 1 to your score"
'
End Sub


Thanks In Advance
Marc

 

[DonkeyOte]

OK, it sounds as though you're using Form Buttons as opposed to ControlToolBox Buttons (my example).

You will need to create macros for each button:

Private Sub BURGER_ROUTINE()
Dim co As Long: co = Application.WorksheetFunction.Match(CLng(Date), ActiveSheet.Rows(40), 0)
Dim rw As Long: rw = 41
Cells(rw, co) = Cells(rw, co) + 1
End Sub

repeating the above for CHIPS & TEST, however, be sure to change rw from rw = 41 to rw = 42 for CHIPS routine and rw = 43 for TEST routine and so on...

 

[MCLARK1]

Hi,

That works great thanks.

Thanks for all your help on this one.

Regards
Marc

 

[MCLARK1]

Hi, I need to adapt or use the above to show information contained in certain cells based on the date.

For example,
A B C
1 10 11 12
2 00 00 00
3 00 00 00

Say I have todays date on col A. With the script above it will add 1 too it when the macro is run.

What I wanted to try and do aswell is display the value in Col A in another cell. The row (A,B,C) Changing depending on the date so say A is todays date, in cell A22 display 10. And if its B Tomorrows date, display the value 11 in cell A22 and so on?

Any help would be greatly appreciated?

Thanks In Advance
Marc