Auto clearing status cells

Example spreadsheets and comments on example spreadsheets.
nigelk
Posts: 377
Joined: Wed Apr 15, 2009 11:00 pm

Mon Dec 11, 2017 10:05 am

Hi Callum

You could monitor column O to see if the bet has been placed, then clear the relevent row in col L. Something along the lines of

Code: Select all

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 15 And Target = "Placed" Then

rn = Target.Row

Cells(rn, 12) = ""

Target = ""

End If

End Sub
The above will clear both the report and status cells. Change to suit. Don't forget to disable/enable events if needed

Cheers, Nigel.

CallumPerry
Posts: 17
Joined: Wed Apr 19, 2017 5:12 pm

Mon Dec 11, 2017 10:55 am

Cheers Nigel but when the first bet is placed the entire formula in L9 disappears preventing me from firing more bets, I tried getting around this by pasting my formula back into the code between the quotation marks instead of just having "" but it displays a compile error, "expected end of statement". How would you get around this?

Say for example for horse 1: L9 shows =IF(A1=1,B1=1,C1=1,1,0) {Just a made up trigger instruction} but then on the second horse the code is =IF(A2=1,B2=1,C2=1,1,0). Because the cells all drop one row each time can you lock in VBA with $ or do I go about this some other way? I have 6 'brands' in total i.e. 6 horses/greyhound or whatever. So I need a code that will wipe column O when placed but redisplay the same (different on each row) trigger formula immediately afterwards and what about when L9 shows back briefly but disappears before the order can be processed?

:lol: It's funny isn't it how you think of the simplest of strategies that you think will take 10 minutes to create and then there are so many other variables that appear that you didn't even think of before!

Callum :)

nigelk
Posts: 377
Joined: Wed Apr 15, 2009 11:00 pm

Mon Dec 11, 2017 12:23 pm

If you want to fill a range of cells 'en-masse' this will be ok, and fill all the cells with the correct formula

Code: Select all

Range("D2:D10").Formula = "=IF(AND(A2=1,B2=1,C2=1),1,0)"
If you need to step through them and only fill every other cell, this will fail because it will always reference the original cells, ie, A2,B2,C2

Code: Select all

For x = 2 To 10 Step 2

Cells(x, 4).Formula = "=IF(AND(A2=1,B2=1,C2=1),1,0)"

Next
You have to use the R1C1 notation to get the correct formulas in each cell: (Just type your formula in as normal, then switch to R1C1 and copy the formula from the formula bar into your code).

Code: Select all

For x = 2 To 10 Step 2

Cells(x, 4).FormulaR1C1 = "=IF(AND(RC[-3]=1,RC[-2]=1,RC[-1]=1),1,0)"

Next
The 'expected end of statement' probably appears because you could have something like:

Code: Select all

Range("D2").Formula = "=IF(A2=1,"Back","")"
But if you have anything in quotation marks in the formula, they themselves have to be within quotation marks (real pain)

Code: Select all

Range("D2").Formula = "=IF(A2=1,""Back"","""")"

nigelk
Posts: 377
Joined: Wed Apr 15, 2009 11:00 pm

Mon Dec 11, 2017 12:56 pm

what about when L9 shows back briefly but disappears before the order can be processed
If you want it to stay there you'll have to code it in, if it's formula based it might only be there for a fraction of a second before your formulas change value.

Say I've added up several different formula and the result appears in F2, so I want to put the word "Back" in the command column. (F2 could just as well contain a total figure rather than a word).

Code: Select all

Private Sub Worksheet_Calculate()

If Range("F2") = "Back" Then
Range("F2").Offset(0, -5) = "Back"
End If

End Sub

CallumPerry
Posts: 17
Joined: Wed Apr 19, 2017 5:12 pm

Mon Dec 11, 2017 4:21 pm

Thank you for such a detailed reply Nigel! I'm going to look into this - as I'm quite new to coding it may take me some time aha - but I should have plenty to go off with your reply. Thank you again!

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest