Comparisions based on value of 2 cells

I am creating a little sports day program in excel.

I am trying to calculate the points based on event and postion.

I have this at the moment for the points function:

VB Code:

Function points()
Sheets("Sheet1").Select
position1 = Range("B6").Value
event1 = Range("B7").Value
 
' if event not relay and position is 1st make points = 4
' if event not relay and position is 2nd make points = 3
' if event not relay and position is 3rd make points = 2
' if event not relay and position is 4th make points = 1
 
' if event is relay and position is 1st make points = 8
' if event is relay and position is 2nd make points = 6
' if event is relay and position is 3rd make points = 4
' if event is relay and position is 4th make points = 2
 
 
End Function

How can I do these comparsions:

if event not relay and position is 1st make points = 4
if event not relay and position is 2nd make points = 3
if event not relay and position is 3rd make points = 2
if event not relay and position is 4th make points = 1

if event is relay and position is 1st make points = 8
if event is relay and position is 2nd make points = 6
if event is relay and position is 3rd make points = 4
if event is relay and position is 4th make points = 2

Thank you

If event1 comes out as a string:

VB Code:

If event1 <> "Relay" then
points = 5 - position1
Else
points = 10 - (position1*2)
End if

zaza

so what does <> mean?

"Not Equal To"

i.e. <> <> = ;)

VB Code:

Calculate
 
Sheets("Sheet1").Select
position1 = Range("B6").Value
event1 = Range("B7").Value
 
 
If event1 <> "Relay" Then
points = 5 - position1
Range("B8").Select
ActiveCell.FormulaR1C1 = "points"
Calculate
Else
points = 10 - (position1 * 2)
Range("B8").Select
ActiveCell.FormulaR1C1 = "points"
Calculate
End If
 
 
 
Calculate
 
    NextRow = Worksheets("Sheet3").Range("B65536").End(xlUp).Row + 1
    Worksheets("Sheet3").Cells(NextRow, 2).Resize(1, 7).Value = Array( _
        Worksheets("Sheet1").Range("B2").Value, _
        Worksheets("Sheet1").Range("B3").Value, _
        Worksheets("Sheet1").Range("B4").Value, _
        Worksheets("Sheet1").Range("B5").Value, _
        Worksheets("Sheet1").Range("B6").Value, _
        Worksheets("Sheet1").Range("B7").Value, _
        Worksheets("Sheet1").Range("B8").Value)
        
        
        Sheets("Sheet1").Select
        Range("B2:B7").Select
        Selection.ClearContents
        Range("B2").Select

Is what I have at the moment but I get an error "13" Type Mismatch error.

What could be causing that?

Can you help me?

What line is giving you the error?
Also, have you declared your variables? If so, what are the dattypes?

Thank you for all your help

I have one last part of this project that I need help with.

Have a look at the following image:

http://img456.imageshack.us/img456/1388/scores7fb.jpg

How can I find the total points for each team i.e. chatsworth,melbourne, Gilbert and Hurst and put them in a leaderboard on a VBA form in decending order?

Thank you in advance

Is it possible?

OK how can I find the sum of each team?

Try using the Excel function SUMIF.