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???

The easy answer is that instead of setting CalcValue = 1 you would set CalcValue = "HS". However, that doesn't address the issue of hardcoding 100 separate abbreviations. Based on your example, it would seem that it would almost be easier to simply type your abbreviations into the cells rather than writing all of that code. I'm also not sure why you are using a Function. I must be missing something. Anyway, if your examples were the actual Catagory names and abbreviations, I would have done something like this: Assuming you have Catagory1 - Catagory10 in A1:A10, and you want the "trailing digits" (1 - 10) in B1:B10, this code would do that. It uses the Right function to grab all the characters after "Catagory". Sub CatAbb() For CatRw = 1 To 10 Cells(CatRw, 2) = Right(Cells(CatRw, 1), Len(Cells(CatRw, 1)) - 8) Next End Sub Perhaps you could do something similiar with your actual catagory names. Basically you're looking for something "common" from each name and/or abbreviation that you can loop on instead of making an individual line of code for each item. Post some real examples and I'll see what I can offer.

I tried "HS" instead of 1 but then i end up with an #VALUE!, I guess because I cannot calculate a value using text or something? The reason I am looking for a code is simple, I have to catagorise over 5.000 lines. I will try to make my problem more tangible for you: I am making a document for my photography collection. Every picture I made is on one line and for each picture I discribed a location. (in column D) I would like to be able to sort by location but I have locations called "Maastricht" and "school". Since my school is located in Maastricht I would like those catagories both to have the abbreviation "MS" wich would make it easy to sort. My document has 120 lines of "Maastricht" and 560 "school" so my catagories are not all on the first ten lines. I would love to have a code for approximately 10 catagories and i will copy-paste and adjust the other catagories in (It will take some time but less then typing everything myself ;)) I really hope this is more usefull for you

Ah! After another night off puzzling I have a better idea of what you ment. I put all my catagories in another sheet and used a vertical lookup, wich was actually quite easy and not to much work.. you were right I did not need to use a macro after all. Thanks a lot for your help! :D

I'm glad you figured out a simple way to do this. re: The reason I am looking for a code is simple, I have to catagorise over 5.000 lines. Even though you solved it out without code, that wasn't really the question I was asking. I was wondering why you were using a function as opposed to just a macro that you would run against your list. There's a difference between a macro and a UDF (User Defined Function) even though both are written in VBA code.

haha, I thought I was using a macro... at least I created it in the macro window! I never really used VBA before, neither in a marco nor a user defined function so basicly I depend on my google skills and a little bit of guessing to get everything working. But it's good to know that VBA code can also be in a function. I think it will make my next excel adventure a little less complicated ;) Thanks again!