Replace (Office 2003 )

Topic

New Reply

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

Reply | Quote

Replies

Hello Francis,

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

Status	Description
1	Completed
2	Process
3	Failed

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hi Hans,

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

cheers, francis

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote