Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transform string in code.... (2000 sr 1)

    The macro into wbook import a value from a sheet to other sheet.
    My problem is to import the column R of the source sheet into column F into target sheet in this mode:

    If into column R of sheet source is present:

    SALDO DEBITORE IRRECUPERABILE (TENTATIVO DI RECUPERO NON ANDATO A BUON FINE PER CLIENTE IRRINTRACCIABILE)

    assign to this string the code "00", and during the import into target sheet insert the code "00" into column F inested this big long string...
    ecc...

    In effect assign to all "big string" a code of 2 charachter to simplify the import and make the target sheet very professional...

    in this example are present only 5 big string so, five string for five code choice you the order...

    my idea is to "00" from "05"...

    Note: the all big string contain the first 2 caharcter different, so, the s first 2 caracher is SA assign code "00", the s first 2 caracher is CO assign code "01", ecc... like this suggestion?

    Hope ima clear...

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Transform string in code.... (2000 sr 1)

    Hello sal,

    If you put the formula:
    =MATCH('DARE_GLOB (EPUR)'!R4,'DARE_GLOB (EPUR)'!Y$4:Y$10,0)
    in cell F4 on your 'RIEPILOGO' sheet and copy it down, does this give you what you want? I know the values are different, but you can control that by changing the order of cells Y4:Y$10 on your 'DARE_GLOB (EPUR)' sheet.

    If the formula gives you the right result, you can simply use that on your 'RIEPILOGO' sheet, or you can convert it to a vba expression and output only the result.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transform string in code.... (2000 sr 1)

    Hi Macropod,
    i have solved with this code.

    But is into culumn not contain a string with the select condition, for example a string tath init with ZZ is possible to insert a condition to intecept the error and put into F a code "99"...

    Sub ESTRAI()
    Dim SourceColumn As Long
    Dim TargetColumn As Long
    Dim Row As Long
    Dim Row1 As Long

    Application.DisplayAlerts = False

    Row1 = 3

    Set TargetSheet = Sheets("RIEPILOGO")
    Set SourceSheet = Sheets("DARE_GLOB (EPUR)")

    TargetSheet.[A3:BR65536].ClearContents

    For Row = 4 To SourceSheet.UsedRange.Row + SourceSheet.UsedRange.Rows.Count - 2

    TargetSheet.Cells(Row1, 1) = SourceSheet.Cells(Row, 5)
    TargetSheet.Cells(Row1, 4) = SourceSheet.Cells(Row, 4)
    TargetSheet.Cells(Row1, 2) = Trim(SourceSheet.Cells(Row, 3))
    TargetSheet.Cells(Row1, 3) = Left(SourceSheet.Cells(Row, 2), Len(SourceSheet.Cells(Row, 2)) - 20)

    CODE = Mid((SourceSheet.Cells(Row, 18)), 1, 2)

    Select Case CODE

    Case Is = "SA"
    TargetSheet.Cells(Row1, 6) = "00"

    Case Is = "RA"
    TargetSheet.Cells(Row1, 6) = "01"

    Case Is = "CO"
    TargetSheet.Cells(Row1, 6) = "02"

    Case Is = "ER"
    TargetSheet.Cells(Row1, 6) = "03"

    Case Is = "FI"
    TargetSheet.Cells(Row1, 6) = "04"

    Case Is = "SU"
    TargetSheet.Cells(Row1, 6) = "05"

    Case Is = "IN"
    TargetSheet.Cells(Row1, 6) = "06"

    End Select

    Row1 = Row1 + 1

    Next Row

    Application.DisplayAlerts = True

    ActiveWorkbook.Save

    MsgBox ("IMPORT TERMINATO!")

    End Sub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Transform string in code.... (2000 sr 1)

    How about these inserted lines:

    Case Is = "IN"
    TargetSheet.Cells(Row1, 6) = "06"

    <font color=red>Case else
    TargetSheet.Cells(Row1, 6) = "99"</font color=red>

    End Select

    Steve

Posting Permissions

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