Tombanana ny fotoana famakiana: 3 minuti
Ity dingana manaraka ity dia mamaky ny soatoavina avy amin'ny sela ao amin'ny tsanganana A amin'ny takelaka miasa, mandra-pahatongany amin'ny sela banga. Ny soatoavina dia voatahiry ao anaty lasy. Ity ohatra macro Excel tsotra ity dia mampiseho ny fampiasana ny:
Do Until
;Ubound
builtin (izay mamerina ny mari-pamantarana ambony indrindra amin'ny array).' Sub procedure store values in Column A of the active Worksheet
' into an array
Sub GetCellValues()
Dim iRow As Integer ' stores the current row number
Dim dCellValues() As Double ' array to store the cell values
iRow = 1
ReDim dCellValues(1 To 10)
' Do Until loop to extract the value of each cell in column A
' of the active Worksheet, as long as the cell is not blank
Do Until IsEmpty(Cells(iRow, 1))
' Check that the dCellValues array is big enough
' If not, use ReDim to increase the size of the array by 10
If UBound(dCellValues) < iRow Then
ReDim Preserve dCellValues(1 To iRow + 9)
End If
' Store the current cell in the CellValues array
dCellValues(iRow) = Cells(iRow, 1).Value
iRow = iRow + 1
Loop
End Sub
Ny fomba fiasa dia mitahiry ny soatoavina ao amin'ny tsanganana A amin'ny worksheet mavitrika amin'ny array, mariho fa:
Do Until
maka ny sandan'ny sela tsirairay ao amin'ny tsanganana A amin'ny takelaka miasa, tsy miraharaha ny sela bangaIf UBound(dCellValues) < iRow
” Manamarina fa ny dCellValues array dia ampy hitazonana ny fampahalalana, raha tsy izany dia ampiasao ny ReDim hampitombo ny haben'ny array amin'ny 10dCellValues(iRow) = Cells(iRow, 1).Value
” Mitahiry ny sela ankehitriny ao amin'ny laharan'ny CellValuesLa seguente procedura Sub legge i valori dalla colonna A del foglio di lavoro denominato “Foglio2” ed esegue operazioni aritmetiche sui valori. I valori risultanti vengono stampati nella colonna A del foglio di lavoro attivo corrente.
Questa macro illustra:
Do Until
;' Sub procedure to loop through the values in Column A of the Worksheet
' "Sheet2", perform arithmetic operations on each value, and write the
' result into Column A of the current Active Worksheet ("Sheet1")
Sub Transfer_ColA()
Dim i As Integer
Dim Col As Range
Dim dVal As Double
' Set the variable 'Col' to be Column A of Sheet 2
Set Col = Sheets("Sheet2").Columns("A")
i = 1
' Loop through each cell of the column 'Col' until
' a blank cell is encountered
Do Until IsEmpty(Col.Cells(i))
' Apply arithmetic operations to the value of the current cell
dVal = Col.Cells(i).Value * 2 + 1
' The command below copies the result into Column A
' of the current Active Worksheet - no need to specify
' the Worksheet name as it is the active Worksheet.
Cells(i, 1) = dVal
i = i + 1
Loop
End Sub
Scriviamo una semplice macro VBA che si attiva quando viene aggiornata una cella in un intervallo specifico del nostro foglio. Supponiamo di voler tenere traccia delle modifiche nella colonna B (da B4 a B11) e registrare la data e l’ora della modifica nella colonna A.
Procediamo così:
Developer
clicca su opzione “Visual Basic
” per aprire l’editor VBA. Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
Target.Range("A1:A1").Value = Now
End If
End Sub
Salva la cartella di lavoro con le macro abilitate (ad esempio, come file .xlsm).
A questo punto, ogni volta che aggiorniamo una cella nella colonna B (da riga 1 a riga 10), la cella in colonna A visualizzerà automaticamente la data e l’ora correnti.
Ercole Palmeri
Ny Coveware avy amin'i Veeam dia hanohy hanome tolotra famaliana trangan-javatra an-tserasera. Ny Coveware dia hanolotra fahaiza-manao forensika sy fanavaozana…
Ny fikojakojana mialoha dia manova ny sehatry ny solika sy entona, miaraka amin'ny fomba fiasa vaovao sy mavitrika amin'ny fitantanana orinasa.…
Ny UK CMA dia namoaka fampitandremana momba ny fihetsik'i Big Tech amin'ny tsenan'ny faharanitan-tsaina artifisialy. Ery…
Ny didim-panjakana "Case Green", novolavolain'ny Vondrona Eoropeana mba hanatsarana ny fahombiazan'ny angovo amin'ny trano, dia namarana ny fizotrany lalàna miaraka amin'ny…