Adatok szétválogatása külön fülekre

Ebben a cikkben egy olyan kódot mutatok be, ami egy különböző márkájú autókról készült listát válogat szét minden márkát külön fülre pakolva.

Az Összes fülön látható a kezdeti állapot:

A többi fülön jelenleg csak a fejléc látható.

A feladat tehát az, hogy az Összes fül minden során végig haladva helyezzük a Ford márkájú autókat a Ford fülre, az Opeleket az Opel fülre, és így tovább.

A Visual Basic Editor használata, illetve az ahhoz tartozó alapfogalmak itt érhetőek el.

Hozzunk létre egy új modult, és nevezzük el modSzetvalogatas-nak.

A füleket nevezzük el a márkák alapján. A Project Explorer dobozban kattintsunk a Sheet2-re, majd a Name tulajdonságot írjuk át „shtFord”-ra. (Az sht az angol Sheet, azaz fül, rövidítése. Használhatunk más rövidítést is, például ws mint WorkSheet, de mindenképp érdemes következetesen ugyanazt használni.)

Tegyük meg ugyanezt a többi füllel is, beleértve az Összes fület. (Figyeljünk rá, hogy ékezetek nem használhatóak.)

Adjunk hozzá egy új modult. Ha szeretnénk ennek is megváltoztathatjuk a nevét. Összetettebb kódok írásakor mindenképp érdemes ezt megtenni.

Hozzunk létre egy Szetvalogatas nevű subroutine-t, és definiáljuk a változóinkat.

Mielőtt belekezdünk, gondoljuk végig a feladat menetét!

A kódunknak végig kell mennie az Összes fül minden során, meg kell állapítania, hogy az adott sor melyik márkához tartozik, azaz melyik fülre kerül, majd az adott sor tartalmát átmásolni (valójában nem másolni fogunk, azaz nem copy-paste parancsot használunk majd) a megfelelő fülre.

Először állapítsuk meg hány sorból áll az Összes fül. Ezt természetesen változóként fogjuk kezelni, hogy a kódunk tetszőleges számú sor esetén is működjön.

Deklaráljuk a változóinkat. (A változók használatát lejjebb részletezem.)

[code language=”vb”]Dim lastRowAll As Long

Dim iRowAll As Long

Dim lastRowMarka As Long

Dim strMarka As String

Dim shtMarka As Worksheet[/code]

Az Összes fül A1 cellájához tartozó összefüggő tartomány sorainak megszámolása:

[code language=”vb”]lastRowAll = shtOsszes.Range("A1").CurrentRegion.Rows.Count[/code]

Az Összes fül A1 cellájából ugrás az oszlop aljára. (Ez azt a cellát adja, ahová az A1 cellán Ctrl+lefelé nyíl kombinációra ugrik.) Ha ezt választjuk figyelni kell arra, hogy amennyiben az Összes fül üres, akkor a fül legutolsó sorát (1048576) kapjuk.

[code language=”vb”]lastRowAll = shtOsszes.Range("A1").End(xlDown).Row[/code]

Az Összes fül A oszlopának utolsó cellájából ugrás felfelé az utolsó kitöltött cellára. (Ez azt a cellát adja, ahová az A1048576 cellán Ctrl+felfelé nyíl kombinációra ugrik.)

[code language=”vb”]lastRowAll = shtOsszes.Range("A1048576").End(xlUp).Row[/code]

Ha tudjuk, hogy az adathalmazban nincs üres sor, akkor érdemes az elsőt választani.

Írjunk egy For loop-ot, ami 2-vel kezdődik (az első sor a fejléc, amit nem vizsgálunk) és a lastRow változó értékig tart. Használjuk ehhez az iRow változót.

[code language=”vb”]For iRowAll = 2 To lastRowAll

Next iRowAll[/code]

A cikluson belül tároljuk el egy változóba (strMarka), hogy az Összes fül iRow értékű sorában és első oszlopában milyen érték (márka) szerepel.

Írjunk egy elágazást (If-Elseif), amiben vizsgáljuk, hogy az strMarka értéke „Ford”, „Opel”, „Renault” vagy „Kia” értéket vett fel. Az elágazáson belül a shtMarka nevű Worksheet objektumhoz rendeljük hozzá a megfelelő fület. A shtMarka változó típusra azért van szükség, hogy ne kelljen megismételni az elágazásban ugyanazt a kódrészletet négyszer. A cikk végén megmutatom, hogyan nézne ki a kód ennek használata nélkül.

[code language=”vb”]If strMarka = "Ford" Then

Set shtMarka = shtFord

ElseIf strMarka = "Opel" Then

Set shtMarka = shtOpel

ElseIf strMarka = "Renault" Then

Set shtMarka = shtRenault

ElseIf strMarka = "Kia" Then

Set shtMarka = shtKia

End If[/code]

Miután megtaláltuk, melyik fülre szeretnénk az Összes fül aktuális sorát „másolni”, keressük meg, hogy mi ennek a fülnek az utolsó kitöltött sora (lastRowMarka). Az ezt követő sorba fogjuk illeszteni az adatokat.

[code language=”vb”]lastRowMarka = shtMarka.Range("A1").CurrentRegion.Rows.Count[/code]

A shtMarka fül utolsó utáni sorába töltsük be az Összes fül aktuális (iRowAll) sorának értékeit.

[code language=”vb”]shtMarka.Cells(lastRowMarka + 1, 1) = shtOsszes.Cells(iRowAll, 1)

shtMarka.Cells(lastRowMarka + 1, 2) = shtOsszes.Cells(iRowAll, 2)

shtMarka.Cells(lastRowMarka + 1, 3) = shtOsszes.Cells(iRowAll, 3)

shtMarka.Cells(lastRowMarka + 1, 4) = shtOsszes.Cells(iRowAll, 4)

shtMarka.Cells(lastRowMarka + 1, 5) = shtOsszes.Cells(iRowAll, 5)[/code]

Vegyük észre, hogy erre az öt sorra is lehetne egy ciklust írni (cikluson belüli ciklus), amiben az oszlop sorszámát is változóval határozzuk meg. Amennyiben több oszlopunk van különösen érdemes ezt a módszert használni a fenti helyett.

[code language=”vb”]For iCol = 1 To 5

shtMarka.Cells(lastRowMarka + 1, iCol) = shtOsszes.Cells(iRowAll, iCol)

Next[/code]

(Mivel egy egyszerű példát szeretnék ebben a cikkben bemutatni, maradjunk az első megoldásnál.)

Ezzel tulajdonképpen majdnem kész is van a kódunk. Ha lefuttatjuk, az alábbi eredmény kapjuk:

(Hasonlóan a többi márka fülén)

Nézzük meg mi történik, ha többször lefuttatjuk ezt a kódot.

Azt látjuk, hogy az adatok ismétlődnek. Hogy ez ne forduljon elő, a ciklus előtt törölnünk kell a márka fülek adatait. Ehhez találjuk meg a márka fül utolsó sorát, és töröljük a 2 sortól a megtalált utolsó sorig.

[code language=”vb”]lastRowMarka = shtFord.Range("A1").CurrentRegion.Rows.Count

shtFord.Rows("2:" & lastRowMarka).ClearContents

lastRowMarka = shtOpel.Range("A1").CurrentRegion.Rows.Count

shtOpel.Rows("2:" & lastRowMarka).ClearContents

lastRowMarka = shtRenault.Range("A1").CurrentRegion.Rows.Count

shtRenault.Rows("2:" & lastRowMarka).ClearContents

lastRowMarka = shtKia.Range("A1").CurrentRegion.Rows.Count

shtKia.Rows("2:" & lastRowMarka).ClearContents[/code]

Ennél kicsit kevésbé elegáns megoldás, ha nem keressük az utolsó sort, és csak egy biztonságosan nagy sor számmal dolgozunk.

[code language=”vb”]shtFord.Rows("2:99999").ClearContents

shtOpel.Rows("2:99999").ClearContents

shtRenault.Rows("2:99999").ClearContents

shtKia.Rows("2:99999").ClearContents[/code]

Az egyszerűség kedvéért ezt a megoldást használjuk a kódban.

Így néz ki a végleges kódunk:

[code language=”vb”]Sub Szetvalogatas()

Dim lastRowAll As Long

Dim iRowAll As Long

Dim lastRowMarka As Long

Dim strMarka As String

Dim shtMarka As Worksheet

‘márka fülek adatainak törlése

shtFord.Rows("2:99999").ClearContents

shtOpel.Rows("2:99999").ClearContents

shtRenault.Rows("2:99999").ClearContents

shtKia.Rows("2:99999").ClearContents

‘Összes fül utolsó sorának megállapítása

lastRowAll = shtOsszes.Range("A1").CurrentRegion.Rows.Count

‘Ciklus az Összes fül sorain

For iRowAll = 2 To lastRowAll

‘az Összes fül 1. (A) oszlopában lévő márka eltárolása változóba

strMarka = shtOsszes.Cells(iRowAll, 1)

‘a megfelelő fül hozzárendelése a shtMarka objektumhoz

‘az strMarka változó értéke alapján

If strMarka = "Ford" Then

Set shtMarka = shtFord

ElseIf strMarka = "Opel" Then

Set shtMarka = shtOpel

ElseIf strMarka = "Renault" Then

Set shtMarka = shtRenault

ElseIf strMarka = "Kia" Then

Set shtMarka = shtKia

End If

‘a shtMarka utolsó utáni sorának kitöltése az Összes fül aktuális sorával

lastRowMarka = shtMarka.Range("A1").CurrentRegion.Rows.Count

shtMarka.Cells(lastRowMarka + 1, 1) = shtOsszes.Cells(iRowAll, 1)

shtMarka.Cells(lastRowMarka + 1, 2) = shtOsszes.Cells(iRowAll, 2)

shtMarka.Cells(lastRowMarka + 1, 3) = shtOsszes.Cells(iRowAll, 3)

shtMarka.Cells(lastRowMarka + 1, 4) = shtOsszes.Cells(iRowAll, 4)

shtMarka.Cells(lastRowMarka + 1, 5) = shtOsszes.Cells(iRowAll, 5)

Next iRowAll

End Sub[/code]