Your function returns n-1 for integers less than zero, e.g.

[A1] = -55

GetRoundUpInt(A1) = -56

P


"Kevin Stecyk" wrote in message
...
Heather,

Roundup is a "traditional" spreadsheet function and not a VBA function.
Should you want to use Roundup in VBA, you must use the following syntax:

Application.WorksheetFunction.RoundUp(value)

The same applies to min and max. You might want to check XL help for List
of Worksheet Functions Available to Visual Basic.

Below is a "roll-your-own" round up function written by Myrna Larson and
myself. We were interested in speed, and application.worksheet.functions
tend to be a bit slow. Though for your purposes it might be plenty fast.

So you either have to roll your own functions or use the
application.worksheet functions.

Hope this helps.

Regards,
Kevin






'\================================================ ==========================
'\ Sub/Fun: GetRoundUpInt
'\
'\ Purpose:
'\ Arguments: Value as double
'\ Returns: Rounds up the double value to the next integer value.
'\ Called By: UDFs in this module
'\ Notes:
'\
'\ Keywords: Roundup
'\ Originated: Kevin & Myrna 2 Jun 2003
'\ Rev Hist:

'\================================================ ==========================
'\
'\
Function GetRoundUpInt(dbValue As Double) As Integer
Dim dbTemp As Double

dbTemp = dbValue

If dbValue = 0 Then

If Int(dbValue) = dbTemp Then
GetRoundUpInt = Int(dbValue)
Else
GetRoundUpInt = Int(dbValue) + 1
End If
Else
If Int(dbValue) = dbTemp Then
GetRoundUpInt = Int(dbValue) - 1
Else
GetRoundUpInt = Int(dbValue)
End If
End If

End Function





"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