A Replace funkció használata VBA-ban

replace funkció használta Excel VBA-ban

A ‘replace’ funkció az Excel VBA-ban egy beépített, előre definiált funkció. Ha teljes szöveget, szövegrészleteket, vagy karaktereket akarunk kicserélni, akkor nagyban megkönnyíti a dolgunkat. Azonban vannak hiányosságai is, amiket a apróbb trükkel meg lehet kerülni.

Olvasói kérésre született a cikk, viszont valóban hiánypótló a téma a blogon.

Gyakorlati példa olvasói levél alapján

Feladat

Van egy táblázatunk több ezer sorral. Tegyük fel, hogy “A” oszlop soraiban egy 9 karakterből álló azonosító szerepel, minden sorban más-más. Ezek az azonosítók tartalmazhatnak betűt és számot is.

Feladatunk, hogy egy makróval a 9 karakteres azonosítókban “*” (csillag) karakterre cseréljük az 1., a 4. és a 9. karaktert.

Magát a cserét a “replace” VBA függvénnyel fogjuk végrehajtani, azonban kell majd egy kis trükk.

Magyarázat

Először is, hogy rugalmassá tegyük, a cserélendő “*” (csillag) karaktert egy változóba mentjük el, valamint az “A” oszlopot is. Később ha változtatni kell valamit, akkor ne kelljen mindenhol a kódban átírni, elég legyen csak egy helyen megtenni ezt.

Második lépés egy for ciklus lesz, amiben végigmegyünk az első sortól (vagy ha van fejléc, akkor a második sortól) az utolsó sorig, amiben érték szerepel. A cikluson belül fogjuk az éppen aktuális sor cellájában lévő karaktereket kicserélni, azaz a cikluson belül lesz minden más kódunk.

Viszont, tegyünk bele még egy ellenőrzést is, azaz vizsgáljuk meg egy if elágazással, hogy az aktuális sor cellájában valóban 9 karakternyi érték van e. Ha igen, akkor csak ott akarjuk kicserélni a karaktereket, ha nem, akkor színezzük ki azt a cellát sárgára (vagy bármi egyebet is tehetünk)

És itt elérkeztünk a cseréhez.

Nézzük meg először az első karakter cseréjét tartalmazó kódunkat:

Cells(i, oszlop).Value = replace(Cells(i, oszlop).Value, Left(Cells(i, oszlop).Value, 1), karakter, 1, 1)

Cells(i, oszlop).Value = a ciklusunkban szereplő aktuális cella értéke legyen egyelő az azt következő kifejezéssel. “i” jelöli az aktuális sort, míg az “oszlop” változó tartalmazza az elején meghatározott “A” oszlop elnevezést

replace( a függvényünkben 5 paramétert használunk, ebből 3 kötelező, 2 pedig opcionális, de használnunk kell jelen esetben. Replace(expressionfindreplace, [ start, [ count, [ compare ]]])

Cells(i, oszlop).Value, ez az első paramétern, az expression, azaz ebben a string-ben keresem a kifejezést/karaktert, amit le akarok majd cserélni. Ez ugye az adott cellánk értéke lesz

Left(Cells(i, oszlop).Value, 1), ez a második paraméter, a find, azaz ezt a kifejezést/karakter keressük. Jelenleg itt szintén egy beépített függvényt használunk, méghozzá a Left() függvénnyel vizsgáljuk meg, hogy a stringünk-nek mi az első karaktere. Hiszen ugye az első karaktert akarjuk most lecserélni.

karakter, ez a harmadik kötelező paraméter, a replace, azaz erre a stringre akarjuk lecserélni az expression-t. Azaz ez jelenleg a “*” (csillag) karakterünk.

1, ez a negyedig paraméter, a start. Ez már nem kötelező, de jelen esetben szükséges. Ez a kezdőpozíciót adja meg, hogy honnan kezdődjön a keresés. Jelen esetben nekünk az első karaktertől kell. Fontos megjegyezni itt, hogy ez azt is eredményezi majd, hogy az innentől számított értéket adja vissza. Tehát pl. ha a szavunk ALMA és itt 1 szerepel, akkor a végeredmény ALMA lesz, de ha itt 3 szerepel, akkor levágja az első két karaktert és csak MA lesz az eredmény, mert a 3. karaktertől számol.

1) az ötödik paraméter, a count, azt adja meg, hogy hányszor kell lecserélni a karaktert. Jelen esetben csak egyszer akarunk cserélni. Tehát ha a szavunk ALMA és 1 az értéke a paraméternek, akkor csak az első A betűt cseréli le, mert az első A betű az ALMA szóban. Viszont ha 2 az értéke, akkor mindkét A betűt kicseréli az ALMA szóban.

Ez eddig könnyebb volt, viszont nézzük meg mi van ha a negyedik illetve a kilencedik karaktert akarjuk lecserélni. Az egyszerűség kedvéért csak a 4-et mutatom meg, mivel ugyanaz a metódus lesz mindkettő.

Cells(i, oszlop).Value = Left(Cells(i, oszlop).Value, 3) & replace(Cells(i, oszlop).Value, Mid(Cells(i, oszlop).Value, 4, 1), karakter, 4, 1)

Három fontos változás van.

A replace() függvény start paramétere 4 lesz, azaz a 4. karaktertől kezdődően akarjuk lecserélni egyszer (count paraméter 1 marad) a cellánk 4. karakterét.

Ezt a negyedik karakter ismét egy beápített függvénnyel határozzuk meg, csak most nem a Left(), hanem a Mid() fügvénnyel. Mid(Cells(i, oszlop).Value, 4, 1)

Viszont mivel a start paraméterünkbe ha 4-et írunk akkor levágja az első 3 karaktert, így szükséges manuálisan hozzáadni a cellánk string-jének első három karakterét. Azaz & jellel az a replace() fügvényünket összefűzzünk az cellánk első három karakterével. ( a 9. karakter cserénél az első 8 karakterrel)

Left(Cells(i, oszlop).Value, 3) & replace(

Megoldás

Íme a teljes kód:

Sub replace_funkcio()
' Első lépésként felvesszük a változóinkat

'Milyen karakterre akarjuk lecserélni
Dim karakter As String
karakter = "*"

'Melyik oszlopban vannak a string-jeink
Dim oszlop As String
oszlop = "A"

'Meghatározzuk a táblázatunk utolsó sorát, azaz eddig fog lefutni a ciklusunk majd
Dim utolso_sor As Integer
utolso_sor = Cells(Rows.Count, oszlop).End(xlUp).Row

'Végig futunk egy for ciklussal a kívánt oszlopon.
For i = 1 To utolso_sor

'Ellenőrizzük, hogy valóban mindenhol 9 karakteres szöveg van e a cellában. Ha nem, akkor kisárgázzuk a cellát az "else" ágban.
If Len(Cells(i, oszlop).Value) = 9 Then
    'első karaketert cseréljük ki
    Cells(i, oszlop).Value = replace(Cells(i, oszlop).Value, Left(Cells(i, oszlop).Value, 1), karakter, 1, 1)
    
    'negyedik karaktert cseréljük ki
    Cells(i, oszlop).Value = Left(Cells(i, oszlop).Value, 3) & replace(Cells(i, oszlop).Value, Mid(Cells(i, oszlop).Value, 4, 1), karakter, 4, 1)
    
    'kilencedik karaktert cseréljük ki
    Cells(i, oszlop).Value = Left(Cells(i, oszlop).Value, 8) & replace(Cells(i, oszlop).Value, Mid(Cells(i, oszlop).Value, 9, 1), karakter, 9, 1)
    
Else
    Cells(i, oszlop).Interior.ColorIndex = 6
End If

Next

End Sub

A feladatot persze több másik módon is meglehet oldani.

Mennyire találtad hasznosnak ezt cikket?
[Összes szavazat: 1 Átlag értékelés: 5]