VBA solution for a tally sheet, add 1 to a cell based on current date

Burrgogi

Active Member
Joined
Nov 3, 2005
Messages
443
Office Version
  1. 2010
Platform
  1. Windows
I've got a simple tally sheet to count my FreeCell wins & losses. There are just 2 sheets in the workbook. The first one (we'll just call it "Sheet1") shows me the win percentage. I'm having some problems with the 2nd one ("Daily Count"). Here's what that 2nd sheet looks like:

Excel screenshot 3.png


I've got numbers 1 - 31 in column A representing the day of each month (starting at row 3). Up to now I have been manually adding 1 to the corresponding cell. For example today is the 27th of the month, if I win the next game, cell B29 would change from 1 to 2. It's gotten rather tedious to do by hand so I've put together a VBA routine to automate this. I'm not a programmer by any means so I did some 'google' searching. Through trial & error I've pieced together following routine. I need 2 things.

I've created 2 macros (wins & losses accordingly). The code below is for my wins.

1) The code currently changes the value in column B. That's fine because I'm dealing with April stats for now, but I would like the code to evaluate the current month and change accordingly. As you can see from my screenshot, I have the upcoming months next to April all the way through the end of the year.

2) I would like the loss column ("L") to display a zero if it is blank. Using today as an example, I would like cell C29 to display a ZERO when I run the macro. BUT ONLY if it is blank. Again, it won't always be cell C29 - it needs to change dynamically based on the current month.

Actually 1 last request.... I would like change ActiveSheet.Range("B2") in line 4 to Sheet1.Range("B2"). For some strange reason when I made the change, Excel threw up an error.

VBA Code:
Sub Freecell_WIN_v2()
Dim theDay As Integer
theDay = Day(Date)
Dim Fnd As Range
ActiveSheet.Range("B2") = ActiveSheet.Range("B2").Value + 1
Set rRange = Sheets("Daily Count").Range("A3:A33")
    Set Fnd = rRange.Find(What:=theDay, LookIn:=xlValues, Lookat:=xlWhole)
    If Fnd Is Nothing Then
        MsgBox "Can't find today's date!"
        Exit Sub
    End If
    Fnd.Offset(0, 1).Value = Value + 1
End Sub
 
Correct in the previous image, the first name you see in the properties window (and the objects window) is the code name.
You can effectively only change that in Developer mode and the user doesn't see that name. One of the main reasons for using it is that if the user changes the sheet name, the code name stays unchanged and the routine will not be impacted.
If you are going to use it please change it to something meaningful. The code name only works if you are referring to a sheet that is in the same workbook that holds the code.

A light bulb went off in my head after reading this. This is very helpful to know. Thanks!
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

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
Back
Top