De methode CELLS gebruiken

News around the world

Oorspronkelijk geplaatst op:

De methode Cells(Rij, Kolom) gebruiken

Een gemakkelijke manier om een cel op een werkblad aan te duiden is de Cells methode.
De syntax voor deze methode is:

Cells(Rijnummer, Kolomnummer)
of
Cells(Rijnummer, “KolomLetter”) Let op de aanhalingstekens.

Je kunt  geen negatieve nummers of 0 gebruiken.
Cells verwijst naar alle cellen op een werkblad.

Bijvoorbeeld:

Cells(1, 1) Verwijst naar cel A1

Cells(2, 1) Verwijst naar cel A2

Cells(5, “F”) Verwijst naar cel F5

Cells(X, Y) Verwijst naar de cel in rij X en kolom Y,  X en Y zijn variabelen die eerder in de code zijn ingesteld.

ActiveCell.Cells(1, 2) Verwijst naar een cel relatief ten opzichte van de actieve cel.
In dit geval, in dezelfde rij en één kolom naar rechts.

ActiveCell.Offset(2, 2) Verwijst naar een cel  1 rij naar beneden en 1 kolom naar rechts.

Dit kan verwarrend zijn want bij 2 denk je aan 2 rijen naar beneden. ActiveCell.Offset(1, 1) is dus gewoon de actieve cel.

Je kunt in de syntax ook combinaties maken zoals:

Cells(1, 1).Value = 9

Hier wordt de methode Cells gebruikt om de waarde van cel A1 op 9 in te stellen.

Cells(x, y) is een voorbeeld waarbij x en y variabelen voorstellen.

Het voorbeeld ActiveCell.Cells(1, 2) is een voorbeeld waarbij de ligging relatief is ten opzichte van een andere cel namelijk de actieve cel.

Relatieve verwijzingen kunnen bijzonder nuttig zijn.

Je kunt de methode Cells koppelen aan de naam van de werkmap of het werkblad zonder eerst die werkmap of het werkblad te selecteren

Workbooks(“Testdata.Xls”).Sheets(“New Data”).Cells(4, 1).Value = 0

stelt de waarde van cel (A4) in op 0 zonder eerst het werkblad te selecteren.

De reden waarom Cells een methode is en niet een object, is omdat het een verwijzing is naar een object.

Het Range (bereik) object

News around the world

Oorspronkelijk geplaatst op:

Range(cel verwijzing, cel verwijzing) gebruiken om naar een gebied te verwijzen

De syntax Range(cel verwijzing, cel verwijzing), stelt je in staat om naar een bepaald gebied te verwijzen. Je kunt voor je verwijzing de zogenaamde A1 syntax gebruiken maar ook de syntax Cells(RijNr, KolomNr). Ook kun je bereiknamen, die naar een cel verwijzen, gebruiken. Tenslotte kun je variabelen gebruiken

De volgende voorbeelden verwijzen naar een gebied d.m.v. de methode Range. Merk op dat de komma NIET binnen de aanhalingstekens staat.

Range(“B4”, “D9”)

Verwijst naar het bereik B4:D9

Range(Cells(4, 2), Cells(9, 4))

Verwijst eveneens naar het bereik B4:D9. Deze manier werkt alleen op het actieve blad. Het volgende voorbeeld is meer flexibel

With Sheets(“Blad4”)
.Range(.Cells(4, 2), .Cells(9, 4))
End With

Het woord With zorgt er voor dat je code korter wordt. Je hoeft namelijk Sheets(“Blad4”) niet telkens te herhalen daar waar de punt staat bij .Range en .Cells

Range(ActiveCell, ActiveCell.Offset(4, 5)).Select

Gaat uit van de actieve cel en vervolgens 4 rijen naar beneden en 5 kolommen naar rechts

Range(ActiveCell, Cells(2, 5)).Select

Gaat uit van de actieve cel en vervolgens 2 rijen naar beneden en 5 kolommen naar rechts

Range(Cells(4, 5), “H5”).Select

Links boven is cel E4 en rechts onder is cel H5. Dus het gebied E4:H5 wordt geselecteerd

Range(“A1”, “LaatsteCel”).Select

Selecteert het gebied van A1 tot en met de cel met de naam “LaatsteCel”

Range(“E2”, Cells(X, Y)).Select

Selecteert het gebied van E2 tot en met de cel met de variabelen X en Y waarbij X en Y getallen voorstellen.

Range(MijnCel, Cells(4, 4)).Select

Selecteert het gebied van MijnCel tot en met cel D4. Hierbij is MijnCel een object variabele. Dit is een verwijzing naar een cel die als volgt tot stand komt.

Dim MijnCel As Range
Set MijnCel = Range(“A1”)

Dus in dit voorbeeld wordt het gebied A1:D4 geselecteerd. Let op ! ! ! het blad MOET actief zijn.

Range(MijnCel, JouwCel).Select

Selecteert het gebied van MijnCel tot en met cel JouwCel. Hierbij zijn MijnCel en JouwCel object variabelen.

Dim MijnCel As Range, JouwCel As Range
Set MijnCel = Range(“A1”)
Set JouwCel = Range(“E10”)

Dus in dit voorbeeld wordt het gebied A1:E10 geselecteerd. Let op ! ! ! het blad MOET actief zijn.

Belangrijk: Verwijzingen moeten bijna altijd worden voorzien van een verwijzing naar het juiste blad. Hier weer een voorbeeld:

Sub test()
Dim BladVerw As Worksheet
Set BladVerw = Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”)
With BladVerw
.Range(.Cells(1, 1), .Cells(5, 5)).Copy
End With
End Sub

Nog een voorbeeld. Het zal langzamerhand voor zichzelf spreken.

Sub test()
Dim topCell, bottomcell, a, b, x, y
a = 1
b = 1
x = 5
y = 5
Set topCell = Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”).Cells(a, b)
Set bottomcell = Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”).Cells(x, y)
Range(topCell, bottomcell).Copy
end sub

Je hoeft niet altijd het juiste werkblad te activeren. Eigenlijk verdient dit de voorkeur want activeren en vervolgens selecteren zijn extra stappen en maken de uitvoering van de code langzaam.

Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”).Range(“1:2”).Copy

Deze code kopieert rij 1 en 2 naar het klembord zonder de map en het werkblad te activeren en het bereik te selecteren.

Nog een voorbeeld. Als je deze code uitvoert terwijl een ander blad actief is dan het blad “salarissen”, krijg je een foutmelding

Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”).Range(Cells(1, 1), Cells(3, 3)).Value = 4

Dit werkt wel:

With Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”)
.Range(.Cells(1, 1), .Cells(3, 3)).Value = 4
End With

De Range methode kan voorzien worden met een celverwijzing middels ActiveCell. Indien voorzien met deze celverwijzing, is de Range verwijzing relatief ten opzichte van de ActiveCell. Als E4 de ActiveCell is, gebeurt bij de uitvoering van onderstaande code het volgende:

ActiveCell.Range(“A1:C1”).Clear

Het bereik A1:C1 wordt NIET gewist, maar WEL het bereik vanaf de ActiveCell twee cellen naar rechts. Dus bereik E4:G4

Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 10)).Select

Het bereik 6 cellen naar rechts tot en met 10 cellen naar rechts ten opzichte van de ActiveCell wordt geselecteerd. Indien de ActiveCell A1 is, wordt dus het bereik G1:K1 geselecteerd.

Elke N-th cel optellen

News around the world

Oorspronkelijk geplaatst op:

Het is niet ongebruikelijk dat je elke N-th cel wil optellen. Bijvoorbeeld, de derde, zesde, negende etc. Excel heeft daarvoor geen speciale functie die dat kan. Maar met de functie REST heb je wel een hulpmiddel. Kijk maar naar het voorbeeld.

In [E4] komt de volgende matrixformule:
=SOM(ALS(REST($A$1:$A$21;$F$2)=0;$B$1:$B$21;0))
Let op: Invoeren met toetscombinatie Ctrl+Shift+Enter

Verdere opzet van het voorbeeld

A1:A21, getallen van 1 tot en met 21
B1:B21, willekeurige getallen
F2, een keuzelijst met de getallen 1 tot en met 9.

Het maken van de keuzelijst:
Zet ergens op je werkblad de getallen 1 tot en met 9. Ga naar Data | Gegevens validatie en kies voor lijst en zet bij Bron het bereik van je getallen 1 tot en met 9 neer.

Speciale tekens

News around the world

Oorspronkelijk geplaatst op:

Om speciale tekens weer te geven of in cellen te plaatsen met VBA kun je gebruik maken van de ChrW functie. Kies in Excel Insert Symbol en kies bij Lettertype: Arial. Laten we het Euro-teken kiezen. In het venster rechtsonder genaamd Tekencode staat de Hex-code namelijk 20AC



Deze code kun je gebruiken om in Cel A1 het €-teken te krijgen:
Range(“A1”) = ChrW(&H20AC)

Let op ! ! ! De Hex-code wordt vooraf gegaan door &H
Meer experimenteren? Gebruik het Venster Direct, te bereiken via de toetscombinatie:
Alt+F11
Ctrl+G

Voer het volgende in en druk op Enter:
debug.print ChrW(&H20AC)
Resultaat: €

Je kunt ook dit doen. Voer in en dan Enter:
?&H20AC
Resultaat: €

Het vraagteken ? doet hetzelfde als debug.print.

Je kunt die Hex-code veranderen in een getal. Voer in en dan Enter:
S = “&H20AC” : ?Val(S)
Resultaat: 8364

Of van getal naar Hex-code. Voer in en dan Enter:
?Hex(8364)
Resultaat: 20AC

Tekens die vaak gevraagd worden zijn die driehoekjes die aangeven of iets is gestegen of gedaald.
Voorbeeld:

ActiveCell.Value = ChrW(&H25BC) & " Gedaald"
ActiveCell.Font.Color = vbRed
ActiveCell.Offset(1, 0).Value = ChrW(&H25B2) & " Gestegen"
ActiveCell.Offset(1, 0).Font.Color = vbBlue



Tijd aftrekken en totaal berekenen

News around the world

We hebben al eerder gezien hoe je begintijden van eindtijden kunt aftrekken d.m.v. één formule. Dat kan handig zijn als je het aantal gewerkte uren moet berekenen. Als er echter tekst tussen de gegevens staat, werkt dat niet en krijg je een foutmelding. Hier een oplossing.

Formule komt in D9.

De korte manier:
[D9]=SUM(IF(ISNUMBER(C2:C8-B2:B8);MOD(C2:C8-B2:B8;1)*24;0))
Invoeren met Ctrl+Shift+Enter

De langere werkwijze is door in D2:D8 een formule te zetten en de uitkomsten op te tellen.
[D2:D8] =IF(ISNUMBER(B2:C2);MOD(C2-B2;1)*24;””)
Invoeren met Ctrl+Shift+Enter
en dan in [D9] =SUM(D2:D8)

Opmerkingen van cel in andere cel weergeven

News around the world

Oorspronkelijk geplaatst op:

Als je de gegevens in die opmerking zichtbaar wil maken, ga je er met de cursor overheen. Stel je wilt de gegevens in de opmerking gebruiken en je hebt 100 opmerkingen verspreid over je werkblad. Dan heb je veel werk.

Deze functie kan de opmerking in een cel plaatsen.
[D5] =getOpmerking(A2)

Function getOpmerking(rngCel) As String
    'Geeft de opmerkingen van een cel weer als tekenreeks
    On Error Resume Next
    'Herberekenen
    Application.Volatile True
    getOpmerking = rngCel.Comment.Text
End Function

Snel selecteren

News around the world

Oorspronkelijk geplaatst op:

Handige truc:
Je wilt in de lichtgroene cellen dezelfde waarde typen. Duurt lang om alle cellen een voor een te selecteren door de Ctrl-toets ingedrukt te houden, de waarde te tikken en te bevestigen met Ctrl+Enter.

Je kunt ook het gebied A4:M11 selecteren.

Dan F5 | Speciaal | Lege waarden (Blanks) aanvinken  | OK
Waarde intikken en bevestigen met Ctrl+Enter. Klinkt langer maar is korter.

Data van kolommen naar rijen

News around the world

Oorspronkelijk geplaatst op:

Een klant heeft meerdere produkten besteld. Het ID van de klant staat in kolom A. Merk op dat dezelfde ID meerdere keren voorkomt.  De produkten staan in kolom B. In kolom C zet je de unieke ID’s.

Formule in D2:

[D2] =ALS.FOUT(INDEX($B$2:$B$13;KLEINSTE(ALS($A$2:$A$13=$C2;RIJ(A$2:A$13)-RIJ(A$2)+1);KOLOMMEN($D$2:D2)));””)

Invoeren met Ctrl+Shift+Enter
Vervolgens doorvoeren naar rechts en naar beneden.

Prijsverloop tonen

News around the world

Oorspronkelijk geplaatst op:

[F3]=SOM(ALS(A3:A27=F2;ALS(ISGETAL(D3:D27);1)))

[D3]=ALS(ISNB(VERGELIJKEN(C3;$D$2:D2;0));C3;ALS(ISNB(VERGELIJKEN(C3;$D$2:D2;0))*ISNB(VERGELIJKEN($A3&”|”&$B3;$A$2:A2&”|”&$B$2:B2;0));C3;ALS(ISGETAL(ZOEKEN(9,99999999999999E+307;1/($A$2:A2=A3);$C$2:C2));ALS(ZOEKEN(9,99999999999999E+307;1/($A$2:A2=A3);$C$2:C2)<>C3;C3;”#”);”#”)))

[E5]=ALS(RIJEN($E$3:E3)<=$F$3;INDEX($B$3:$B$27;KLEINSTE(ALS($A$3:A27=$F$2;ALS(ISGETAL($D$3:$D$27);RIJ($B$3:$B$27)-RIJ($B$3)+1));RIJEN($E$3:E3)));””)

[F5]=ALS($E5=””;””;INDEX($D$3:$D$27;KLEINSTE(ALS($A$3:$A$27=$F$2;ALS($B$3:$B$27=$E5;RIJ($D$3:$D$27)-RIJ($D$3)+1));AANTALLEN.ALS($E$5:E5;E5))))

Let op: Alle formules invoeren met Ctrl+Shift+Enter.

[D3], [E5], [F5] doorvoeren naar beneden.