Forum Discussion

anniethoang's avatar
anniethoang
Copper Contributor
Aug 01, 2023

Automatic content fill-in in a new column

Hi - I need help categorizing content/ auto-fill from the column A to column B. For example, all the cells in column A with "Argentina" automatically abbreviates to "AR" in corresponding row in column B. Is there a quicker way to automatically fill in column B cells based on content of column A instead of manually typing in the countries abbreviations? Like if a cell in column A contains the word "Argentina", how can I set it up where the cell corresponding to the row of column B automatically fills in "AR"?

 

Another example:

All cells in column A that says "Belgium" automatically abbreviates to "BE" in the corresponding cell to the row in column B. See screenshot for example. Shortcut tips please. NO drag downs solution since this is heavy file. 

 

excel
Formulas and Functions

5 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 01, 2023

    anniethoang 

    Sub abbreviation()

Dim i, j As Long

j = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To j

Select Case Cells(i, 1).Value

Case Is = "argentina"
Cells(i, 2).Value = "AR"

Case Is = "aruba"
Cells(i, 2).Value = "AA"

Case Is = "belgium"
Cells(i, 2).Value = "BE"

End Select

Next i

End Sub

    Maybe with these lines of code. In the attached file you can run the macro to fill in the abbreviations.

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Aug 02, 2023

        anniethoang 

        In the worksheet you can press the keys ALT+F11 to open the VBA Editor. Then select Insert -> Module to open a new module. Now you can paste the suggested code in the module. Then you have to save the workbook as a macro-enabled workbook in order to save the code. 

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Aug 02, 2023

        anniethoang 

        You could apply INDEX and MATCH along with a reference table. INDEX and MATCH with reference table should be simpler than VBA. However the formula has to be draged down which could slow down the file.