VBA counting help

[Captain Sensible]

Can any of the vba coders see whats wrong with the code below ?

I had it working OK on my PC then it decided to stop once I'd pasted it in another sheet just using Alt F11, is there anything I need to do to get VBA code working when placed into new sheets - previously I've just copied them straight in and they run ok. #

Basically it's meant to add 1 to C7 everytime C5 changes to 1.

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [C5].Value = MyMarket Then
GoTo Xit
Else
If [C5].Value = 1 Then
mycount = [C7].Value + 1
[C7].Value = mycount
End If
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[GeorgeUK]

Have you given a value to MyMarket?

Can't see anything else it might be.
(Unless it's not in the sheet code but in a macro)

[Captain Sensible]

How do I give a value to my market, I just copied it from some code previously posted on her. Worked ok in one of those sheets. So I guess the value for mymarket must have been in there somewhere

Thanks

[GeorgeUK]

Was there another macro on the sheet you copied this from?

Possibly public - so that all code could use the range that this referred to?

Should be able to do CTRL+F and search the entire project of your other workbook

[a1ehouse]

I have just pasted the exact code in VBA Editor, put a 1 in C5, a 1 in C7, then pressed F5 to run the macro while in VBA editor and it works fine. Are you sure the Worksheet_Calculate macro is being fired?

[Captain Sensible]

" Are you sure the Worksheet_Calculate macro is being fired"

how would I know when it's being fired - I had it working OK before but now it won't for some unkonown reason. It was basically a hack of code posted on here before that clears the cell values when switching market on auto select

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range(Cells(5, 17), Cells(100, 21)).Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[a1ehouse]

Add the line MsgBox "hello!" after the line
Private Sub Worksheet_Calculate()

If you use Private Sub Worksheet_Change(ByVal Target As Range) it works

[Captain Sensible]

Thanks alehouse the message box thingy doesn't work on calculate but does on change.

Only problem with change is that it doesn't recognise when the cell has changed from 1 to 1 it'll still count and not go to exit. When the calculate one worked it'd not add another 1 if the cell changed from 1 to 1 as it went to exit

[a1ehouse]

Calculate only really works when you've formulae on the sheet

What happens when you put a formula on the sheet e.g. =Now()

[Captain Sensible]

K, thanks alehouse works fine when I have a calculation in cell C5 something like C5=D5+E5

Thanks for the help.

[GeorgeUK]

on your original code you had
If [C5].Value = MyMarket Then

If cell C5 is the same as MYMarket value...

In the other example you gave, it had the line
MyMarket = [A1].Value

So MyMarket had a value. (But is still empty the first time it appears in the code)

The Worksheet_Calculate event will run every time a calculation is made, so your code will run if there is a formula on your sheet that changes (eg if a formula is influenced by the odds, every time the odds change the event will run)

[GeorgeUK]

Just ignore me.

Giving bum advice

I thought the excel variables were Dim, Private and Public. With MyMarket needing to be a Public variable so it retained it's value the next time the code ran. This is what static does - so i was only correct for the first time the code is ran and MyMarket won't have a value assigned.

Sorry about that

[a1ehouse]

Get to bed earlier G!