Copying +/- (X5 to X10) when Greyhounds are off

[excelhasey]

Hi, hope someone can help as I am not a VBA expert just try and understand other people's code and convert it to my use (not that successfully !!!) - I have placed this code in VBA in sheet1 which is also known as "Selections"

I am after recording the name of the race, my balance and my position in that race for traps 1 to 6 as soon as the race is off and for this data to be placed on another sheet (sheet7 which I have also named as Balance) within the same workbook, so I have tried the following (however only the race details and the balance appear on the required sheet) :-

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If [A1] <> currentMarket Then
currentMarket = [A1]
Meeting
logBalance
End If
End Sub

Private Sub Meeting()
Application.EnableEvents = False
Dim r As Integer
r = 2
While Sheet7.Cells(r, 3) <> ""
r = r + 1
Wend
Sheet7.Cells(r, 3) = currentMarket
Sheet7.Cells(r, 4) = [I2] 'My current balance

Application.EnableEvents = True
End Sub

Private Sub logBalance()

Application.EnableEvents = False

If Range("F2").Text = "Suspended" Then 'Greyhound race off

Dim r As Integer
r = 2
While Sheet7.Cells(r, 3) <> ""
r = r + 1
Wend

Range("X5").Copy
Sheet7.Cells(r, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheet7.Cells(r, 6) = [X6]
Sheet7.Cells(r, 7) = [X7]
Sheet7.Cells(r, = [X8]
Sheet7.Cells(r, 9) = [X9]
Sheet7.Cells(r, 10) = [X10]

'Two different codes above tried to see if either of them work

End If

Application.EnableEvents = True
End Sub

Any clever boys got any good ideas ???

Thanks

[Captain Sensible]

Probably easier if you just say where you want the data placed i.e. Cell or column addresses and if you want a record of all races, either last race on top or being placed at the bottom of the results. By postion I'm assuming you mean Column X the pnl column?

[Captain Sensible]

By easier I mean it's easier to right from scratch as most of us have code snipets anyway and it's quicker than trying to correct or figure out what someone's code is trying to do.

[excelhasey]

Evening Captain,

On the main excel sheet (sheet 1 or "Selections") where all the main data is updated from BF via BA showing race name, all the runners, the prices etc., your trigger column, the odds and your position in the market i.e. up\or down for each selection - In my case this is Column X and starts with X5 (and goes to X44 but obviously that is set up for by BA for a max of 40 horses and not the case for Greyhounds) shows this

So I want a new sheet to show the race venue, time etc which is cell A1 on the main sheet to be in Column B of my new sheet (within the same workbook) starting at cell B2 (as I have headings in row 1) then in column C my balance (taken from the main sheet cell I2 again starting in row 2) then in columns D to wherever (Column AR would be good as it will then cover horses as well as greyhound races) the individual profit or loss for each selection (for this race at the off (so that is Column X rows 5 to 44 on the main sheet) and I want this copied as a past special as a value so it doesn't then return to zero when the market is shown as closed etc., so again that would be row 2 to match above race data etc

I wish to keep all previous races so the next set of data (next race) would then be on the next available blank row below the previously filled rows (so race 2 of the day would be row 3, race 3 on row 4 etc.)

Does that make any sense ??

Cheers for any pointers

[Captain Sensible]

Here's something that might be OK to use, you can obviously change addresses. If you don't want a record of all races just remove the line Worksheets("Balance").Range("A1:A8").Insert (xlShiftDown)

Code: Select all

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
   
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
 
With Target.Parent ' saves us hard coding the full address
   
    If .Range("A1").Value <> Worksheets("Balance").Range("A1").Value And .Range("F2").Value = "Suspended" Then

        Worksheets("Balance").Range("A1:A8").Insert (xlShiftDown)
        Worksheets("Balance").Range("A1").Value = .Range("A1").Value
        Worksheets("Balance").Range("A2").Value = .Range("I2").Value
        Worksheets("Balance").Range("A3:A8").Value = .Range("X5:X10").Value


    End If

 
End With
   
 
Application.EnableEvents = True 'Turn on events again
End Sub

[Captain Sensible]

I posted the above just as you were posting, my tea has just arrived so I'll have a look when/if I get chance later otherwise tomorrow as it doesn't sound hard.

[Captain Sensible]

Here's a quick version that you can probably tweak to suit your needs

Code: Select all

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myarray As Variant


If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
   
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
On Error GoTo Xit:


    If Worksheets("Balance").Range("B2").Value <> Worksheets("Selections").Range("A1").Value And _
    Worksheets("Selections").Range("F2").Value = "Suspended" Then
   
        Worksheets("Balance").Range("B2:BB2").Insert (xlShiftDown)
        myarray = Worksheets("Selections").Range("X5:X55").Value
       
        Worksheets("Balance").Range("B2").Value = Worksheets("Selections").Range("A1").Value
        Worksheets("Balance").Range("C2").Value = Worksheets("Selections").Range("I2").Value
        Worksheets("Balance").Range("D2:BB2").Value = Application.WorksheetFunction.Transpose(myarray)
   
    End If
 
Xit:
Application.EnableEvents = True 'Turn on events again
End Sub


[excelhasey]

Thanks Captain hope you didn't let ya dinner go cold

I will try it tomorrow much appreciated

[excelhasey]

Your code seems to do what it says on the tin Mr Sensible thank you

One further question please It inserts the new data above existing data and moves existing data down I guess that is why your code has (xlShiftDown) in it and if I wanted the data to be on the next available row below the last row of data I changed it to (xlShiftUp) but it does nothing as I guess that is limited by Worksheets("Balance").Range("B2:BB2").Insert as the code is always calling for an insert above row 2 ??

Is it easy to amend code to add data below last row ?? or is it a major inconvenience ??

Thanks

[Captain Sensible]

Try this, haven't had chance to test it but should work in theory as you're simply finding the last filled row

Code: Select all

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myarray As Variant
Dim lastrow As Integer


If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
   
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
On Error GoTo Xit:
lastrow = Worksheets("Balance").Range("B" & Rows.Count).End(xlUp).Row

    If Worksheets("Balance").Range("B" & lastrow).Value <> Worksheets("Selections").Range("A1").Value And _
    Worksheets("Selections").Range("F2").Value = "Suspended" Then

       
        myarray = Worksheets("Selections").Range("X5:X55").Value
       
 Worksheets("Balance").Range("B" & lastrow + 1).Value = Worksheets("Selections").Range("A1").Value
        Worksheets("Balance").Range("C" & lastrow + 1).Value = Worksheets("Selections").Range("I2").Value
        Worksheets("Balance").Range("D" & lastrow + 1 & ":BB" & lastrow + 1).Value = Application.WorksheetFunction.Transpose(myarray)
   
    End If
 
Xit:
Application.EnableEvents = True 'Turn on events again
End Sub