In desperate need of help!

[breakbacktennis]

Hi, Im in desperate need of help with this VBA code. The code works fine when entered manually however I need the code to work when linked with a live feed.

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]    Dim KeyCells As Range




   If Target.Address <> Range("G1").Address Then Exit Sub
    


        If Range("G1").Value = "In-play" Then
               Range("U9").Value = Range("G9").Value
               Range("U11").Value = Range("G11").Value
        End If

 [COLOR=#333333]End Sub[/COLOR]

 

[silentwolf]

Hi breakbacktennis,
sorry what are you trying to do?
The code you writen works when you enter in cell "G1" the value "In-play"
When you enter anything else in this cell then your code will exit as disiered.
For what do you need the KeyCells declaration? It does not do anything and by the way is not even set ...
When you are declaring an Object Variable you also need to set it .)

HTH
Silentwolf

 

[breakbacktennis]

Thankyou for your reply silentwolf,

So the above code records 3 cell values. It needs to do this when cell G1 = "In-play".
When I type "In-play" in manually the 3 cell values will be recorded. However this is linked to live data which puts "In-play" in cell G1 if the event is In-play. The code works when manually ran but not when prompted to by the live data. Hope this explains things, again thankyou for your help!

 

[CodeNinja]

Breakbacktennis,
This depends on how you are changing the data in G1. The listener for worksheet_change does not see the change in a formula or external reference as a change... the formula never changes, just the results of that formula. Try this in the worksheet_calculate listener... that might do the job for you... If that doesn't work, please provide exact details as to how this is "linked to live data"... is it a data connection, an external reference or some other method...

Good luck,

CN.

***Post note:
You will not have "Target" as a range to validate against, so instead of using something like if target = "In Play" then... you will have to use if sheet1.range("G1") = ...

 

[breakbacktennis]

Hi CodeNinja, Yes i believe worksheet_calculate could be the way forward as others have said but im a complete vba novice, would you be able to put an example of this. Thankyou in advance and apologise for my lack of knowledge with vba.

 

[silentwolf]

Hi breakbacktennis,
I might not really understand what you are exactly after but maybe this would work?

Private Sub Worksheet_Calculate()
    Dim Target As Range
    
    Set Target = Range("G1")
    
   If Target.Address <> Range("G1").Address Then Exit Sub
        If Range("G1").Value = "In-play" Then
               Range("U9").Value = Range("G9").Value
               Range("U11").Value = Range("G11").Value
        End If
End Sub

HTH
Silentwolf

Otherwise I am sure CodeNinja will get back to you shortly .)

The Worksheet_Calculate() is just a different Event you know how to change that right?

 

[breakbacktennis]

Hi Silentwolf, this code works but everytime the data refreshes the cell values keep refreshing. I want the code only to record the cell values once as soon as G1 says "In-play". I literally no next to nothing with vba so cant even comment on changing event lol.

 

[silentwolf]

Hi breakbacktennis,
You have changed the event to worksheet_Calculate right?

What if you just write this?

        If Range("G1").Value = "In-play" Then
               Range("U9").Value = Range("G9").Value
               Range("U11").Value = Range("G11").Value
        End If

does that work?

HTH
I am also not quite sure what you are after lol .) So we are both novice gg ,) But maybe we can still work it out

 

[breakbacktennis]

Hi Silentwolf unfortunately didnt work but thankyou for giving it a go.

 

[silentwolf]

Hi breakbacktennis,
Sorry I guess you need to wait for CodeNija to get back on that one / he understands it better what you are trying to do and I am sure he has the answer for you in no time .)

Cheers
Silentwolf