Prev: SEARCH IS NOT WORKING
Next: cell formula question
From: Amin on 27 May 2010 16:55
Hi Rick,

I'd really like to thank both you and Helmut.

When I first posted the original post I was sure I ticked the "notify me of
replies" option but when I didn't get any and I tried looking for my post
back in the pile of new posts I thought I lost it.

I made those replies to the old thread after starting the new one.

I'd like to thank you experts again for your input.

Regards,
Amin



"Rick Rothstein" wrote:

> Oh, by the way, I'm not sure if you caught it or not, but we may be having
> this conversation solely between us<g>... the OP posted the same question
> again today and started his message out with this line...
>
> "(I've previously asked this question but didnt get any replies.)"
>
> Yes, I know the OP has participated in this thread... twice... and I pointed
> this out to him in my response to his newer posting; I have no explanation
> for his comment as he has not responded back in his newer thread yet.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
> news:eQjf4539KHA.4308(a)TK2MSFTNGP04.phx.gbl...
> > Rick,
> >
> > you are right about trailing zeroes, I should have used
> > EncodeCosts = Format(Costs, "0.00")
> > instead of CStr(Costs), but your solution won't work on my
> > system or on others in countries with a decimal comma without
> > changing the code.
> > I admit your code is shorter. From his questions I guessed Amin
> > isn't an experienced programmer and I think my code is easier
> > to understand and to adjust to his needs.
> > By changing one character in my code the value 832.25
> > would produce
> > ERT-TV or ERT*TV or ERT TV instead of ERT.TV
> > Personally I like the "*" best.
> >
> > Helmut.
> >
> >
> > "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> schrieb im
> > Newsbeitrag news:utHZJO29KHA.4564(a)TK2MSFTNGP05.phx.gbl...
> >> You need to change the data type for your Costs argument to String... if
> >> you leave it as a numeric data type, trailing zeroes will be lost after
> >> the decimal point.
> >>
> >> With that said, here is your another (slightly shorter) approach to do
> >> what your code does...
> >>
> >> Function EncodeCosts(Costs As Currency) As String
> >> Dim X As Long
> >> EncodeCosts = CStr(Costs)
> >> For X = 1 To Len(EncodeCosts)
> >> If Mid(EncodeCosts, X, 1) <> "." Then Mid(EncodeCosts, X, 1) = _
> >> Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
> >> Next
> >> End Function
> >>
> >> We can reduce the amount of code a little bit more if we remove the
> >> decimal point from the encoded number, displaying nothing in its place
> >> (as I suggested as a possible encoding method in my prior response in
> >> this thread)...
> >>
> >> Function EncodeCosts(Costs As String) As String
> >> Dim X As Long
> >> EncodeCosts = Replace(CStr(Costs), ".", "")
> >> For X = 1 To Len(EncodeCosts)
> >> Mid(EncodeCosts, X) = Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
> >> Next
> >> End Function
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >>
> >> "Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
> >> news:OsSOEN09KHA.1892(a)TK2MSFTNGP05.phx.gbl...
> >>> Amin,
> >>>
> >>> try this:
> >>>
> >>> Function EncodeCosts(ByVal Costs As Currency) As String
> >>> Dim strCosts As String, strTmp As String
> >>> Dim l As Integer, i As Integer
> >>> Dim vArr As Variant
> >>>
> >>> vArr = Array("Z", "O", "T", "R", "F", "V", "X", "S", "E", "N")
> >>>
> >>> strCosts = Trim(CStr(Costs))
> >>> l = Len(strCosts)
> >>> EncodeCosts = Space(l)
> >>> For i = 1 To l
> >>> strTmp = Mid(strCosts, i, 1)
> >>> If strTmp = "." Or strTmp = "," Then
> >>> Mid(EncodeCosts, i, 1) = "."
> >>> Else
> >>> Mid(EncodeCosts, i, 1) = vArr(Val(strTmp))
> >>> End If
> >>> Next i
> >>> End Function
> >>>
> >>> The above works regardless of the locale. Because CStr is localized,
> >>> it returns a string containing the local decimal sign. The code always
> >>> returns a string with a dot, but you can easily replace it with any
> >>> other
> >>> sign or character. If you remove the line
> >>> Mid(EncodeCosts, i, 1) = "."
> >>> you'll get a space instead of the decimal point.
> >>>
> >>> Helmut.
> >>>
> >>>
> >>> "Amin" <Amin(a)discussions.microsoft.com> schrieb im Newsbeitrag
> >>> news:8E6BA483-F87F-4E7C-AD52-749F0362FB99(a)microsoft.com...
> >>>> Hello Helmut,
> >>>>
> >>>> Thanks for your response.
> >>>>
> >>>> It doesn't really matter which logic to use I still need the CODE to do
> >>>> it,
> >>>> I have an item list with more than 1000 items.
> >>>>
> >>>> Any ideas about the code?
> >>>>
> >>>> Thanks again,
> >>>>
> >>>>
> >>>> "Helmut Meukel" wrote:
> >>>>
> >>>>> Hmm,
> >>>>>
> >>>>> one problem with encoding is to obscure the value, so guessing
> >>>>> the real value isn't easy and nonetheless make it siple for YOU
> >>>>> to read the real value. Just using A to I for 1 to 9 makes decoding
> >>>>> for others easier than necessary.
> >>>>> How about using
> >>>>> 0 = Z(ero)
> >>>>> 1 = O(ne)
> >>>>> 2 = T(wo)
> >>>>> 3 = (th)R(ee)
> >>>>> 4 = F(our)
> >>>>> 5 = (fi)V(e)
> >>>>> 6 = (si)X
> >>>>> 7 = S(even)
> >>>>> 8 = E(ight)
> >>>>> 9 = N(ine)
> >>>>> I would probably leave one number as a number (3) and use
> >>>>> A for 8.
> >>>>> e.g. 832.05 = A3T.ZV
> >>>>> 96.74 = NX.SF
> >>>>>
> >>>>> Helmut.
> >>>>>
> >>>>>
> >>>>> "Amin" <Amin(a)discussions.microsoft.com> schrieb im Newsbeitrag
> >>>>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E(a)microsoft.com...
> >>>>> > Hello Experts,
> >>>>> >
> >>>>> > At my shop I'm printing product labels from an Excel sheet.
> >>>>> >
> >>>>> > I'd like to add the cost field to the label but the problem
> >>>>> > everybody would
> >>>>> > be able to know my cost for an item unless I encode it, so I thought
> >>>>> > of
> >>>>> > converting numbers to characters for example:
> >>>>> >
> >>>>> > 0 = Z
> >>>>> > 1 = A
> >>>>> > 2 = B
> >>>>> > 3 = C
> >>>>> > 4 = D
> >>>>> >
> >>>>> > So, if the cost of an Item is 322.04 the field would show CBB.ZD
> >>>>> >
> >>>>> > Any thoughts?
> >>>>> >
> >>>>> > Thanks in advance
> >>>>>
> >>>>>
> >>>>> .
> >>>>>
> >>>
> >>>
> >
> >
> .
>
First  |  Prev  | 
Pages: 1 2 3 4
Prev: SEARCH IS NOT WORKING
Next: cell formula question