Function Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

freightliner2006@hotmail.com - 04 Oct 2006 22:18 GMT

Hi all,

I have a function defined as follows:
CEILING(3*(SUM(MID(A1159,1,1),MID(A1159,3,1),MID(A1159,5,1)))

and I want to create my own one, so that once I apply to a (current)
cell,
it performs on the cell immediately on the left, (instead A1159...)

For now, I create a function in VBA editor:

Function MyCeiling(cellontheleftaddress?)
' dont know what to put as parameter
myCeiling = Ceiling(3 * (Sum(Mid(cellontheleft, 1, 1),
Mid(cellontheleft,
3, 1), Mid(cellontheleft, 5, 1)))0, 1)))

End Function

thanks for help

Reply to this Message

Dave Peterson - 04 Oct 2006 22:39 GMT

First, I think you're missing something from your worksheet function.
=Ceiling() wants to have a second argument that tells excel to round up to the
multiple of that number.

So if you had used:
=CEILING(3*(SUM(MID(A1159,1,1),MID(A1159,3,1),MID(A1159,5,1))),7)
to round up to a multiple of 7, you could use a UDF like:

Option Explicit
Function MyCeiling(CellOnTheLeft As Range)
With Application
MyCeiling = .Ceiling(3 * (.Sum(Mid(CellOnTheLeft.Value, 1, 1), _
Mid(CellOnTheLeft.Value, 3, 1), _
Mid(CellOnTheLeft.Value, 5, 1))),
7)
End With
End Function

And if you put the formula in C2 (say), you'd just make sure you pass the cell
that's to the left:

=myCeiling(B2)

ps. Although, I'm not sure what you'd pass to the function if the formula was
in column A--what do you want to use for the cell to the left of something in
column A?

pps. You'll find that if you use lots of these formulas, then using the built
in worksheet formula (not the UDF), your workbook will work lots faster.

> Hi all,
>
[quoted text clipped - 16 lines]
>
> thanks for help

Signature

Dave Peterson

Reply to this Message

freightliner2006@hotmail.com - 05 Oct 2006 15:53 GMT

Sorry I made a mistake trying to simplify my formula to make my post
readable.
The full formula was
=CEILING(3*(SUM(MID(A2,1,1),MID(A2,3,1),MID(A2,5,1),MID(A2,7,1),MID(A2,9,1),MID(A2,11,1)))+SUM(MID(A2,2,1),MID(A2,4,1),MID(A2,6,1),MID(A2,8,1),MID(A2,10,1)),10)-(3*(SUM(MID(A2,1,1),MID(A2,3,1),MID(A2,5,1),MID(A2,7,1),MID(A2,9,1),MID(A2,11,1)))+SUM(MID(A2,2,1),MID(A2,4,1),MID(A2,6,1),MID(A2,8,1),MID(A2,10,1)))

I tried your suggestion and it works great. Thank you!
Just a thing, in code below I used .text instead of .value
(I would not know how to declare lcv otherwise). Is this correct?

Function UPCcheck(lc As Range)
' lc = cell on the left
Dim lcv As String
Dim OddSum, EvenSum As Integer
lcv = lc.Text
With Application
If Len(lcv) = 11 Then
OddSum = .Sum(Mid(lcv, 1, 1), Mid(lcv, 3, 1), Mid(lcv, 5, 1),
Mid(lcv, 7, 1), Mid(lcv, 9, 1), Mid(lcv, 11, 1))
EvenSum = .Sum(Mid(lcv, 2, 1), Mid(lcv, 4, 1), Mid(lcv, 6, 1),
Mid(lcv, 8, 1), Mid(lcv, 10, 1))
UPCcheck = .Ceiling(3 * OddSum + EvenSum, 10) - (3 * OddSum +
EvenSum)
Else
Dim msg As Integer
msg = MsgBox("Input is not a 11-digit UPC", vbOKOnly, "Invalid
input")
End If
End With
End Function

> First, I think you're missing something from your worksheet function.
> =Ceiling() wants to have a second argument that tells excel to round up to the
[quoted text clipped - 46 lines]
> >
> > thanks for help

Reply to this Message

Dave Peterson - 05 Oct 2006 17:57 GMT

lc.Text will be what you see in the cell. lc.Value will be the value of the
cell.

So for instance, if I have 1 in A1, but formatted as "00000000000", I'd get:
00000000001 using .text
and
1 using .value.

I might have used:

lcv = left(string(11,0) & lc.value, 11)

So that I could keep the cell formatted as General and still check 1234.

> Sorry I made a mistake trying to simplify my formula to make my post
> readable.
[quoted text clipped - 80 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rate this thread: