Need help with to assign numerical value to a particular set of data

[Euro_87]

Hi,

I am working with a set of data, which cannot be uploaded unless I convert it to a particular numeric value.

I am dealing with more than 20 such data sets spread across different cells. For example, as shown below, you will find some common datasets - AR, AF, Web, PR. Now, every dataset has a unique code, for instance - AR = 9, AF = 21, PR = 35 and Web = 13. Now what could be the best macro to use that will allow me to convert my dataset into the numerical values. Can any one please help me out with it.

Keg	Bond	Pie	Off
AR	AF	Web	PR
Web	PR	AR	AF
Web	PR	AR	AF
Web	PR	AR	AF
Web	PR	Web	PR
PR	AR	AF	PR
Web	PR	Web	PR
AR	Web	PR	NA

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Regards,
Euro_87

 

[alansidman]

Assuming all your data is in the columns A:D then the following code would work

Option Explicit


Sub ams626()
    Dim c As Range
    Dim lr As Long
    Dim rng As Range
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2:D" & lr)
    For Each c In rng
        If c = "AF" Then
            c = 21
        ElseIf c = "AR" Then
            c = 9
        ElseIf c = "PR" Then
            c = 35
        ElseIf c = "Web" Then
            c = 13
        End If
    Next c
End Sub