Excel VBA -intervall och -celler

Områden och celler i VBA

Excel -kalkylark lagrar data i celler. Celler är ordnade i rader och kolumner. Varje cell kan identifieras med skärningspunkten för dess rad och kolumn (Ex. B3 eller R3C2).

Ett Excel -område avser en eller flera celler (ex. A3: B4)

Celladress

A1 Notation

I A1 -notering refereras till en cell med dess kolumnbokstav (från A till XFD) följt av dess radnummer (från 1 till 1 048 576).

I VBA kan du hänvisa till vilken cell som helst med hjälp av Områdeobjekt.

123456789

'Se cell B4 på det för närvarande aktiva arketMsgBox -intervall ("B4")'Se cell B4 på arket med namnet' Data 'MsgBox -kalkylblad ("Data"). Område ("B4")'Se cell B4 på arket med namnet' Data 'i en annan ÖPPEN arbetsbok'heter' Mina data 'MsgBox Workbooks ("Mina data"). Arbetsblad ("Data"). Område ("B4")

R1C1 Notation

I R1C1 -notering refereras en cell av R följt av radnummer sedan bokstaven 'C' följt av kolumnnummer. t.ex. B4 i R1C1 -notering kommer att refereras med R4C2. I VBA använder du Celler objekt att använda R1C1 -notation:

12	'Se cell R [6] C [4] dvs D6Celler (6, 4) = "D6"

Utbud av celler

A1 Notation

För att referera till mer än en cell använder du ett ":" mellan startcelladressen och sista celladressen. Följande kommer att referera till alla celler från A1 till D10:

1	Område ("A1: D10")

R1C1 Notation

För att referera till mer än en cell använder du ett ”,” mellan startcelladressen och sista celladressen. Följande kommer att referera till alla celler från A1 till D10:

1	Område (celler (1, 1), celler (10, 4))

Skriver till celler

För att skriva värden till en cell eller en sammanhängande grupp av celler, hänvisar du enkelt till intervallet, sätter ett = -tecken och skriver sedan värdet som ska lagras:

12345678910

'Lagra F5 i cellen med adress F6Område ("F6") = "F6"'Förvara E6 i cell med adress R [6] C [5] dvs E6Celler (6, 5) = "E6"'Förvara A1: D10 i intervallet A1: D10Område ("A1: D10") = "A1: D10"'ellerOmråde (celler (1, 1), celler (10, 4)) = "A1: D10"

Läser från celler

För att läsa värden från celler, hänvisa enkelt till variabeln för att lagra värdena, sätt ett = -tecken och hänvisa sedan till intervallet som ska läsas:

1234567891011

Dim val1Dim val2'Läs från cell F6val1 = Område ("F6")'Läs från cell E6val2 = Celler (6, 5)MsgBox val1Msgbox val2

Obs! För att lagra värden från ett cellområde måste du använda en matris istället för en enkel variabel.

Ej sammanhängande celler

För att hänvisa till icke -sammanhängande celler använder du ett komma mellan celladresserna:

123456

'Förvara 10 i cellerna A1, A3 och A5Område ("A1, A3, A5") = 10'Förvara 10 i cellerna A1: A3 och D1: D3)Område ("A1: A3, D1: D3") = 10

Korsning av celler

För att referera till icke -sammanhängande celler, använd ett mellanslag mellan celladresserna:

123	'Förvara' Col D 'i D1: D10'vilket är vanligt mellan A1: D10 och D1: F10Område ("A1: D10 D1: G10") = "Col D"

Offset från en cell eller ett område

Med funktionen Offset kan du flytta referensen från ett givet intervall (cell eller grupp av celler) med det angivna antalet_rutor och antalet_kolumner.

Offset Syntax

Range.Offset (number_of_rows, number_of_columns)

Offset från en cell

12345678910111213141516

'OFFSET från en cell A1'Se till själva cellen'Flytta 0 rader och 0 kolumnerOmråde ("A1"). Offset (0, 0) = "A1"'Flytta 1 rad och 0 kolumnerOmråde ("A1"). Offset (1, 0) = "A2"'Flytta 0 rader och 1 kolumnerOmråde ("A1"). Offset (0, 1) = "B1"'Flytta 1 rad och 1 kolumnOmråde ("A1"). Förskjutning (1, 1) = "B2"'Flytta 10 rader och 5 kolumnerOmråde ("A1"). Offset (10, 5) = "F11"

Offset från ett intervall

123	'Flytta referens till område A1: D4 med 4 rader och 4 kolumner'Ny referens är E5: H8Område ("A1: D4"). Offset (4,4) = "E5: H8"

Ange referens till ett intervall

För att tilldela ett intervall till en intervallvariabel: deklarera en variabel av typen Range och använd sedan kommandot Set för att ställa in det till ett intervall. Observera att du måste använda kommandot SET eftersom RANGE är ett objekt:

12345678

'Deklarera en intervallvariabelDim myRange som intervall'Ställ in variabeln till intervallet A1: D4Ställ in myRange = Range ("A1: D4")'Skriver ut $ A $ 1: $ D $ 4MsgBox myRange.Adress

Ändra storlek på ett intervall

Ändra storlek på metod för områdeobjekt ändrar dimensionen för referensintervallet:

1234567

Dim myRange As Range'Omfång för att ändra storlekStäll in myRange = Range ("A1: F4")'Skriver ut $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5). Adress

Cellen längst upp till vänster i storleksintervallet är densamma som den övre vänstra cellen i det ursprungliga intervallet

Ändra storlek på syntax

Range.Resize (antal_rutor, antal_kolumner)

OFFSET vs Resize

Offset ändrar inte måtten på intervallet utan flyttar det med det angivna antalet rader och kolumner. Ändra storlek ändrar inte positionen för det ursprungliga intervallet utan ändrar dimensionerna till det angivna antalet rader och kolumner.

Alla celler i blad

Cells -objektet refererar till alla celler i arket (1048576 rader och 16384 kolumner).

12	'Rensa alla celler i kalkylbladCells.Clear

UsedRange

Egenskapen UsedRange ger dig det rektangulära intervallet från den cell som används högst upp till vänster till den använda cellens högra och nedre cell.

1234567

Dim ws Som arbetsbladAnge ws = ActiveSheet'$ B $ 2: $ L $ 14 om L2 är den första cellen med något värde'och L14 är den sista cellen med något värde på'aktivt bladDebug.Print ws.UsedRange.Address

CurrentRegion

CurrentRegion-egenskapen ger dig det sammanhängande rektangulära intervallet från den övre vänstra cellen till den högra och nedre använda cellen som innehåller den refererade cellen/intervallet.

1234567891011

Dim myRange As RangeStäll in myRange = Range ("D4: F6")'Skriver ut $ B $ 2: $ L $ 14'Om det finns en fylld väg från D4: F16 till B2 OCH L14Debug.Print myRange.CurrentRegion.Address'Du kan också hänvisa till en enda startcellSet myRange = Range ("D4") 'Skriver ut $ B $ 2: $ L $ 14

Områdeegenskaper

Du kan få adress, rad/kolumnnummer i en cell och antal rader/kolumner i ett intervall enligt nedan:

123456789101112131415161718192021

Dim myRange As RangeStäll in myRange = Range ("A1: F10")'Skriver ut $ A $ 1: $ F $ 10Debug.Print myRange.AddressStäll in myRange = Range ("F10")'Skriver ut 10 för rad 10Debug.Print myRange.Row'Trycker 6 för kolumn FDebug.Print myRange.ColumnStäll in myRange = Range ("E1: F5")'Skriver ut 5 för antal rader inom intervalletDebug.Print myRange.Rows.Count'Skriver ut 2 för antalet kolumner i intervalletDebug.Print myRange.Columns.Count

Sista cellen i bladet

Du kan använda Rader och Kolumner. Räkna fastigheter med Celler objekt för att få den sista cellen på arket:

1234567891011

'Skriv ut det sista radnumret'Skriver ut 1048576Debug.Print "Rader i arket:" & Rows.Count'Skriv ut det sista kolumnnumret'Trycker 16384Debug.Print "Kolumner i arket:" & Columns.Count'Skriv ut adressen till den sista cellen'Skriver ut $ XFD $ 1048576Debug.Print "Adress för sista cellen i arket:" & Celler (Rows.Count, Columns.Count)

Senast använda radnummer i en kolumn

END -egenskapen tar dig den sista cellen i intervallet, och End (xlUp) tar dig till den första använda cellen från den cellen.

123	Dim lastRow As LonglastRow = Cells (Rows.Count, "A"). End (xlUp) .Row

Senast använda kolumnnummer i rad

123	Dim lastCol As LonglastCol = Cells (1, Columns.Count) .End (xlToLeft) .Column

END -egenskapen tar dig den sista cellen i intervallet, och End (xlToLeft) tar dig vänster till den första använda cellen från den cellen.

Du kan också använda egenskaperna xlDown och xlToRight för att navigera till de första nedre eller högra använda cellerna i den aktuella cellen.

Cellegenskaper

Vanliga egenskaper

Här är kod för att visa vanliga cellegenskaper

12345678910111213141516171819202122

Dim cell As RangeAnge cell = intervall ("A1")cell.AktiveraDebug.Print cell.Address'Skriv ut $ A $ 1Debug.Print cell.Value'Trycker 456' AdressDebug.Print cell.Formula'Utskrifter = SUMMA (C2: C3)' KommentarDebug.Print cell.Comment.Text'StilDebug.Print cell.Style'CellformatDebug.Print cell.DisplayFormat.NumberFormat

Cell typsnitt

Cell.Font -objektet innehåller egenskaper för cellteckensnittet:

1234567891011121314151617181920

Dim cell As RangeAnge cell = intervall ("A1")'Vanlig, kursiv, fet och fet kursivcell.Font.FontStyle = "Fet kursiv"' Samma somcell.Font.Bold = Santcell.Font.Italic = Sant'Ställ teckensnittet till Couriercell.Font.FontStyle = "Kurir"'Ställ in teckensnittsfärgcell.Font.Color = vbBlue'ellercell.Font.Color = RGB (255, 0, 0)'Ställ in teckenstorlekcell.Font.Size = 20

Kopiera och klistra

Klistra in allt

Områden/celler kan kopieras och klistras in från en plats till en annan. Följande kod kopierar alla egenskaper för källintervallet till destinationsområdet (motsvarande CTRL-C och CTRL-V)

1234567

'Enkel kopiaOmråde ("A1: D20"). KopieraArbetsblad ("Ark2"). Omfång ("B10"). Klistra in'eller'Kopiera från nuvarande blad till blad med namnet' Sheet2 'Område ("A1: D20"). Kopieringsdestination: = Arbetsblad ("Ark2"). Område ("B10")

Klistra in special

Valda egenskaper för källområdet kan kopieras till destinationen med PASTESPECIAL -alternativet:

123	'Klistra in intervallet endast som värdenOmråde ("A1: D20"). KopieraArbetsblad ("Sheet2"). Område ("B10"). PasteSpecial Paste: = xlPasteValues

Här är de möjliga alternativen för alternativet Klistra in:

12345678910111213

'Klistra in speciella typerxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats

AutoFit -innehåll

Storleken på rader och kolumner kan ändras för att passa innehållet med hjälp av koden nedan:

12345

'Ändra storlek på raderna 1 till 5 för att passa innehålletRader ("1: 5"). AutoFit'Ändra storlek på kolumnerna A till B för att passa innehålletKolumner ("A: B"). AutoFit

Fler intervallexempel

Det rekommenderas att du använder Macro Recorder medan du utför den nödvändiga åtgärden via GUI. Det hjälper dig att förstå de olika tillgängliga alternativen och hur du använder dem.

För varje

Det är lättare att gå igenom ett intervall med För varje konstruera som visas nedan:

123	För varje cell i intervallet ("A1: B100")'Gör något med cellenNästa cell

Vid varje iteration av slingan tilldelas en cell i intervallet variabeln c och satser i For -slingan körs för den cellen. Loop avslutas när alla celler bearbetas.

Sortera

Sort är en metod för Range -objekt. Du kan sortera ett intervall genom att ange alternativ för sortering till Range.Sort. Koden nedan sorterar kolumnerna A: C baserat på nyckeln i cell C2. Sorteringsordning kan vara xlAscending eller xlDescending. Rubrik: = xlJa ska användas om första raden är rubrikraden.

12	Kolumner ("A: C"). Sorteringsnyckel1: = Område ("C2"), _order1: = xlAscending, Header: = xlJa

Hitta

Find är också en metod för Range Object. Den hittar den första cellen med innehåll som matchar sökkriterierna och returnerar cellen som ett intervallobjekt. Det återkommer Ingenting om det inte finns någon match.

Använda sig av Hitta nästa metod (eller FindPrevious) för att hitta nästa (föregående) förekomst.

Följande kod kommer att ändra teckensnittet till "Arial Black" för alla celler i intervallet som börjar med "John":

12345

För varje c i intervall ("A1: A100")Om c gillar "John*" Dåc.Font.Name = "Arial Black"Avsluta omNästa c

Följande kod kommer att ersätta alla förekomster av "Att testa" till "Godkänd" i det angivna intervallet:

12345678910

Med intervall ("a1: a500")Ställ in c = .Find ("To Test", LookIn: = xlValues)Om inte c är ingenting dåfirstaddress = c.AdressDoc.Value = "Godkänt"Ange c = .FindNext (c)Loop While Not c är ingenting och c.Adress firstaddressAvsluta omSluta med

Det är viktigt att notera att du måste ange ett intervall för att använda FindNext. Du måste också tillhandahålla ett stoppförhållande, annars kommer slingan att köra för alltid. Normalt lagras adressen till den första cellen som hittas i en variabel och slingan stoppas när du når den cellen igen. Du måste också kontrollera om det inte finns något som stoppar slingan.

Område Adress

Använd Range.Address för att få adressen i A1 Style

123	MsgBox -intervall ("A1: D10"). Adress'ellerDebug.Print Range ("A1: D10"). Adress

Använd xlReferenceStyle (standard är xlA1) för att få tillägg i R1C1 -stil

123	MsgBox Range ("A1: D10"). Adress (ReferenceStyle: = xlR1C1)'ellerDebug.Print Range ("A1: D10"). Adress (ReferenceStyle: = xlR1C1)

Detta är användbart när du hanterar intervall lagrade i variabler och bara vill bearbeta för vissa adresser.

Räckvidd till Array

Det är snabbare och enklare att överföra ett intervall till en array och sedan bearbeta värdena. Du bör deklarera arrayen som variant för att undvika att beräkna storleken som krävs för att fylla ut intervallet i arrayen. Arrayens mått är inställda för att matcha antalet värden i intervallet.

123456789

Dim DirArray som variant'Spara värdena i intervallet till matrisenDirArray = Range ("a1: a5"). Värde'Loop för att bearbeta värdenaFör varje c i DirArrayDebug.Print cNästa

Array to Range

Efter bearbetning kan du skriva Array tillbaka till ett område. För att skriva matrisen i exemplet ovan till ett område måste du ange ett område vars storlek matchar antalet element i matrisen.

Använd koden nedan för att skriva matrisen till intervallet D1: D5:

123	Område ("D1: D5"). Värde = DirArrayOmråde ("D1: H1"). Värde = Application.Transpose (DirArray)

Observera att du måste transponera matrisen om du skriver den till en rad.

Summa intervall

12	SumOfRange = Application.WorksheetFunction.Sum (intervall ("A1: A10"))Debug.Print SumOfRange

Du kan använda många tillgängliga funktioner i Excel i din VBA -kod genom att ange Application.WorkSheetFunction. före funktionsnamnet som i exemplet ovan.

Räkna intervall

1234567

'Räkna antalet celler med siffror i intervalletCountOfCells = Application.WorksheetFunction.Count (intervall ("A1: A10"))Debug.Print CountOfCells'Räkna antalet icke tomma celler i intervalletCountOfNonBlankCells = Application.WorksheetFunction.CountA (intervall ("A1: A10"))Debug.Print CountOfNonBlankCells

Skriven av: Vinamra Chandra