VBA help anyone?

[Toma]

Guys, just wondering if anyone would know the VBA code to capture my positions at Kick Off?

I have extracted what I need into this "dashboard" but can't capture the kick off prices and financials - other than manually doing it

Any help very much appreciated (hope this picture works ???)

[osknows]

This will copy the data from pre-game columns to at kick off columns when run. As I can't see the exact row/column ids, I've assumed it starts from cell A1 so that the -£40.87 is in cell F5

Just call KickOff when you want to copy the values

MODULE:

Code: Select all

Sub KickOff()
Dim lRow As Long
Dim ws As Worksheet
Dim rngToSearch As Range
Dim dataStartRow As Long
   
    Set ws = R1
    Set rngToSearch = ws.Range("F:K")
    dataStartRow = 3
    writeDataOffsetColumns = 8
   
    lRow = LastRow(rngToSearch)
    If lRow > dataStartRow Then
        'Copies Contents of data to same row offset by 8 columns
        rngToSearch.Resize(lRow - dataStartRow, rngToSearch.Columns.Count).Offset(dataStartRow, writeDataOffsetColumns).Value _
         = rngToSearch.Resize(lRow - dataStartRow, rngToSearch.Columns.Count).Offset(dataStartRow, 0).Value
    End If
End Sub


Function LastRow(ByVal rng As Range, Optional Offset As Long) As Long
On Error GoTo blankSheetError
    'Identify next blank row
    LastRow = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + Offset

    Exit Function

blankSheetError:
    LastRow = 1 'Will produce error if blank sheet so default to row 1 as cannot have row 0
    Resume Next

End Function

[Toma]

This is brilliant osknows thank you very much for this response...however, its currently coming up with "Object Required"? Sorry I'm normally ok with Excel itself but this VB stuff is a different level.

Ideally what I wanted to be able to do was use the =NOW() function to get the correct time and then check the kick off times and copy the relevant rows over as and when the games started.

Rather than actually be there to run the macro as that's half the problem I keep missing the kick offs and therefore the prices at kick off

Sorry mate for not explaining myself earlier but once again thank you so much for your assistance (so far) different class!

Alan

[Toma]

so at this stage of the day this is how my "manually updated" spreadsheet looks now:-

[osknows]

What format are the kick off times in column B, are they strings '18:00 or dates formatted as hh:mm?

If you like, email it to me and I'll add the code. I'll PM my email address

Os

[ghhtty]

If you like, email it to me and I'll add the code. I'll PM my email address.