Lucrul cu celule și intervale în Excel VBA (Select, Copy, Move, Edit)

când lucrați cu Excel, cea mai mare parte a timpului dvs. este petrecut în zona foii de lucru – care se ocupă de celule și intervale.și dacă doriți să vă automatizați munca în Excel folosind VBA, trebuie să știți cum să lucrați cu celule și intervale folosind VBA.

există o mulțime de lucruri diferite pe care le puteți face cu intervale în VBA (cum ar fi selectați, copiați, mutați, editați etc.).deci ,pentru a acoperi acest subiect, voi rupe acest tutorial în secțiuni și vă voi arăta cum să lucrați cu celule și intervale în Excel VBA folosind exemple.,

Să începem.toate codurile pe care le menționez în acest tutorial trebuie să fie plasate în editorul VB. Accesați secțiunea „Unde să puneți codul VBA” pentru a ști cum funcționează.

dacă sunteți interesat în procesul de învățare VBA mod simplu, a verifica afară meu on-line de formare Excel VBA.

selectarea unei celule/interval în Excel folosind VBA

pentru a lucra cu celule și intervale în Excel folosind VBA, nu trebuie să o selectați.în majoritatea cazurilor, este mai bine să nu selectați celule sau intervale (așa cum vom vedea).,în ciuda acestui fapt ,este important să parcurgeți această secțiune și să înțelegeți cum funcționează. Acest lucru va fi crucial în învățarea VBA și o mulțime de concepte acoperite aici vor fi utilizate pe parcursul acestui tutorial.deci, să începem cu un exemplu foarte simplu.

Selectarea unei Singure Celule Folosind VBA

Dacă doriți să selectați o singură celulă din foaia activă (să zicem A1), apoi puteți folosi codul de mai jos:

Sub SelectCell()Range("A1").SelectEnd Sub

codul De mai sus are obligatoriu Sub ” și „End Sub’ parte, și o linie de cod care selectează celula A1.,

Range(„A1”) spune VBA adresa celulei la care vrem să ne referim.

Select este o metodă a obiectului Range și selectează celulele / intervalul specificat în obiectul Range. Referințele de celule trebuie să fie închise în ghilimele duble.

acest cod ar afișa o eroare în cazul în care o foaie de diagramă este o foaie activă. O foaie de diagramă conține diagrame și nu este utilizat pe scară largă. Deoarece nu are celule / intervale în el, codul de mai sus nu îl poate selecta și ar ajunge să arate o eroare.rețineți că, deoarece doriți să selectați celula din foaia activă, trebuie doar să specificați adresa celulei.,dar dacă doriți să selectați celula într-o altă foaie (să zicem Sheet2), trebuie să activați mai întâi Sheet2 și apoi să selectați celula din ea.

Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

în mod similar, puteți activa și un registru de lucru, apoi activați o foaie de lucru specifică în ea, apoi selectați o celulă.

Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

rețineți că atunci când vă referiți la registrele de lucru, trebuie să utilizați numele complet împreună cu extensia de fișier (.xlsx în codul de mai sus). În cazul în care registrul de lucru nu a fost salvat niciodată, nu este necesar să utilizați extensia de fișier.,acum, aceste exemple nu sunt foarte utile, dar veți vedea mai târziu în acest tutorial cum putem folosi aceleași concepte pentru a copia și lipi celule în Excel (folosind VBA).la fel cum selectăm o celulă, putem selecta și un interval.

în cazul unui interval, ar putea fi un interval de dimensiuni fixe sau un interval de dimensiuni variabile.

într-un interval de dimensiuni fixe, ați ști cât de mare este intervalul și puteți utiliza dimensiunea exactă în codul VBA. Dar, cu o gamă de dimensiuni variabile, nu aveți idee cât de mare este gama și trebuie să utilizați un pic de magie VBA.

Să vedem cum se face acest lucru.,

selectarea unui interval de dimensiuni fixe

Iată codul care va selecta intervalul A1: D20.

Sub SelectRange()Range("A1:D20").SelectEnd Sub

un Alt mod de a face acest lucru este folosind codul de mai jos:

Sub SelectRange()Range("A1", "D20").SelectEnd Sub

codul De mai sus are în partea de sus-stânga celulei adresa (A1) și partea de jos-dreapta celulei adresa (D20) și se selectează întreaga gamă. Această tehnică devine utilă atunci când lucrați cu intervale de dimensiuni variabile (așa cum vom vedea când proprietatea finală este acoperită mai târziu în acest tutorial).,dacă doriți ca selecția să aibă loc într-un registru de lucru diferit sau într-o foaie de lucru diferită, atunci trebuie să spuneți VBA numele exacte ale acestor obiecte.

de exemplu, codul de mai jos ar selecta intervalul A1:D20 în foaia de lucru Sheet2 din registrul de lucru Book2.

Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub

acum, ce se întâmplă dacă nu știți câte rânduri există. Ce se întâmplă dacă doriți să selectați toate celulele care au o valoare în ea.în aceste cazuri, trebuie să utilizați metodele prezentate în secțiunea următoare (la selectarea intervalului de dimensiuni variabile).,

selectarea unui interval de dimensiuni variabile

există diferite moduri în care puteți selecta o gamă de celule. Metoda pe care o alegeți depinde de modul în care sunt structurate datele.în această secțiune, voi acoperi câteva tehnici utile care sunt cu adevărat utile atunci când lucrați cu intervale în VBA.

selectați folosind proprietatea CurrentRange

în cazurile în care nu știți câte rânduri/coloane au datele, puteți utiliza proprietatea CurrentRange a obiectului Range.

proprietatea CurrentRange acoperă toate celulele umplute contigue într-un interval de date.,

mai jos este codul care va selecta regiunea curentă care conține celula A1.

Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub

metoda de mai sus este bună atunci când aveți toate datele ca tabel fără rânduri/coloane goale în ea.

Dar în cazul în care aveți rânduri/coloane de date, se va selecta cele de după gol rânduri/coloane. În imaginea de mai jos, Codul CurrentRegion Selectează date până la rândul 10, deoarece rândul 11 este gol.

În astfel de cazuri, poate doriți să utilizați UsedRange proprietatea Obiect Foaie de lucru.,

selectați utilizând proprietatea UsedRange

UsedRange vă permite să vă referiți la orice celule care au fost modificate.deci, codul de mai jos ar selecta toate celulele utilizate în foaia activă.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

Rețineți că în cazul în care aveți o de departe-off celulă care a fost folosit, acesta va fi considerat de către codul de mai sus și toate celulele până folosit mobil va fi selectat.

selectați folosind proprietatea End

acum, această parte este foarte utilă.

proprietatea End vă permite să selectați ultima celulă umplută., Acest lucru vă permite să imite efectul de control în jos/sus tasta săgeată sau tastele de control Dreapta/Stânga.

să încercăm să înțelegem acest lucru folosind un exemplu.să presupunem că aveți un set de date așa cum se arată mai jos și doriți să selectați rapid ultimele celule umplute din coloana A.

problema aici este că datele se pot schimba și nu știți câte celule sunt umplute. Dacă trebuie să faceți acest lucru folosind tastatura, puteți selecta celula A1, apoi utilizați tasta Control + Săgeată în jos și va selecta ultima celulă umplută din coloană.acum, să vedem cum se face acest lucru folosind VBA., Aceasta tehnica vine la îndemână atunci când doriți să sari rapid la ultima celulă umplut într-o variabil de dimensiuni coloana

Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub

codul De mai sus va sari la ultimul umplut celula din coloana A.

în mod Similar, puteți utiliza End(xlToRight) pentru a sări la ultima celulă umplut într-un rând.

Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub

acum, ce se întâmplă dacă doriți să selectați întreaga coloană în loc să săriți la ultima celulă umplută.,puteți face acest lucru folosind codul de mai jos:

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub

în codul de mai sus, am folosit prima și ultima referință a celulei pe care trebuie să o selectăm. Indiferent cât de multe celule umplute sunt acolo, codul de mai sus va selecta toate.

amintiți-vă exemplul de mai sus unde am selectat intervalul A1:D20 folosind următoarea linie de cod:

interval(„A1″,”D20”)

aici A1 a fost celula din stânga sus și D20 a fost celula din dreapta jos din interval. Putem folosi aceeași logică în selectarea intervalelor de dimensiuni variabile., Dar din moment ce nu știm adresa exactă a celulei din dreapta jos, am folosit proprietatea finală pentru ao obține.

în interval („A1”, interval („A1”).End(xlDown)), „A1” se referă la prima celulă și intervalul(„A1”).End (xlDown) se referă la ultima celulă. Deoarece am furnizat ambele referințe, metoda Select selectează toate celulele dintre aceste două referințe.în mod similar, puteți selecta, de asemenea, un întreg set de date care are mai multe rânduri și coloane.

codul de mai jos ar selecta toate rândurile/coloanele completate începând de la celula A1.,

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub

în codul de mai sus, am folosit gama(„A1”).Sfârșit (xlDown).End (xlToRight) pentru a obține referința celulei umplute din dreapta jos a setului de date.

Diferența între Utilizarea CurrentRegion și de Sfârșit

Daca te intrebi de ce folosesc Sfârșitul proprietate pentru a selecta gama de umplut atunci când avem proprietatea CurrentRegion, permiteți-mi să vă spun diferența.

cu proprietatea End, puteți specifica celula start., De exemplu, dacă aveți datele dvs. în A1:D20, dar primul rând sunt anteturi, puteți utiliza proprietatea End pentru a selecta datele fără anteturi (folosind codul de mai jos).

Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub

Dar CurrentRegion va selecta automat întregul set de date, inclusiv antetele.până în prezent, în acest tutorial, am văzut cum să ne referim la o serie de celule folosind moduri diferite.

acum să vedem câteva modalități prin care putem folosi aceste tehnici pentru a face ceva.,după cum am menționat la începutul acestui tutorial, selectarea unei celule nu este necesară pentru a efectua acțiuni asupra acesteia. Veți vedea în această secțiune cum să copiați celulele și intervalele fără să le selectați.

să începem cu un exemplu simplu.

copierea unei singure celule

Dacă doriți să copiați celula A1 și să o lipiți în celula D1, codul de mai jos ar face-o.

Sub CopyCell()Range("A1").Copy Range("D1")End Sub

rețineți că metoda de copiere a obiectului interval copiază celula (la fel ca Control +C) și o lipește în destinația specificată.,

în codul de exemplu de mai sus, destinația este specificată în aceeași linie în care utilizați metoda de copiere. Dacă doriți să faceți codul dvs. și mai lizibil, puteți utiliza codul de mai jos:

Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub

codurile de mai sus vor copia și lipi valoarea, precum și formatarea/formulele din ea.după cum ați observat deja, codul de mai sus copiază celula fără a o selecta. Indiferent unde vă aflați în foaia de lucru, codul va copia celula A1 și o va lipi pe D1.de asemenea, rețineți că codul de mai sus ar suprascrie orice cod existent în celula D2., Dacă doriți ca Excel să vă anunțe dacă există deja ceva în celula D1 fără a o suprascrie, puteți utiliza codul de mai jos.dacă doriți să copiați A1: D20 în J1: M20, puteți utiliza codul de mai jos:

Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub

în celula destinație, trebuie doar să specificați adresa celulei din stânga sus. Codul va copia automat intervalul copiat exact în destinație.

puteți utiliza același construct pentru a copia date dintr-o foaie în alta.

codul de mai jos ar copia A1:D20 din foaia activă în Sheet2.,

Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

cele de mai sus copiază datele din foaia activă. Deci, asigurați-vă că foaia care are datele este foaia activă înainte de a rula codul. Pentru a fi în siguranță, puteți specifica și numele foii de lucru în timp ce copiați datele.

Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

lucrul bun despre codul de mai sus este că, indiferent de foaia activă, va copia întotdeauna datele din Sheet1 și le va lipi în Sheet2.

de asemenea, puteți copia un interval numit utilizând numele acestuia în locul referinței.,

de exemplu, dacă aveți un interval numit numit „SalesData”, puteți utiliza codul de mai jos pentru a copia aceste date în Sheet2.

Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub

Dacă domeniul de aplicare al intervalului numit este întregul registru de lucru, nu trebuie să fiți pe foaia care are intervalul numit pentru a rula acest cod. Deoarece intervalul numit este vizat pentru registrul de lucru, îl puteți accesa din orice foaie folosind acest cod.

Dacă aveți un tabel cu numele Table1, puteți utiliza codul de mai jos pentru a-l copia în Sheet2.

Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub

de asemenea, puteți copia un interval într-un alt registru de lucru.,

în exemplul următor, copiez tabelul Excel (Table1), în registrul de lucru Book2.

Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub

acest cod ar funcționa numai dacă registrul de lucru este deja deschis.

copierea unui interval de dimensiuni variabile

o modalitate de a copia intervale de dimensiuni variabile este de a le converti în intervale numite sau tabel Excel și de a utiliza codurile așa cum se arată în secțiunea anterioară.

dar dacă nu puteți face acest lucru, puteți utiliza CurrentRegion sau proprietatea End a obiectului range.

codul de mai jos va copia regiunea curentă în foaia activă și o va lipi în Sheet2.,

Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub

Dacă doriți să copiați prima coloană din setul de date până la ultima celulă umplut și inserați codul în Sheet2, puteți folosi codul de mai jos:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub

Dacă doriți să copiați rânduri și coloane, puteți folosi codul de mai jos:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub

Rețineți că toate aceste coduri nu selectați celulele în timp ce obținerea de executat. În general, veți găsi doar o mână de cazuri în care trebuie să selectați o celulă/interval înainte de a lucra la ea.,până în prezent, am folosit Adresa completă a celulelor (cum ar fi registrele de lucru(„Book2.xlsx”).Fișe De Lucru („Sheet1”).Interval („A1”)).

pentru a face Codul mai ușor de gestionat, puteți atribui aceste intervale variabilelor obiect și apoi utilizați aceste variabile.

de exemplu, în codul de mai jos, am atribuit intervalul sursă și destinație variabilelor obiect și apoi am folosit aceste variabile pentru a copia date dintr-un interval în altul.

începem prin declararea variabilelor ca obiecte de interval., Apoi atribuim intervalul acestor variabile folosind instrucțiunea Set. Odată ce intervalul a fost atribuit variabilei, puteți utiliza pur și simplu variabila.

introduceți date în următoarea celulă goală (folosind caseta de intrare)

puteți utiliza casetele de intrare pentru a permite utilizatorului să introducă datele.

de exemplu, să presupunem că aveți setul de date de mai jos și doriți să introduceți înregistrarea vânzărilor, puteți utiliza caseta de intrare în VBA. Folosind un cod, ne putem asigura că acesta umple datele din următorul rând gol.,codul de mai sus utilizează caseta de intrare VBA pentru a obține intrările de la utilizator, apoi introduce intrările în celulele specificate.

rețineți că nu am folosit referințe de celule exacte. În schimb, am folosit proprietatea End and Offset pentru a găsi ultima celulă goală și a completa datele din ea.acest cod este departe de a fi utilizabil. De exemplu, dacă introduceți un șir de text atunci când caseta de intrare solicită cantitate sau cantitate, veți observa că Excel o permite. Puteți utiliza o condiție If pentru a verifica dacă valoarea este numerică sau nu și apoi permiteți-o în consecință.,

Looping prin celule / intervale

până acum am văzut cum să selectăm, să copiem și să introducem datele în celule și intervale.

în această secțiune, vom vedea cum să facem buclă printr-un set de celule/rânduri/coloane într-un interval. Acest lucru ar putea fi util atunci când doriți să analizați fiecare celulă și să efectuați o acțiune bazată pe aceasta.de exemplu, dacă doriți să evidențiați fiecare al treilea rând din selecție, atunci trebuie să faceți buclă și să verificați numărul rândului., În mod similar, dacă doriți să evidențiați toate celulele negative schimbând culoarea fontului în roșu, trebuie să faceți buclă și să analizați valoarea fiecărei celule.

Iată codul care va trece prin rândurile din celulele selectate și va evidenția rânduri alternative.

Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub

codul de mai sus utilizează funcția MOD pentru a verifica numărul rândului din selecție. Dacă numărul rândului este egal, acesta este evidențiat în culoarea cyan.

Iată un alt exemplu în care codul trece prin fiecare celulă și evidențiază celulele care au o valoare negativă în ea.,

Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub

rețineți că puteți face același lucru folosind formatarea condiționată (care este dinamică și o modalitate mai bună de a face acest lucru). Acest exemplu este doar în scopul de a vă arăta cum funcționează looping cu celule și intervale în VBA.

unde să puneți codul VBA

vă întrebați unde merge codul VBA în registrul de lucru Excel?

Excel are un backend VBA numit editorul VBA. Trebuie să copiați și să lipiți codul în fereastra codului modulului editorului VB.

iată pașii pentru a face acest lucru:

1. accesați fila Dezvoltator.,
2. Faceți clic pe opțiunea Visual Basic. Aceasta va deschide editorul VB în backend.
3. în panoul Project Explorer din Editorul VB, faceți clic dreapta pe orice obiect pentru registrul de lucru în care doriți să introduceți codul. Dacă nu vedeți Project Explorer, accesați fila Vizualizare și faceți clic pe Project Explorer.
4. mergeți la Insert și faceți clic pe modul. Aceasta va insera un obiect modul pentru registrul de lucru.
5. copiați și inserați codul în fereastra modulului.,

ați putea dori, de asemenea, următoarele tutoriale Excel:

• lucrul cu foi de lucru folosind VBA.
• lucrul cu registrele de lucru folosind VBA.
• crearea funcțiilor definite de utilizator în Excel.
• pentru următoarea buclă în Excel VBA – un ghid pentru începători cu exemple.
• cum se utilizează funcția Excel VBA InStr (cu exemple practice).mai multe detalii
• cum se înregistrează o macrocomandă în Excel.
• cum se execută o macrocomandă în Excel.
• cum se creează un Add-in în Excel.,
• Excel Personal Macro Workbook | Save& utilizați macro-uri în toate registrele de lucru.
• Excel VBA evenimente-Un ghid ușor (și complet).
• Excel VBA eroare de manipulare.
• cum să sortați datele în Excel utilizând VBA (un ghid pas cu pas).
• 24 exemple utile Excel Macro pentru incepatori VBA (gata de utilizare).

---