Populate one column based on values of other column?

[Moubasher82]

Good day friends,

I have a sheet which has two columns, one is the Column "A" which is the Nationality, and column "B" which is the Language.

I need to populate the language column "B" based of the nationality on column "A" as below:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Nationality[/TD]
[TD]Language[/TD]
[/TR]
[TR]
[TD]ESP[/TD]
[TD]ES[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]EN[/TD]
[/TR]
[TR]
[TD]BRA[/TD]
[TD]PT[/TD]
[/TR]
[TR]
[TD]EGY[/TD]
[TD]AR[/TD]
[/TR]
</tbody>[/TABLE]

I have a script which was working fine until recently giving me memory error, the script is:

Sub LanguageGenerator()
    'Language Column Generator
        Dim Bits
        Dim i As Long


        Const Lbls As String = "ES,ES,GB,EN,RU,RU,BR,PT,IT,IT,AR,ES,FR,FR,UA,RU,NL,EN,PT,PT,MX,ES,US,EN,PL,PL,IE,EN,TR,EN,AU,EN,CO,ES,DE,EN,DK,EN,JP,JP,LK,EN,RO,EN,VE,ES" '<- Add more as required
        
        Application.ScreenUpdating = False
        Bits = Split(Lbls, ",")
        With range("A1:A" & range("l" & Rows.Count).End(xlUp).Row)
            For i = 0 To UBound(Bits) Step 2
                .AutoFilter Field:=1, Criteria1:="*" & Bits(i) & "*"
                .Offset(, 1).SpecialCells(xlVisible).Value = Bits(i + 1)
            Next i
            .AutoFilter
            .Offset(, 1).Cells(1).Value = "Language"
        End With
End Sub

The error is :

There isn't enough memory to complete this action.
Try using less data or closing other application.
......end of error massege

It was working fine before but suddenly I don't know it is giving this error message.

Please help me with another script or a how tofix to this error message.

Thank you very much

 

[mole999]

does it have to be vba, you could use a vlookup or maybe a lookup

 

[Moubasher82]

Thanks Mole for the reply, yes it is a script I run and this is part of the script to generate the final file.

 

[mole999]

i've just recreated you display, I have no idea how it determines ESP = ES, the first thing it did was post two rows of ES of over 16,000 columns

so if it did work before what has changed

 

[Moubasher82]

I really don't know, it was working before but I don't remember to change anything, or maybe something got changed and I can't remember.

is there is any other script that could achieve what I am looking for ??

 

[mole999]

what do old archive files show

 

[Moubasher82]

Hello Mole, what old archives files you are referring to? you mean the files that I was using before ??

well, it is a manifest which contains people information and one of the columns is the person nationality and the language column is empty and I need to populate it according to each person nationality.

the old archives were around 1700 rows for each manifest and I tested it now and worked just fine, but with the same manifest with only around 20 persons, it gives this memory error.

That is really weird ?