Excel update on new market only partial

[greenfingers]

Hi, apologies if this has always been the case but I'm having trouble with a few of my spreadsheets (that record odds movements) relating to when a new market loads.

The problem occurs when going to a new horse race market which has fewer runners than the previous race.

The problem is that the first update that happens seems to only cut data for the "excess" number of runners from the previous race's data before actually updating with all the data for the new race. Confused I know, I just can't explain it!

Here's an example. I'm recording the data for a 20 runner race. The next race happens to be a 12 runner race. When the race is selected, the first update cuts all the data for the bottom 8 runners in the previous race. The race name, names of the first 12 runners etc all stay the same and are not changed yet. On the next refresh, all the new race data is imported.

This means that the last data captured for the first race only has 12 runners.

Problem is, I'm finding this difficult to work around and detect the market change as the market name is still the same when the "excess" runners get wiped.

Phew, sorry that's all a bit longwinded.....

I'm running 1.1.0.66x82.

[osknows]

You could detect the change of market name in cell A1 and clear a range one-time only. Eg this in a sheet object will clear range A5:Z55 once when the market changes

Code: Select all

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
    Application.EnableEvents = False

    With Target.Parent

        If .Range("A1").Value <> currentMarket Then
            currentMarket = .Range("A1").Value
            .Range("A5:Z55").ClearContents
        End If
    End With

   Application.EnableEvents = True
End If

End Sub

[alrodopial]

After the
Application.EnableEvents = False
add the

If Target.Cells(1, 1).Value = "" Then Exit Sub

Before entering the new data BA checks if the number of runners in the sheet are more that those that are about to be copied.
If they are , it deletes the existing data at the sheet and then rights the new data, so there may be a "false" 16 columns uptade.
At this update the cell(1,1) has no data so -> exit sub

[greenfingers]

Thanks very much for your quick replies guys. I'll try them out in the morning.

osknows - I don't really understand how 'target' works and I haven't seen target.parent before, what's that about?

Cheers.

[osknows]

When the worksheet change event triggers the Target variable is the range of cells which have changed.

That range of cells will be on a worksheet, it's 'parent'; the worksheet also has a 'parent' the workbook etc

Using 'With Target.Parent' references the sheet but doesn't require a worksheet name or codename to be hardcoded.

Try this in a sheet object

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox Target.Address
MsgBox Target.Parent.Name
MsgBox Target.Parent.Parent.Name
End Sub

[greenfingers]

Thanks again guys. alrodopial, I didn't see at first why your line of code works, but I get it now! I see what you mean about testing for the 'false' update. Thanks for the explanation osknows. The penny has dropped!

[alrodopial]

Small correction, it should be insert between these two lines:

Code: Select all

If Target.Columns.Count = 16 Then
    Application.EnableEvents = False