Results 1 to 4 of 4

Thread: Little Puzzle

  1. #1
    Join Date
    Feb 2009
    Posts
    19

    Unanswered: Little Puzzle

    Halo Pro...

    I've some puzzle to work with, but i don't know how to write it in vba.

    Ok,i've a set of numbers : 5439 (actually 0000 - 9999)

    1st.
    i want to extract the '5439' (data input) to be :9345

    5439 ----> 9345

    2nd
    i want to seperate 9345 into three set of data.

    Data set 1 : 9
    Data set 2 : 3
    Data set 3 : 45

    -----scenario-----
    Ok. Assume that i input a set of data = 5439

    with vba help, 4 set new data will be produced.

    Set data 1 = 9345
    Set data 2 = 9
    Set data 3 = 3
    Set data 4 = 45


    That it, could pro help me with this? Thank you pro...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you want to meddle with an existing 4 digit number and reformat it, then you want to split it again for some purpose

    To renumber I'd suggest a VBA function
    eg
    Code:
    public function ReformatString(aVariant as variant) as string
    'this function chops up the supplied string representation of a number (avariant)
    'and returns a mangled version of the original
    ReformatString = "" 'set up our default return condition
    if we have crap data then return the compliment, crap out
    if isnull(avariant) or len(avariant)<>4 then exit function
    'ok so we want to make string 1234 = 4321
    returnstring = right$(avariant,1) & mid$(avariant,3,1) & & mid$(avariant,2,1) & left$(avariant,1)
    end function
    Code:
    public function GetElement(aVariant as variant, index as integer) as string
    'this function rreturns the specified element from a supplied 4 digit string representation fo a number
    GetElement = "" 'set up our default return condition
    if we have crap data then return the compliment, crap out
    if isnull(avariant) or len(avariant)<>4 then exit function
    'the element can be only 1 of 4 types
    select case index
    case 1 ' then we want to reformat the the number
     GetElement= right$(avariant,1) & mid$(avariant,3,1) & & mid$(avariant,2,1) & left$(avariant,1)
    case 2 'then we want the 4th digit
     GetElement = mid$(avariant,4,1)
    Case 3 'then we want the 3rd digit
     getelement = mid$(avaraint,3,1)
    Case 4:'the we want digits 1 & 2
      get element = mid$(avariant,2,1) & mid$(avariant,1,1)
    'case else
    'do nothing, we have already set our default return condition
    end select
    'jobsagoodun....
    end function
    to call the function do so with the number required. if its currently an integer then use the format
    eg getelement(format(mynumber,"0000"),1)
    it is your responsibility to make sure the you check the return of the function. if it returns an empty string then you have supplied duff data, it is up to the calling code to work out how to fix that problem
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2009
    Posts
    19

    forget to mention...

    Halo pro, thanks for your attention...

    Yes... could you plz upload a sample... i am bit confussed.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no I've uploaded enough

    call the function with your data
    Set data 1 = 9345
    Set data 2 = 9
    Set data 3 = 3
    Set data 4 = 45
    lets assume
    your data is in a column called myColumn
    you want the reformatted data in a series of varairables called myVaraiable

    'check if the data is valid, all we can check on is that the source data
    'make sure the data is not null and is exactly 4 characters long
    if not isnull(mycolumn) and len(mycolumn)=4 then
    myVariable1=getelement(mycolumn,1)
    myVariable2=getelement(mycolumn,2)
    myVariable3=getelement(mycolumn,3)
    myVariable4=getelement(mycolumn,4)
    else 'else set the destination variable to be empty strings
    myvariable1=""
    myvariable2=""
    myvariable3=""
    myvariable4=""
    end if

    The reformat string does a simple manipulation that fucntionally is the equivalent of.
    ReformatString(mycolumn) is fnctionally the same as myVariable1=getelement(mycolumn,1)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •