Estimated reading time: 3 minutes
The following Sub procedure reads values from cells in column A of the active worksheet, until it encounters a blank cell. The values are stored in an array. This simple Excel macro example illustrates the use of:
Do Until
;Ubound
builtin (which returns the highest index of an 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
The procedure stores the values in column A of the active worksheet in an array, note that:
Do Until
extracts the values of each cell in column A of the active worksheet, ignoring blank cellsIf UBound(dCellValues) < iRow
” checks that the dCellValues array is large enough to hold the information, if not, use ReDim to increase the size of the array by 10dCellValues(iRow) = Cells(iRow, 1).Value
” Stores the current cell in the CellValues arrayThe following Sub procedure reads the values from column A of the worksheet named “Sheet2” and performs arithmetic operations on the values. The resulting values are printed in column A of the current active worksheet.
This macro illustrates:
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
Let's write a simple VBA macro that fires when a cell in a specific range of our sheet is updated. Suppose you want to track changes in column B (B4 to B11) and record the date and time of the change in column A.
Let's proceed like this:
Developer
click on option “Visual Basic
” to open the VBA editor. 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
Save the workbook with macros enabled (for example, as an .xlsm file).
Now, every time we update a cell in column B (from row 1 to row 10), the cell in column A will automatically display the current date and time.
Ercole Palmeri
Coveware by Veeam will continue to provide cyber extortion incident response services. Coveware will offer forensics and remediation capabilities…
Predictive maintenance is revolutionizing the oil & gas sector, with an innovative and proactive approach to plant management.…
The UK CMA has issued a warning about Big Tech's behavior in the artificial intelligence market. There…
The "Green Houses" Decree, formulated by the European Union to enhance the energy efficiency of buildings, has concluded its legislative process with…