lundi 1 mars 2021

VBA replace last 3 symbols in a CELL to COMBOBOX value

I have a code that finds cells that start with "UZL." and is 16 symbols long:

Dim vDB As Variant
Dim rngDB As Range
Dim s As String, sReplace As String
Dim i As Long
Dim Ws As Worksheet

Set Ws = Sheets("BOM")
With Ws
    Set rngDB = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
End With
vDB = rngDB
For i = 1 To UBound(vDB, 1)
    s = vDB(i, 1)
    If s Like "UZL.*" And Len(s) = 16 Then
        sReplace = Me.LanguageBox.Value
        s = Replace(s, "ENG", sReplace)
        vDB(i, 1) = s
    End If
Next i
rngDB = vDB

In which there's a line:

s = Replace(s, "ENG", sReplace)

That change's only the cells that has ".ENG" in them (always at the end), but I need to define it to not be dependant on that. Instead of .ENG it probably should say "change last three symbols to what the combobox says". There's a combobox with 249 difefrent options for what there last three symbols may be changed and I need to be able to change them over and over again, say from ENG to AUS to RUS and so on.

Would someone please help me modify this code to make changes over and over again?

Workbook: https://easyupload.io/37tztt

Aucun commentaire:

Enregistrer un commentaire