Macro For Different Cell Depending on the Date

MCLARK1

New Member
Joined
Mar 20, 2008
Messages
30
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,124
can you elaborate on the layout of your data -- ie where are the date dependent cells housed
 
Upvote 0

MCLARK1

New Member
Joined
Mar 20, 2008
Messages
30
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

exc.jpg
 
Last edited:
Upvote 0

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,124
OK couple more things... in which rows/columns do the dates appear and presumably they are entered as date value
 
Upvote 0

MCLARK1

New Member
Joined
Mar 20, 2008
Messages
30
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
 
Upvote 0

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,124
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.

Code:
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

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

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

Code:
Private Sub CommandButton3_Click()
Call CB_ROUTINE(CommandButton3.Caption)
End Sub
 
Upvote 0

MCLARK1

New Member
Joined
Mar 20, 2008
Messages
30
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
 
Last edited:
Upvote 0

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,124
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:

Code:
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...
 
Upvote 0

MCLARK1

New Member
Joined
Mar 20, 2008
Messages
30
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,191,165
Messages
5,985,034
Members
439,935
Latest member
Monty238

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top