VBA Worksheet Calculate VBA - BA Spreadsheet

[Harry101]

Hi

I was hoping someone could help with the following:

I have a column (in a defined range) eg AK10:AK300. Each one of these row cells refers, by way of a simple IF formula to another single cell. eg AJ10.

AK10 to AK300 (each cell in the column) ends up receiving a single piece of data

I'd like to capture/record the value change from AK10:AK300 to AL10 to AL300 and I'm thinking Worksheet Calculate is the way to go.

And my VBA coding skills are minus zero

Thank you for any help

[nigelk]

Depends if you have other formulas in the worksheet, the calculate event will fire when any formula calculates.

So if you have for example, another formula in AB1 and the value changes 20 times, the event will still fire irrespective of wether the cells you are interested in have changed value or not.So it's possible you could have it fire 20 times but still record the same values.

A way round it is to use a blank another worksheet that only contains formulas to link the cells you are interested in when the value changes, so you know that if it fires, a value must have changed.

[ShaunWhite]

I'd just look for a change in a specific cell. The Worksheet_Change operation is a sledgehammer to crack a nut and always requires a bodge to get it working efficiently otherwise it just spins round and disappears up it's own chuff or blows the stack.

[Harry101]

Thanks Nigel & Shaun.

I was aware of the Calculate changing everything .... and that was the bit I was hoping to find a workaround with. I'd assumed there was a specific nifty bit of coding 'out there' somewhere. I'll start to look at the separate worksheet idea

Thanks again

[spreadbetting]

Like the others have pointed out you need to ensure your WorkSheet calculate/change routines don't continually trigger with each formula being calculated. But that's simple enough by turning off events and calculations within the coding. The actual copying code is very simple and something as below should work fine for you.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False 



Worksheets("Sheet1").Range("AL10:AL300").Value = Worksheets("Sheet1").Range("AK10:AK300").Value



Application.EnableEvents = True

End Sub

I'm not sure how many sets of data BA sends to excel in one refresh as I know other apps send it as two sets, one for prices and one for market data. In reality you won't notice any difference but if you wanted to be nerdy and optimise things further you'd just need to check how many colums changed on each data pass and omit one of those by exiting the sub routine. You could also set any calculations to manual within the code but as you're simply copying data it may be overkill so I didn't include it.

[Bet Angel]

During a worksheet refresh, the last range to be updated is cells $C$2 to $C$6, so if you react to that changing you'll know that everything is up to date and consistent.

So you could add a macro to the worksheet as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2:$C$6" Then
' Worksheet has been fully updated by Bet Angel

'Add your code here

End If
End Sub

[spreadbetting]

That's good info to know, thanks.

In that case the following should work fine for what you're looking for Harry101

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2:$C$6" Then 
Application.EnableEvents = False 


Worksheets("Sheet1").Range("AL10:AL300").Value = Worksheets("Sheet1").Range("AK10:AK300").Value



Application.EnableEvents = True
End If
End Sub

[ShaunWhite]

The tech support 'hive' here is pretty amazing init...if only the rest of the world were like that.

[Harry101]

Apologies for my tardy reply. Thank you for all your replies.

I'm going to crack on with right now - so see what happens!