Function GiftCertificate(PCV, GCV)
'Calculates the number of Gift Certificates required to
'move to the next rank

GiftCert = 35
BronzeGCv = 1000
SilverGCV = 3500
CQSliverGCV = 3500
GoldGCV = 10000
PlatinumGCV = 25000
DiamondGCV = 50000
DDiamondGCV = 250000
BronzePCv = 100
SilverPCV = 350
CQSliverPCV = 600
GoldPCV = 1000
PlatinumPCV = 2500
PlatinumPCV = 5000
PlatinumPCV = 25000
With Application
Select Case PCV
Case 0 To 99: GiftCertificate = .Max(.RoundUp((BronzePCv - PCV) / _
GiftCert, 0), .RoundUp((BronzeGCv - GCV) / GiftCert, 0)) * GiftCert
Case 100 To 349: GiftCertificate = .Max(.RoundUp((SilverPCV - PCV) / _
GiftCert, 0), .RoundUp((SilverGCV - GCV) / GiftCert, 0)) * GiftCert
Case 350 To 599: GiftCertificate = .Max(.RoundUp((CQSliverPCV - PCV) _
/ GiftCert, 0), .RoundUp((CQSliverGCV - GCV) / GiftCert, 0)) * GiftCert
Case 600 To 999: GiftCertificate = .Max(.RoundUp((GoldPCV - PCV) / _
GiftCert, 0), .RoundUp((GoldGCV - GCV) / GiftCert, 0)) * GiftCert
Case 1000 To 2499: GiftCertificate = .Max(.RoundUp((PlatinumPCV - _
PCV) / GiftCert, 0), .RoundUp((PlatinumGCV - GCV) / GiftCert, 0)) * _
GiftCert
Case 2500 To 4999: GiftCertificate = .Max(.RoundUp((PlatinumPCV - _
PCV) / GiftCert, 0), .RoundUp((DiamondGCV - GCV) / GiftCert, 0)) * _
GiftCert
Case 5000 To 24999: GiftCertificate = .Max(.RoundUp((PlatinumPCV - _
PCV) / GiftCert, 0), .RoundUp((DDiamondGCV - GCV) / GiftCert, 0)) * _
GiftCert
End Select
End With

--
Regards,
Tom Ogilvy

"Heather" wrote in message
om...
Hi
Below is a copy of my code, but Excel returns the message: Sub or
function not defined. Roundup is selected. If I change to
Round(which I really don't want to do), then Max is highlighted. If I
change Max to Large(value,1), it also gets selected with the above
message.

If anyone can help me devise a function that tests which range 2
separate values fall into and what the difference is to step up to the
next rank. This difference then needs to be divided by 35, and the
rounded up interger is displayed.
Thanks


Function GiftCertificate(PCV, GCV)
'Calculates the number of Gift Certificates required to
'move to the next rank

GiftCert = 35
BronzeGCv = 1000
SilverGCV = 3500
CQSliverGCV = 3500
GoldGCV = 10000
PlatinumGCV = 25000
DiamondGCV = 50000
DDiamondGCV = 250000
BronzePCv = 100
SilverPCV = 350
CQSliverPCV = 600
GoldPCV = 1000
PlatinumPCV = 2500
PlatinumPCV = 5000
PlatinumPCV = 25000


Select Case PCV
Case 0 To 99: GiftCertificate = Max(RoundUp((BronzePCv - PCV) /
GiftCert, 0), RoundUp((BronzeGCv - GCV) / GiftCert, 0)) * GiftCert
Case 100 To 349: GiftCertificate = Max(RoundUp((SilverPCV - PCV) /
GiftCert, 0), RoundUp((SilverGCV - GCV) / GiftCert, 0)) * GiftCert
Case 350 To 599: GiftCertificate = Max(RoundUp((CQSliverPCV - PCV)
/ GiftCert, 0), RoundUp((CQSliverGCV - GCV) / GiftCert, 0)) * GiftCert
Case 600 To 999: GiftCertificate = Max(RoundUp((GoldPCV - PCV) /
GiftCert, 0), RoundUp((GoldGCV - GCV) / GiftCert, 0)) * GiftCert
Case 1000 To 2499: GiftCertificate = Max(RoundUp((PlatinumPCV -
PCV) / GiftCert, 0), RoundUp((PlatinumGCV - GCV) / GiftCert, 0)) *
GiftCert
Case 2500 To 4999: GiftCertificate = Max(RoundUp((PlatinumPCV -
PCV) / GiftCert, 0), RoundUp((DiamondGCV - GCV) / GiftCert, 0)) *
GiftCert
Case 5000 To 24999: GiftCertificate = Max(RoundUp((PlatinumPCV -
PCV) / GiftCert, 0), RoundUp((DDiamondGCV - GCV) / GiftCert, 0)) *
GiftCert
End Select



End Function