Newbie in crisis..Excel/VBA coders, please help!

[soulytrack]

Hi everyone,

Complete noob when it comes to excel/vba commands, but I absolutely need to figure this out.

Here's the layout :

Column A has dates from July 1st 2011 down to March 23rd 2014 (A4:A1000).

Column B is empty. Will remain this way.

Column C, from C4 to C1000, has a default value of "0". These are the numbers of calls in the day.

Here's what I need to do :
Everytime I receive a call, I want to increment that number in today's corresponding cell (in the C column) by 1 with the press of a button.

I've figured out how to add a button and add a macro with a VBA script I found through my numerous google searches.

Sub Plus()
 With ActiveSheet.Range("C7")
    .Value = .Value + 1
  End With
End Sub

This only works for one specific cell (C7, in this instance). I don't want to have to modify the macro every day, so I was wondering :

How can I ...

• Make Excel select the current date's cell in column C
• Increment the number by 1 after every click of the button in the right column, according to the date

Thank you so much in advance, you guys are life savers.

P.S. If you're having trouble following my conditions, let me know and I'll gladly clear it up.

 

[jeffreybrown]

How about....

Sub FindToday()
    Dim Found  As Range
    Set Found = Columns("A").Find(what:=Date, LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        Range("C" & Found.Row).Value = Range("C" & Found.Row).Value + 1
    End If
End Sub

 

[soulytrack]

How about....

Sub FindToday()
    Dim Found  As Range
    Set Found = Columns("A").Find(what:=Date, LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        Range("C" & Found.Row).Value = Range("C" & Found.Row).Value + 1
    End If
End Sub

Thanks for the quick reply!

I just copy/pasted and applied your code to my current "+" button, slight problem : it does nothing.

I'm guessing that's due to the " If Not Found Is Nothing Then" line?

Why wouldn't it find the date, could it be a formatting issue? Am I supposed to edit the "Find(what:=Date," section?

Again, im still new to all of this...so walkthroughs are heavily appreciated.

Thank you very much though...i feel like I'm getting so close to accomplishing this.

 

[jeffreybrown]

Often the best way to analyze code is to use the F8 button within the VBE environment so you can step through line-by-line and see what is happening.

Here is how I setup my sheet. A4:C10 just for simplicity. Right above A4 there is a button which I will press for today's call's and as you can see it registered 9 since I pressed the button 9 times.

The button is a forms control button, not an ActiveX button. The macro is in a module.

<TABLE style="WIDTH: 162pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=216><COLGROUP><COL style="WIDTH: 54pt" span=3 width=72><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=72 align=right>7/2/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=72> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=72>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 align=right>7/3/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 align=right>7/4/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>9</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 align=right>7/5/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 align=right>7/6/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 align=right>7/7/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 align=right>7/8/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD></TR></TBODY></TABLE>

How is your setup different than the above? Again, maybe step through the code and see if you find a date.

 

[soulytrack]

The setup isn't much more different than yours, I actually just recreated the example again in a fresh new spreadsheet, and I'm still not getting anything.

2 things :

- My excel version is in French. I'm at work and this is what we use..sadly.
- VBA is in french, but the coding is still in English, thankfully.

I tried your code again, no success. It didn't do anything.

I DID, for some random reason,

replace Find(what:=Date,

by

Find(what:=Today,

and it would actually increment the number by 1! Only problem was that it was in the wrong row (always ends up in C2 for whatever reason).

The button I added is a forms control as well, so no issue there.

If you're using the code as is, as you sent me, it should technically work. Only thing I'm wondering is how are your dates formatted within Excel, and would that impact the results of the search?

As for the F8 function in VB...all it does is highlight in yellow. I have no idea what I'm supposed to be doing with it.

Again, thanks for all your help.