custom function

[Guest]

hi all,

i have a range of data that will be something like this:

1 case
10 cases
100 cases

my cell formula is
=SUMPRODUCT((CustomerOrdersCode=H1)*(formatQuantity(CustomerOrdersQuantity)))
i am using a cell formula with SUMPRODUCT to determine the total number of
cases
i am having trouble stripping the text from the data and converting it to an
integer
i thought to use a function like:

Public Function formatQuantity(q As Range) As Integer
Dim i As Integer, c
For Each c In q.Cells
For i = 1 To Len(c.value)
If Mid(c.value, i, 1) = " " Then Exit For
Next i
formatQuantity = CInt(Mid(c.value, 1, i))
Next
End Function

i'm not exactly sure how to modify it to accept/return the correct values.

any help - or possibly a better idea?

tia!

J

 

[Guest]

Hi,

The follwing will work assuming there is a blank between the number and text)

=INT(LEFT(A1,FIND(" ",A1,1)))

Caters for any integer numeric value

OR as a UDF


Public Function formatQuantity(q As Range) As Integer
Dim i As Integer
i = InStr(1, q.Value, " ")
formatQuantity = CInt(Left(q.Value, i - 1))

End Function

OR your original

Remove the "For each c " loop and then enter the UFD in each cell and copy
down.
Routine assumes values are always start with 1 - is this correct for always?

=formatQuantity(A1)


Public Function formatQuantity(q As Range) As Integer
Dim i As Integer, c

For i = 1 To Len(q.Value)
If Mid(q.Value, i, 1) = " " Then Exit For
Next i
formatQuantity = CInt(Mid(q.Value, 1, i))

End Function


HTH

 

[Guest]

I think you can do it without the need for a custom function:
=SUMPRODUCT((CustomerOrdersCode=H1)*(VALUE(LEFT(CustomerOrdersQuantity,FIND(" ",CustomerOrdersQuantity)))))

 

[Guest]

Yup! that's exactly what i was looking for!

thanks!

J

 

[Guest]

Hi,

thanks for the help - using the LEFT and FIND worked without the need to
create a UDF.

thanks again!

J