Calling GeorgeUk

[soccergeez]

Just wanted to say thank you for replying to my thread on General - I must admit it went a little over my head as I've never even looked at Macros etc etc before yesterday!

Anyway I have kept what you wrote and I'm off to buy myself a basic book tomorrow if I can find one which will hopefully make things a little clearer!

Many thanks again.

[GeorgeUK]

No problem mate.

Don't know if you need to buy a book for what you want, but if your betting is better than mine, you may be able to afford one

If you want to look at different macros, i would suggest doing a search on mrexcel.com or google groups for either worksheet_change or worksheet_calculate (whichever one you decide to go with).

Just have a read of a few of the questions and you should see what they are doing in the code.

There's a post on here somewhere that suggested a few good websites for beginners so you may want to look at that too. If you need any help, just yell. I'm still working on a workbook thats taken me months for betting before the off, then resubmitting inplay, but had to change the logic as the code was getting to complex.

In the example i gave, it's usually best to write down what you are going to try to do in that pseudo-code style so you can keep track of what should go where.

good luck

[soccergeez]

George - I have been looking around on the net this morning and have come across this bit of code which (I think) does roughly what I need.

Must admit though I dont fully understand it though (surprise eh!).

I was hoping you would have a quick look and see if its what I'm after and how I could change it for my sheet, all I need it to do is keep the previous value of F5 in cell F12, then update every time so in effect cell F12 is always one price movement behind the current price.

Anyway if you do get time to have a look would be much appreciated

Thanks.

[GeorgeUK]

i'd be happy to look at the code.

what code?

[soccergeez]

hmmm you would never guess I have the flu would you!

Public acVal

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
Target.AddComment
Target.Comment.Text "Previous entry was " & acVal
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.Value = "" Then
acVal = ""
Else
acVal = Target.Value
End If
End Sub

Does that make any sense?

[GeorgeUK]

soccergeez

The code is definately working in the right area you want, but there is probably a little more required for what you want. It's the logic i'm trying to work through to make sure this is right.

Cell F5 updates (for 1st time)
Cell F12 shows old F5 value (blank)
then repeat

If this is what you want, i think the code will need to store the F5 value in memory until F5 is updated again, then place the value in F12.

Can you confirm this is what you are looking for?
And is it only F5 you need or all the odds from F5 down?

[soccergeez]

George - that is indeed what I am looking for thanks.

Re: the F5 question, I will only be following one selection at a time, normally F5 will be the odds I am following. Presumably if I wanted to follow F6 or some other cell I could just alter the code to reflect that without much difficulty? In any case I dont need it to follow all the F column.

[GeorgeUK]

Just put some quick code together. The reason i was asking was the layout of the workbook may make it tricky for entering the ranges - you could alter the range each time if you wanted to, but there might be an easier way.

right click on the worksheet tab and view code
paste this in (it should only run if cell F5 is altered)

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static OldValue As Double
'OldValue = 0
If Target.Address = Range("F5").Address Then
    Range("F12").Value = OldValue
    OldValue = Target.Value
End If
End Sub


Edit: Used Integer instead of double. You'll be needing decimals won't you.
Code updated and fixed.

[soccergeez]

I apologise for this but real ficko question coming up now, I have copied and pasted the code as you said now what do I need to do to get it to run??

Sorry about this George.

[GeorgeUK]

It should have a value coming up in F12 after the value in F5 changes...

Unless you have macro security on maximum.
goto tools - options - security
macro security (button at bottom)
change to the middle setting if it is on high.

[soccergeez]

nope nothing coming up, macro security was already at medium. I have copied the code in as suggested but there is nothing at all in F12, its like the sheet doesnt know the VBA is there, do I have to tell it somehow?

In case you were wondering this is an original of Excel 2003!!

[GeorgeUK]

Thats what i get for writing the code at work when i don't have access to BA
sorry mate.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static OldValue As Double
'OldValue = 0
If Not Application.Intersect(Target, Range("F5")) Is Nothing Then
'If Target.Address = Range("F5").Address Then
    If Range("F5").Value = OldValue Then GoTo 1
    Range("F12").Value = OldValue
    OldValue = Range("F5").Value
End If
1
End Sub

ok - explanation time.
worksheet_change - this means that every time a cell on the worksheet the code is in changes, the code will run.
Static OldValue as Double - Static retains the value of "OldValue" in memory so it can be used later (like a sticky note reminder)

what i had to change:
target refers to the cell you are interested in. The one you want to monitor any changes on. The code didn't run before because the target address neas never "$F$5". BA was updating all the data from excel in one lot so the target address was something like "$A$1:$P$6". So it was not finding what we were telling it to look for.
To get around this we tell the program to see if one of the cells being changed in target.address is F5 using
If Not Application.Intersect(Target, Range("F5")) Is Nothing Then
Then to make sure we didn't just get an updated value in F12 every time F5 was refreshed (If F6 was being refreshed, it meant that target.address had a change, but you didn't want to know about it as you are only interested in F5), we said If Range("F5").Value = OldValue Then GoTo 1. If F5's value is the same as the last time then goto 1, otherwise when F6 was updated, it would make F12 the same as F5's unchanged value. (That sounds a bit complicated, but it's not if you try to step through it slowly).
Then we go to the changes for F12 to be F5's old value we have kept in the memory, and as F5 has changed, update OldValue.

Now have something to drink - you'll have a reason if your head is spinning

Note - there will be 2 lines that are green when you put them in the editor. You can delete these if you want as they're not doing anything. They were part of the code i was testing but are not required. I usually keep that stuff in to show myself what i have tried before that didn't work.

[soccergeez]

George - this ones spot on mate, works absolutely perfectly - thank you so much and sorry for taking up so much of your time!

Next time I post a vba query you will know better than to reply to it!!

Many thanks again.

[GeorgeUK]

lol no problem mate.

Next time i'll just not do it at work

[Castillo]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static OldValue As Double
'OldValue = 0
If Not Application.Intersect(Target, Range("F5")) Is Nothing Then
'If Target.Address = Range("F5").Address Then
    If Range("F5").Value = OldValue Then GoTo 1
    Range("F12").Value = OldValue
    OldValue = Range("F5").Value
End If
1
End Sub

Hi George

Could you please help.
All of my spreadsheets use formulas etc, I have never used VBA coding before.
But the recording of cell history is something which could prove very useful.
Could the above be applied to cells F5 to F35, so all odds are updated in say Y5 to Y35?

Many Thanks

Cheers
Castillo