Function Question

  • Thread starter Thread starter freightliner2006
  • Start date Start date
F

freightliner2006

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
 
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.
 
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
 
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.
 
Back
Top