Macro help

[parbynat]

Can someone help with this?

I have a macro that works fine when I input the trigger word YES into cell A3 but when I automate YES to be shown in cell A3 from another cell nothing happens.

In A2 I have the value 20 and in A3 I have the formula =IF A2>10,"YES",""

This is a worksheet change event.

Thanks

[jimibt]

Can someone help with this?

I have a macro that works fine when I input the trigger word YES into cell A3 but when I automate YES to be shown in cell A3 from another cell nothing happens.

In A2 I have the value 20 and in A3 I have the formula =IF A2>10,"YES",""

This is a worksheet change event.

Thanks

you need to get that value from the worksheet calculate event. weird and annoying i know!!

[parbynat]

Thanks for replying.
I think I have 5 bits of code in the worksheet that trigger the macro, do I need to add a calculate event code to go with the change event codes?

[spreadbetting]

The Worksheet change and calculate are basically just ways to trigger your vba code, change will run when there's a physical change on the worksheet, calculate runs when the sheet has done a calculation. =IF A2>10,"YES","" is a calculation as the actual contents of the cell don't change just the value you see changes.

Hard to know if you need a change and calculate event on the spreadsheet as no one knows what your code is, but you can have both a Worksheet_Change and Worksheet_Calculate on the sheet if needed.

If you're linking to BA, everytime it updates the sheet it will trigger a workheet_change simply because it writes the update time to the sheet. If you just want to check the value of the cell you could use the following as the .Value means we're simply looking at the cells current value not the formula.

Code: Select all

If Range("A1").Value = "YES" Then


...do whatever

End If

[ShaunWhite]

If you're using vba then it probably isn't necessary to have your "if (a2>10,"yes" "no") formula in the cell at all.

The code could check if cell A2 > 10 and either set A3 to "yes" or just do whatever it is you want to do when A2 > 10 like setting the bet command cell to place a bet.

Mixing formulas in the sheet and also using coded logic creates the issues you're experiencing. Once you're in the vba realm then the sheet just becomes a visualisation of the data and manipulating that can all be done in your code. Debugging is easier too with all the logic being in one place rather than in various cells.

[parbynat]

I'm trying to compress the data received from a tennis match.
Rather than get well over a thousand lines I am trying to get data everytime the score changes.
This is one line of the code

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    If Not Intersect(Range("J2"), Target) Is Nothing Then Call Record_data
    Application.EnableEvents = True

I got this for points move on player 1 with a slight delay so the markets settle. It starts the macro and gives me a line of data if for example I change it from 0 to 15, but when I link it upto BA score feed it doesn't recognise the change so doesn't feed a line of data.

[parbynat]

This works perfectly if I'm unconnected and just change AH10 manually, I get 15 in AM1 which triggers Record data and TRUE to be placed in AM2 to stop it firing again.
I have other codes working for 30,40 and Av but have left them out of this code.
Problem obviously comes from connecting to BA through guardian.
After much research and replies here I feel I have a high percentage of this correct and have tried the differant lines of code with Selection change, change & calculate but it still will not play ball.
Can someone help me with this?

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Range underneath = num then calls Macro'

If Range("AH10") = 15 Then Call In_N
If Range("AM1") = 15 And Range("AM2") = "" Then Call Record_data
If Range("AM1") = 15 Then Call In_N5

End Sub

Sub In_N()

'Macro will send value underneath to Range underneath'
Range("AM1").Value = 15

End Sub
Sub In_N5()

'Macro will send value underneath to Range underneath'
 Range("AM2").Value = ("TRUE")

End Sub