Comparisions based on value of 2 cells

[asgsoft]

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:

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

[zaza]

If event1 comes out as a string:

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

zaza

[asgsoft]

so what does <> mean?

[DKenny]

"Not Equal To"

i.e. <> <> =

[asgsoft]

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?

[DKenny]

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

[asgsoft]

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:



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

[asgsoft]

Is it possible?

[asgsoft]

OK how can I find the sum of each team?

[DKenny]

Try using the Excel function SUMIF.