Posts by jonny

    Sorry, Carim, just saw your code. Thank you.

    Don't know why , but somehow I'm not getting notifications once the answer is posted.


    Anyway the code is not grouping consecutive locations. By applying a code on U2, U6, U12, U13 I've gotten U12, U13, U2, U6.

    Where the expected result is U2, U6, U12-U13

    The file cannot be attached due to legal issues, however here is the file content in table format:


    Component Location Location (Short Form)
    COMP1 R1, R2, R4, R4, R5, R6, R7, R8, R9, R10 R1-R10
    COMP2 C98, C99, C103, C104, C105, C109, C11, C111, C115, C116, C117, C121 C98-C99, C103-C105, C115-C117, C121-C125, C129-C130
    COMP3 U2, U6, U12, U13 U2, U6, U12-U13
    COMP4

    Each electronic components on electronics PCB has a reference designator (unique location). The reference designator usually consists of one or two letters followed by a number, e.g. R13, C1002.

    I'm trying to create a UDF that gets a string of locations and returns a group of consecutive locations as shown:

    Examples:

    1. C1, C2, C3 ==> C1-C3

    2. R4, R1, R7, R5, R3 ==> R1, R3-R4, R7

    3. LD100, LD101, LD102, LD103, LD104, LD105, LD107 ==> LD100-LD105, LD107

    Thanks, RoyUK, much appreciate it!


    Turned your code into UDF:

    Code
    1. Function CONVERTNUMERIC(rCl String) As Double
    2. If InStr(1, rCl , "R") > 0 Then CONVERTNUMERIC= Replace(rCl , "R", ".")
    3. If InStr(1, rCl , "K") > 0 Then CONVERTNUMERIC= Replace(rCl , "K", "") * 100
    4. If InStr(1, rCl , "M") > 0 Then CONVERTNUMERIC= Replace(rCl , "M", "") * 100000
    5. End Function

    I wish to apply a password for internal documents (PPTX, DOCX, XLSX..) , that will not be opened out of company.
    I assume should be some some Window Log-In validation.


    Please your help with the code..


    Here is it to all that may be interested in Excel:



    Any idea for a same solution in PPT and DOC files?

    The below VBA code scores supplier, based on its response time, please your help to convert it to "IF" function format.


    Thank you

    Guys,
    Need to extract all the letters within the string , separated by comma.
    Now the code extracts first letters only.
    Please your help with modifying an user defined function:


    Input: HKJ234CBV546LL
    Output (is): HKJ
    Output (ought): HKJ,CBV,LL

    Guys, Need to extract all the letters within the string , separated by comma. Now the code extracts first letters only. Please your help with modifying an user defined function:

    Code
    1. Function ExtractLetter(str As String) Dim TDCode As String Dim result As String On Error GoTo ErrHandler: With CreateObject("vbscript.regexp") .Pattern = "[A-Z]+" ExtractLetter = .Execute(str)(2) End With Exit Function ErrHandler: End Function

    Input: HKJ234CBV546LL Output (is): HKJ Output (ought): HKJ,CBV,LL