Sok cellából egy cella, egy cellából sok cella - összefűzés és forgatás VBA-ban

2016. február 16. - Office Guru

Miután általában mindannyian megint újra gyűrjük a munkás hétköznapokat, ennek megfelelően itt a blogon ismét egy olvasói kérdés megválaszolásával folytatom a posztolást és a megszokott módon ezúttal is egy egyszerűnek tűnő Excel-kérdés kerül terítékre, amelynek megoldása történhet akár formulákkal is, de a probléma dimenziójából adódóan itt talán már célszerűbb VBA-kódhoz nyúlni.

A kérdés a következő: adott néhány cella, amelyben szóközzel (de akár bármi mással) elválasztva különböző szavak vannak - hogyan érjük el azt, hogy ezek a szavak külön cellákba, egymás alá kerüljenek szépen sorban?

t01.jpgA következő megoldás csak egy ötlet, szívesen olvasok más javaslatokat is, de a mostani posztban kezdjük azzal, hogy a Developer fül alatt lévő Visual Basic parancs segítségével (vagy a billentyűkombinációval) menjünk át a VBA-editorba, hiszen itt fogunk rövid kódunkon dolgozni.

Ebben a megközelítésben én most két részre bontottam a problémát, első lépésként az összes kijelölt cellát összefűzöm egyetlen cellába, majd ha ez megvan, akkor a kód második részével ezt a szóközök mentén feldarabolom és egymás alatti cellákba rendezem.

Két változó és egy konstans deklarálásával kezdem (ha a rendszeres olvasók jól figyelnek, észrevehetik, hogy egy ilyen típusú kódról már volt itt szó a múltban):

- lesz egy "concatenated" nevű változóm mint String, ide fűzöm össze a cellák tartalmát
- lesz egy konstans "dm" nevű érték, amely a szóközt fogja megtestesíteni, hiszen az összefűzött értékek között jó, ha van szóköz
- és végül lesz egy "splitter" névre hallgató Variant típusú változóm, ami akár lehetne más típus is, a Varianttal azt kívánom elérni, hogy nagyjából bármi bekerülhessen ide

t02.jpgEzután a már megismert For Each..Next ciklussal folytatódik a kód, hiszen végig kell mennünk a kijelölt celláink mindegyikén, majd szépen be kell őket fűznünk a "concatenated" változóba.

t03.jpgLátható, hogy a legelső körben, a "concatenated" változó még üres, majd ehhez az üres értékhez adjuk hozzá az első cellánk értékét, plusz a dm konstansban deklarált szóközt és ugrunk a következő cellára, ahol a "concatenated" változóban már várakozó első cellánk értékéhez adjuk hozzá a következőt.

Ezután egy egyszerű With segítségével fogjuk a kijelölt tartományunkat, a benne szereplő összes cellát kitöröljük, majd a kijelölés első cellájába beletesszük a "concatenated" változó értékét.

t04.jpgA With utasítást ne felejtsük el lezárni egy End With paranccsal.

Ezután jön a kódunk második része, amelyben első lépésként feltöltjük a fentebb deklarált "splitter" változónkat az előbb megszületett első cellánkban szereplő érték feldarabolásával megalkotott tömbünkkel, ami nem másból fog állni, mint a szóközök mentén elválasztott szavainkból.

t05.jpgA kódból most már csak egyetlen sorunk van hátra, méghozzá az egész művelet kulcsa. Ehhez először meg kell ismernünk a Range.Resize tulajdonságot, amely nem tesz mást, mint a megadott cellatartományt méretezi át a mögötte zárójelben megadott sorszám és oszlopszám szerinti tartományba.

Ismernünk kell továbbá az UBound és az LBound funkciókat is, előbbi segítségével egy tömb legnagyobb elemét illetve elemeinek számát tudjuk meghatározni, utóbbi segítségével pedig egy tömb legkisebb elemét. Figyelni kell arra, hogy a sorszámozás tömbünkben mindig a nulláról indul, azaz az UBound mindig eggyel többet hoz ki, mint ahány elemünk van a tömbben, az LBound pedig mindig nullát ad alapesetben.

Ergó mit fogunk tenni? Az egyes számú cellánkat úgy méretezzük át, hogy annyi sorra dobáljuk szét, amennyi szóból a "splitter" változónk áll, de ezt még meg kell bolondítanunk egy

Application.Transpose(splitter)

utasítással, ami a szétdarabolt első cellánk értékeit sorokba rendezi, hasonlóan az Excelből elérhető Transpose típusú beillesztéshez.

t06.jpgÉs ha ezt lefuttatjuk kijelölt celláinkra, máris látjuk az eredményt:

t07.jpg

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr508396844

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

toportyánféreg 2016.02.20. 17:46:37

bár értem én, hogy jót tesz a tanulásnak a begépelés, de nem lehetne a forráskódokat szövegként is kitenni, hogy egyszerűbb legyen kipróbálni?

toportyánféreg 2016.02.20. 18:00:46

+tegyük hozzá, hogy 20 ilyen sor alatt vsz. egyszerűbb "kézzel" megoldani:
1. szövegból oszlopok - a space karakterre
2. tényleg kézzel egy sorba másolni mindent (de ez kimaradhat)
3. Ctrl C - beillesztés transzponálással

mindez a példánál 50 sec

toportyánféreg 2016.02.20. 22:43:45

@toportyánféreg:
Sub pr()
Dim v As Variant
For Each cell In Selection
v = v + cell.Value
Next cell
Selection.Cells(1) = v
End Sub