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.

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Keg[/TD]
[TD="class: xl66, width: 64"]Bond[/TD]
[TD="class: xl66, width: 64"]Pie[/TD]
[TD="class: xl67, width: 64"]Off[/TD]
[/TR]
[TR]
[TD="class: xl64"]AR[/TD]
[TD="class: xl64"]AF[/TD]
[TD="class: xl64"]Web[/TD]
[TD="class: xl64"]PR[/TD]
[/TR]
[TR]
[TD="class: xl63"]Web[/TD]
[TD="class: xl63"]PR[/TD]
[TD="class: xl63"]AR[/TD]
[TD="class: xl63"]AF[/TD]
[/TR]
[TR]
[TD="class: xl63"]Web[/TD]
[TD="class: xl63"]PR[/TD]
[TD="class: xl63"]AR[/TD]
[TD="class: xl63"]AF[/TD]
[/TR]
[TR]
[TD="class: xl63"]Web[/TD]
[TD="class: xl63"]PR[/TD]
[TD="class: xl63"]AR[/TD]
[TD="class: xl63"]AF[/TD]
[/TR]
[TR]
[TD="class: xl63"]Web[/TD]
[TD="class: xl63"]PR[/TD]
[TD="class: xl63"]Web[/TD]
[TD="class: xl63"]PR[/TD]
[/TR]
[TR]
[TD="class: xl63"]PR[/TD]
[TD="class: xl63"]AR[/TD]
[TD="class: xl63"]AF[/TD]
[TD="class: xl63"]PR[/TD]
[/TR]
[TR]
[TD="class: xl63"]Web[/TD]
[TD="class: xl63"]PR[/TD]
[TD="class: xl63"]Web[/TD]
[TD="class: xl63"]PR[/TD]
[/TR]
[TR]
[TD="class: xl63"]AR[/TD]
[TD="class: xl63"]Web[/TD]
[TD="class: xl63"]PR[/TD]
[TD="class: xl63"]NA[/TD]
[/TR]
</tbody>[/TABLE]

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