Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace (Office 2003 )

    Hi Hans and all,

    I have a file which I download from one of our application and on some fields its shows numbers which actually represent some types and status. Is it possible to replace these numbers to their meaningfull texts in macro.

    I have use the macro recorder for this but its doesn't seem to work as there are two type with the same number .

    I have attached a file and also the result that I am seeking.

    Thanks for all your assistance.

    cheers, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Replace (Office 2003 )

    Hello Francis,

    You will have to create tables in the workbook that provide the description for the codes. For example for status:

    <table border=1><td>Status</td><td>Description</td><td align=right>1</td><td>Completed</td><td align=right>2</td><td>Process</td><td align=right>3</td><td>Failed</td></table>
    And similar for Order Type and Product type - three tables in all. These tables can be in another worksheet, if you prefer.

    Please post a workbook with these extra tables.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace (Office 2003 )

    Hi Hans,

    Long time since I have last posted. Really appreciate your help on this.
    I have posted the extra tables in Sheet 3.

    TIA

    cheers, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Replace (Office 2003 )

    That was not quite what I meant. The lookup tables should contain each lookup value only once, without duplicates.
    In the attached version, I have corrected the lookup tables and added a macro. See if it does what you want.

    Warning: because the macro replaces the original values (as you asked), you can run it only once. So make a copy of the workbook before trying the macro.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Replace (Office 2003 )

    Have you considered using Lookup functions to use the values in columns A, C and E to columns G, I and K as per the attached?
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace (Office 2003 )

    Hi Hans,

    Yes it does what I want. Thank for your time and help on this.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace (Office 2003 )

    Hi Gfamily,

    Appreciate your suggestion and help. this would be an good option and it does greatly assist me in knowing how to use Functions
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace (Office 2003 )

    Hi Hans and all

    How can I replace the data with the Vlookup if the some of the data in the attached table which doesn't need to be replace.
    for example, I want to add -L after IRPC by looking up the code 0 and at the same time I don't want to change PTT-F on the first column
    if there is a -F after the Abbreivation.

    Is this possible?

    Thanks for your help
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Replace (Office 2003 )

    Please explain in detail what you want to do. It is not at all clear from your description.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace (Office 2003 )

    Hi Hans,

    I try again to explain.Let say I have a table with the following :

    Col A Col B
    IRPC 0
    IRPC-F 0
    IRPC 2

    I want to change IRPC to IRPC-L in Col A, if there is a 0 in col B, and IRPC to IRPC-R if there is a 2 in col B.
    But the downloaded data set also included IRPC-F in col A and there is a 0 in col B which I don't want to change the IRPC-F in col A,
    How can I tell the macro to ignore the 0 in col B if in col A the data is correct such as there is already a -F after IRPC.

    Hope you understand my explaination.

    thanks in advance.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    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: Replace (Office 2003 )

    The following works. It wasn't clear from your reasoning why "BANPU" did not get the "-R" so I just chose to not change any "BANPU". If there is another reason, you can include that instead

    Steve

    <pre>Option Explicit
    Sub FrancizCode()
    Dim rCell As Range
    For Each rCell In Range("A2:A13")
    With rCell
    If InStr(.Value, "-") = 0 _
    And .Value <> "BANPU" Then
    Select Case .Offset(0, 1)
    Case 0
    .Value = .Value & "-L"
    Case 2
    .Value = .Value & "-R"
    End Select
    End If
    End With
    Next
    Set rCell = Nothing
    End Sub</pre>


  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace (Office 2003 )

    Hi Steve,

    Thank for your help and I really appreciate it. It is an error for not including the "-R" which should be included. I think I can't amend the code for this. I will try and revrt back.

    Is it too much to ask how the code works? I am in the learning process.

    thanks

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    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: Replace (Office 2003 )

    If it is an error then the code below should work (eliminating the "ignoring" of the "BANPU").

    Here are some comments in the code to explain the process

    Steve

    <pre>Option Explicit
    Sub FrancizCode()
    Dim rCell As Range
    'Check each of the cells in the range
    For Each rCell In Range("A2:A13")
    With rCell
    'Check for a dash
    If InStr(.Value, "-") = 0 Then
    'No it does not have a dash, it can be modified
    'What is the value in the cell to the right?
    Select Case .Offset(0, 1)
    Case 0
    'It is a zero, add a "dash L" to value
    .Value = .Value & "-L"
    Case 2
    'It is a two, add a "dash R" to value
    .Value = .Value & "-R"
    End Select
    End If
    End With
    'Repeat with the next cell
    Next
    Set rCell = Nothing
    End Sub</pre>


Posting Permissions

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