I have a 100 different catagories in one collumn and I want an Excel macro to generate a abbreviation for each different catagorie in another collumn in order to have a better overview.
I am new to the whole macro writing thing but found a way to do it using this code:
Sub catagories()
'
'catagories Macro
' Macro recorded
'
Function CalcValue(pVal As String) As Long
If pVal = "CATAGORY1" Then
CalcValue = 1
ElseIf pVal = "CATAGORY2" Then
CalcValue = 2
ElseIf pVal = "CATAGORY3" Then
CalcValue = 3
ElseIf pVal = "CATAGORY4" Then
CalcValue = 4
ElseIf pVal = "CATAGORY5" Then
CalcValue = 5
ElseIf pVal = "CATAGORY6" Then
CalcValue = 6
ElseIf pVal = "CATAGORY7" Then
CalcValue = 7
ElseIf pVal = "CATAGORY8" Then
CalcValue = 8
ElseIf pVal = "CATAGORY9" Then
CalcValue = 9
ElseIf pVal = "CATAGORY10" Then
CalcValue = 10
Else
CalcValue = 0
End If
End Function
This works, but instead of numbers I want tekst (exemple: when it's Catagory 1 the "Callcvalue should be "HS" instead of "1")
I am probably making a very basic mistake here but I have tried everything I could think off and cannot find a way to fix it.
Can you please help me???
Bookmarks