PDA

Click to See Complete Forum and Search --> : Comparisions based on value of 2 cells

asgsoft
Jun 10th, 2006, 03:21 AM
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
Jun 10th, 2006, 10:36 AM
If event1 comes out as a string:

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


zaza
asgsoft
Jun 12th, 2006, 04:50 PM
so what does <> mean?
DKenny
Jun 13th, 2006, 02:57 PM
"Not Equal To"

i.e. <> <> = ;)
asgsoft
Jun 14th, 2006, 01:40 PM
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
Jun 14th, 2006, 01:42 PM
What line is giving you the error?
Also, have you declared your variables? If so, what are the dattypes?
asgsoft
Jun 17th, 2006, 12:27 PM
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
asgsoft
Jun 19th, 2006, 12:15 PM
Is it possible?
asgsoft
Jun 22nd, 2006, 07:27 AM
OK how can I find the sum of each team?
DKenny
Jun 22nd, 2006, 09:38 AM
Try using the Excel function SUMIF.