Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dividing numbers/inserting spaces (Excel 2k)

    Hi,

    I need to perform a calculation on serial numbers (changing them into barcodes) I've got a formula in an Excelsheet, is there a way to export this into code?

    Here is the formula:

    =IF(B4<>0;CONCATENATE(" ";"(";CHAR(IF(VALUE(LEFT(B4;2))<50;VALUE(LEFT(B4;2 ))+48;VALUE(LEFT(B4;2))+142));CHAR(IF(VALUE(MID(B4 ;3;2))<50;VALUE(MID(B4;3;2))+48;VALUE(MID(B4;3;2)) +142));CHAR(IF(VALUE(MID(B4;5;2))<50;VALUE(MID(B4; 5;2))+48;VALUE(MID(B4;5;2))+142));CHAR(IF(VALUE(MI D(B4;7;2))<50;VALUE(MID(B4;7;2))+48;VALUE(MID(B4;7 ;2))+142));")";" ");"")

    What it does is cut a number into pairs, perform a calculation on the pairs and place the result in a cell. The cell is then printed using a barcodefont

    Any help will be much appreciated,

    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    Will valid numbers always have 8 digits? Or can the length vary?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    Does this function do what you want?

    Function CalcBarcode(MyNum) As String
    Dim n As Integer, i As Integer
    If Val(MyNum) = 0 Then
    CalcBarcode = ""
    Else
    CalcBarcode = " ("
    For i = 1 To 4
    n = Val(Mid(Format(MyNum, "00000000"), 2 * i - 1, 2)) + 48
    If n >= 98 Then
    n = n + 94
    End If
    CalcBarcode = CalcBarcode & Chr(n)
    Next i
    CalcBarcode = CalcBarcode & ") "
    End If
    End Function

    Use it like this:

    =CalcBarcode(B4)

  4. #4
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    Hans,

    Thanks again, it works of course. Last and final problem is that the numbers start with "0" (zero) and this zero gets trimmed away while importing the.csv-file using yet another piece of your code.

    This seems to be a general Excel setting Is it possible to change this when when the code starts Excel?

    Thanks,

    Paul

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    As in the thread starting at <post:=655,400>post 655,400</post:>, change the extension of the .csv file to .txt. The OpenText method has an optional argument FieldInfo that lets you specify how you want to treat columns. For example:

    Workbooks.OpenText Filename:=strTxt, _
    DataType:=xlDelimited, SemiColon:=True, Comma:=False, _
    FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlGeneralFormat), Array(3, xlDMYFormat))

    tells Excel to import column 1 as text, column 2 as general (i.e. let Excel decide) and column 3 as dates in DMY format.

  6. #6
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    Hans,

    Once again,

    Thank you very, very much

    Paul

  7. #7
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    Hans,

    Too soon, too soon. This doesn't work. Here's the code:
    Workbooks.OpenText FileName:=strTxt, DataType:=xlDelimited, SemiColon:=True, Comma:=False, _
    FieldInfo:=Array(Array(65, xlTextFormat), Array(66, xlTextFormat), Array(67, xlTextFormat))

    It seems tom be ignored. To my frustration, Column 1 is in text format. (Without any settings to achieve this)
    What is happening?

    Paul

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    Try specifying a complete array, starting with column 1, however tedious that is.

  9. #9
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    Hans,

    Well, it's not beautiful code, but it works. Apparently you have to enter a continuous array, in shameless contrast to what Excel help says about this option.

    Thanks,

    Paul

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: dividing numbers/inserting spaces (Excel 2k)

    Yes, the online help explicitly mentions that you can specify part of the columns and that they can be in any order, but it doesn't work that way... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

Posting Permissions

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