Have a cell count

Ahhhh

New Member
Joined
May 31, 2015
Messages
29
Hi all,
Alright, so I know this will take VBA.
I would like for cell A1 to add 1 (to count) the number of times a word (yes) appears in cell a2.
Example: cell A1 = 0, A2 = yes then A1 = 1, A2 = no then A1 = 1, A2 = yes then A1 =2
Any help would be appreciated
Ahhhhh, this is driving me crazy
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try putting the code below in the worksheet module (Right click your sheet tab, click view code, post the code in the window that appears).

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then
If UCase(Target.Value) = UCase("YES") Then Range("A1").Value = Range("A1").Value + 1
End If

End Sub
 
Upvote 0
Try putting the code below in the worksheet module (Right click your sheet tab, click view code, post the code in the window that appears).

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then
If UCase(Target.Value) = UCase("YES") Then Range("A1").Value = Range("A1").Value + 1
End If

End Sub

OK, that worked great in a new worksheet, but when I put it into my project it does not work. I have other things going on (a VBA countdown timer) perhaps that why.
Any Ideas?
 
Upvote 0
Any Ideas?


Not without you posting the code (preferably within code tags) and you stating if you got any error messages and if yes what they said.
 
Upvote 0
Not without you posting the code (preferably within code tags) and you stating if you got any error messages and if yes what they said.

Here is the other code
its part of a userform

Code:
Private Sub CommandButton1_Click()Dim T, E, M As Double, S As Double


T = Timer
Do
    E = CDbl(Time) * 24 * 60 * 60 - T
    M = AllowedTime - 1 - Int(E / 60)
    S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)
    
    With tBx1
        .Value = Format(CStr(M), "00") & ":" & Format(CStr(S), "00")
    End With
    DoEvents
Loop Until (Timer - T) / 60 >= AllowedTime
End Sub






Private Sub poker_Initialize()
Dim M As Double, S As Double
M = Int(AllowedTime)
S = (AllowedTime - Int(AllowedTime)) * 60
 With tBx1
    .Value = Format(CStr(M), "00") & ":" & Format(CStr(S), "00")
End With
End Sub



No error comes up,
I can send you the worksheet it that helps
Thanks
 
Upvote 0
I can't see anything obvious in the code that would stop the code working, if you upload your workbook to Box.com (mark it for sharing) then post the link it produces back in the thread I'll take a look.
 
Upvote 0
so basically when the countdown timer reaches 00:00, a what if statement puts yes into the cell, that is what the
Code:
[COLOR=#333333][I]Private Sub Worksheet_Change(ByVal Target As Range)[/I][/COLOR]
If Target.Address = "$A$2" Then
If UCase(Target.Value) = UCase("YES") Then Range("A1").Value = Range("A1").Value + 1
End If
 [COLOR=#333333][I]End Sub[/I][/COLOR]
pulls from
Thanks
 
Upvote 0
First of all the bits in the code that were in capitals needed to remain in capitals and so your code with your actual ranges needed to be

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$29" Then
If UCase(Target.Value) = UCase("YES") Then Range("A28").Value = Range("A28").Value + 1
End If

End Sub

Then it would have worked if you were manually changing A29 but you are not!, you are trying to get a macro to run at the change of the formula result.

I'm afraid that won't trigger a macro, instead try making the change in red to your main code instead.

Code:
Private Sub CommandButton1_Click()
    Dim T, E, M As Double, S As Double

    T = Timer
    Do
        E = CDbl(Time) * 24 * 60 * 60 - T
        M = AllowedTime - 1 - Int(E / 60)
        S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)

        With tBx1
            .Value = Format(CStr(M), "00") & ":" & Format(CStr(S), "00")
        End With
        DoEvents
        [COLOR="#FF0000"]If (Timer - T) / 60 = AllowedTime Then Range("A28").Value = Range("A28").Value + 1[/COLOR]
    Loop Until (Timer - T) / 60 >= AllowedTime
End Sub

By the way just to let you know that the way you have dimmed your variables T and E are variants not doubles.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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