Updating records

[asgsoft]

Please have a look at http://www.asgsoft.net/form.xls

You will see that there is a sheet for records

and also there is an input sheet. What i want is to be able to see that if the information entered has broken the record. However there are two types of measurements. There is time, so the smaller the better but there are also distance events so the longer that better.

Do you think you can help me?

Thank you

[salvelinus]

With your current setup, it looks like you'll have to get every entry for a particular event first, in order to check time/distance in any case. But to check whether you want shortest or longest, you'll have to check at least the first letter of each event. If it's numeric - IsNumeric(Left(somecell, 1)) - then check time. Otherwise, check distance.
If this was Access I'd set it up differently, but I know Access lots better than Excel.
Of course, this won't work if you have a time event that begins with a letter, or a distance event that begins with a number, but I didn't see any in your example.
Another option would be to either separate time/distance into 2 columns, or add a column with some type of identifying code - T and D, for instance.

[asgsoft]

I was thinking of using an IF statement that checks what event it is? and then I have two functions to check if a record is broken. One for lengths and the other for time. But what I have trouble with is to check that a record was broken.

[salvelinus]

Sorry, don't know how to do that between sheets. Access yes, Excel no.

[RobDog888]

You could perform a Sort based on user/time/ascending and then get the first record for that user and look at the cell value. Then do the same for distance with th eappropriate changes.

In your calling code you will perform the first sort and check the value then again another call to sort with the next criteria and get its value. Then if the record is broken by one r oth then you can inform the user or ? Just reference the other sheet.

[asgsoft]

But is there no way to check if the record is broken as it's being entered?

[westconn1]

yes if can be done, you would have to loop through all rows to compare the the result for each race type, as this entails searchin the whole sheet for each race type it might be better to put each race type into a named range, then you can just work with all rows within that named range

pete

[RobDog888]

You could list the records of whatever type is being entered on the form itself. Or even use a UserForm instead of a Sheet for record entry.

[asgsoft]

yes if can be done, you would have to loop through all rows to compare the the result for each race type, as this entails searchin the whole sheet for each race type it might be better to put each race type into a named range, then you can just work with all rows within that named range

pete

can you please give me some code examples?

[westconn1]

Sub checkrecord()
Dim s As Sheet4, f As Sheet1
Set s = Sheet4
Set f = Sheet1
n = f.Name
's.Activate
For i = 2 To s.UsedRange.Rows.Count
    If s.Cells(i, 3) = f.Cells(3, 2) And s.Cells(i, 4) = f.Cells(4, 2) And s.Cells(i, 5) = f.Cells(6, 2) Then
        If f.Cells(8, 2) > s.Cells(i, 6) Then 'compare time/distance, not sure if high or low is better
            s.Cells(i, 2) = f.Cells(2, 2)      ' update record if better
            s.Cells(i, 6) = f.Cells(8, 2)
            s.Cells(i, 7) = Year(Now)
            
        End If
        Exit For
    End If
Next
'for i = 1 to
 
 
 
End Sub

try this see if it works right, check if i have the compare of time/distance round the right way, probably not, the code will just overwrite the new details into the rcords sheet

pete

[asgsoft]

I don't have a Sheet4 or Sheet1

Also how would you say I can check if the distance is more or time is less?

[westconn1]

you do have those sheets, cos i was working in your spreadsheet.

sheet4 is the same as sheets(4) or sheets("records")

which is faster, smaller time/distance or bigger??

the code i posted checked if time distance was greater, then updated the record, if time/distance should be shorter to break record then change the greater than to less than.

as it is for a particular event (eg. 100m) why not just record the time instead of time/distance?

pete

[asgsoft]

OK its working, however, the time distance is a bit confusing. If it's a timed event such as 100m,200m,300m,400m,800m,1500m and relay then the shorter the time the better. Otherwise if its a distance event such as Shot,Discus,Javelin,High Jump,Long Jump,Triple Jump then the longer the distance the better.

All the times are in seconds and distances are in centimeters so its easier to compare.

How can I do this?

When I try this I get an error:

Sub checkrecord2()
Dim s As Sheet4, f As Sheet1
Set s = Sheet4
Set f = Sheet1
n = f.Name
 
For i = 2 To s.UsedRange.Rows.Count
    event1 = f.Cells(6, 2)
If s.Cells(i, 3) = f.Cells(3, 2) And s.Cells(i, 4) = f.Cells(4, 2) And s.Cells(i, 5) = f.Cells(6, 2) Then
If event1 = "100m" Or event1 = "200m" Or event1 = "300m" Or event1 = "400m" Or event1 = "800m" Or event1 = "1500m" Or event1 = "Relay" Then
If f.Cells(8, 2) < s.Cells(i, 6) Then
s.Cells(i, 2) = f.Cells(2, 2)      
            s.Cells(i, 6) = f.Cells(8, 2)
            s.Cells(i, 7) = Year(Now)
MsgBox "Record Broken!", vbInformation, "Record Broken"
 
Else
If f.Cells(8, 2) > s.Cells(i, 6) Then
s.Cells(i, 2) = f.Cells(2, 2)      
            s.Cells(i, 6) = f.Cells(8, 2)
            s.Cells(i, 7) = Year(Now)
MsgBox "Record Broken!", vbInformation, "Record Broken"
End If
            
        End If
        End If
        Exit For
    End If
Next
End Sub

[westconn1]

what error, where?

try using select case or your events, it will probably be much easier to do that way

pete

[asgsoft]

I changed it a bit and I am not able to see if the sport is a distance one. I am not getting any errors but it won't just recognise them. Have a look at this please:

Sub checkrecord2()
Dim s As Sheet4, f As Sheet1
Set s = Sheet4
Set f = Sheet1
n = f.Name
's.Activate
For i = 2 To s.UsedRange.Rows.Count
    event1 = f.Cells(6, 2)
If s.Cells(i, 3) = f.Cells(3, 2) And s.Cells(i, 4) = f.Cells(4, 2) And s.Cells(i, 5) = f.Cells(6, 2) Then
If event1 = "100m" Or event1 = "200m" Or event1 = "300m" Or event1 = "400m" Or event1 = "800m" Or event1 = "1500m" Or event1 = "Relay" Then
If f.Cells(8, 2) < s.Cells(i, 6) Then
s.Cells(i, 2) = f.Cells(2, 2)      ' update record if better
            s.Cells(i, 6) = f.Cells(8, 2)
            s.Cells(i, 7) = Year(Now)
MsgBox "Record Broken!", vbInformation, "Record Broken"
 
End If
If event1 = "Shot Putt" Or event1 = "Discus" Or event1 = "Javelin" Or event1 = "High Jump" Or event1 = "Long Jump" Or event1 = "Triple Jump" Then
 
If f.Cells(8, 2) > s.Cells(i, 6) Then
s.Cells(i, 2) = f.Cells(2, 2)      ' update record if better
            s.Cells(i, 6) = f.Cells(8, 2)
            s.Cells(i, 7) = Year(Now)
MsgBox "Record Broken!", vbInformation, "Record Broken"
End If
            
        End If
        End If
        Exit For
    End If
Next
'for i = 1 to
 
 
 
End Sub

[westconn1]

Sub checkrecord()
Dim s As Sheet4, f As Sheet1, newrecord As Boolean
Set s = Sheet4
Set f = Sheet1
n = f.Name
's.Activate
For i = 2 To s.UsedRange.Rows.Count
    If s.Cells(i, 3) = f.Cells(3, 2) And s.Cells(i, 4) = f.Cells(4, 2) And s.Cells(i, 5) = f.Cells(6, 2) Then
        a = f.Cells(6, 2)
        Select Case f.Cells(6, 2)
            Case "100m", "200m", "400m", "800m", "1500m", "Relay"
                If f.Cells(8, 2) < s.Cells(i, 6) Then newrecord = True
            Case "Shot Putt", "Long Jump", "Triple Jump", "High Jump", "Discus", "Javelin"
                If f.Cells(8, 2) > s.Cells(i, 6) Then newrecord = True
            Case Else
                MsgBox "This event not in record list"
        End Select
        
        
        
        If newrecord Then 'compare time/distance, not sure if high or low is better
            s.Cells(i, 2) = f.Cells(2, 2)      ' update record if better
            s.Cells(i, 6) = f.Cells(8, 2)
            s.Cells(i, 7) = Year(Now)
            
        End If
        Exit For
    End If
Next
'for i = 1 to
 
 
 
End Sub

try this
pete

[asgsoft]

it works

you are a genious

[westconn1]

Case Else
MsgBox "This event not in record list"

this will never do anything, because if the event does not match one of the events already in the record list it will never get that far, i think this is the case with "Shot" or one event that is different in your drop down box to what is in the records, also if you start a new event there is no provision to add that to the records, it will only update existing records

pete