Forum Discussion
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.
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 SubMaybe with these lines of code. In the attached file you can run the macro to fill in the abbreviations.
- Where would I fill in code in on Excel?
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.
- is there a simpler way with no code?
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.
