Hi Rostov, just a note to say thanks for the code and I can confirm it's working perfectly. I can see your logic … save the Market as a variable (OldVal) then check that the current market equals OldVal. If it doesn't then the Market has changed so call the subroutine to clear the cells (or anything else you may want to do). I wanted to do it by having code to call the sub that actually clears the cells because in the future I can use that code to call any subroutine as I build a library of subs. Thanks again and sorry for the delay in acknowledging but I got distracted into another thread where I have shared your wisdom and given credit. Now I need to explore moving my cell formulae into VBA.
Clearing the Status (and other) cells in Excel
- firlandsfarm
- Posts: 2720
- Joined: Sat May 03, 2014 8:20 am
- firlandsfarm
- Posts: 2720
- Joined: Sat May 03, 2014 8:20 am
ShaunWhite wrote: ↑Sun May 17, 2020 6:54 pm... have you tried prefixing the range with a sheets("SheetName") I'm wondering if it's losing track of which is the active sheet … maybe wrap the process with ... but it's the commands that disable/enable calcs and refreshes.
Hi Shaun/sa7med, thanks for the suggestions. Yes I did try adding the Sheetname but it didn't make any difference and the code was already in the active worksheet and not as a Module (more by luck than judgement). Enable/disable calcs etc. is something I need to think about more but as I said to Shaun earlier I've not spent much time on housekeeping, too much to learn!sa7med wrote: ↑Mon May 18, 2020 9:21 am... as shaun said if it's working when you call it manually on an activesheet then it's probably not pointing to the right place ...Why not feed your sub the worksheet that you want to clear … Another option would just be to copy the code over into the sheets themselves into your worksheet calculate event, rather than calling it.
And thanks for your point about clearing cell AI7. I thought about that and came to the conclusion I can't clear it because it was an IF cell formula so to clear it would delete the formula then I realised the advantage of having VBA calculate the formula and assign the value to the cell. The cell value can then be cleared without deleting the formula. Thanks Shaun, I now realise an advantage in having VBA hold the formulae.
sa7med knows I now have the code I was looking for, I made it a Suggestion that it should be a standard feature of BA to offer the option of clearing the Status cells when the market changes (I can't think of why anyone would want to retain old bet data into a new Market). BA have now published a standard workbook that clears all cells, even the Bet Placement, Odds and Staking cells. I think this is overkill, I don't see a need to clear those cells the same as the Status cells. As the facility is likely to be more a thing for beginners like me I have posted a comment about this on that Thread and simple instruction how to change the cells to be cleared. My problem was not the clearing of the cells it was recording the Market had changed to trigger the clearing.
My thanks to you both for your input.
Hi Anbell, thanks for the post and sorry for the hard precis. Thanks for the tip off about Select, I'm scratch learning by researching a task on an as required basis so such little snippets point me in a direction to research just like the turning on/off calcs etc. I'm sure many will say I should pick up a book and be more structured. One area in your post that I noticed is that you seem to use ClearContents … Range(...) = "" … Range(...).Value = "" to do what appears to me to be the same thing … clear cells. Is there a subtle difference between them?Anbell wrote: ↑Mon May 18, 2020 11:05 am... from what I've read you should probably stay away from SELECT. It is sluggish and unnecessary … If Left(ws.Name, 9) = "Bet Angel" Then … Sheets(ws.Name).Range("B1").ClearContents … Sheets(ws.Name).Range("L9:L55") = "" … End If … Sheet6.Range("U58:V59").Value = ""
That's precisely what it does.firlandsfarm wrote: ↑Wed May 20, 2020 6:34 amHi Rostov, just a note to say thanks for the code and I can confirm it's working perfectly. I can see your logic … save the Market as a variable (OldVal) then check that the current market equals OldVal. If it doesn't then the Market has changed so call the subroutine to clear the cells (or anything else you may want to do). I wanted to do it by having code to call the sub that actually clears the cells because in the future I can use that code to call any subroutine as I build a library of subs. Thanks again and sorry for the delay in acknowledging but I got distracted into another thread where I have shared your wisdom and given credit. Now I need to explore moving my cell formulae into VBA.