If you have a column of cells and modify the first cell, double clicking on the fill handle on the cell you changed will copy the modified cell down the column, to the first empty
9.10 CELL EXAMPLES
9.10.1 Determining What Is In A Cell
There are a number of ways to determine what is in a cell. The following illustrate their use, using active cell. However, any cell reference or object variable set to refer to a cell can also be used in these statements.
Extract the active cell's value Dim V
'Store the active cell's value in a variable V = ActiveCell.Value
'test to see if a value was assigned, quit if not If V ="" Then Exit Sub
Test to see if the active cell is empty If IsEmpty(ActiveCell) Then MsgBox "the cell is empty"
End If
The following tests to see if the value in the active cell is numeric or can be converted to a number. Please note, that the value can be number if is a number entry, or if it is a formula that evaluates to a number. Also an entry such as '004, which begins with a single quote, will also return True in the following test as it can be converted to the number 4.
If IsNumeric(ActiveCell.Value)Then 'code to execute if true
End If
You can always use the worksheet functions, which don't do a conversion to numeric if the entry is actually a string.
Application.IsNumber(ActiveCell.Value) 'only returns true if it is actually a number Application.Istext(ActiveCell.Value)
'returns true if the argument is text.
The following tests to see if the active cell contains a formula. It uses the HasFormula property which returns True of all cells in the range have a formula, False if no cell in the range has a formula, and Null if some cells have formulas.
If ActiveCell.HasFormula Then
'actions to take if the active cell has a formula End If
The following tests to see if the active cell contains an error value, for example #DIV/0! Or
#REF!
If IsError(ActiveCell) Then 'actions to take if an error value End If
The following returns the type entry in the active cell. For example, it returns "String" if a string entry, "Double" if a numeric entry, and "Boolean" if True or False.
MsgBox TypeName(ActiveCell.Value)
9.10.2 Determining Information About A Cell
Sub InfoExample1()
'This example displays the value of the active cell MsgBox ActiveCell.Value
End Sub
Sub InfoExample2()
'This example determines if the active cell is empty If IsEmpty(ActiveCell) Then
MsgBox "The cell is empty"
Else
MsgBox "The cell is not empty"
End If End Sub
Sub InfoExample3()
'this example determines which cells in a selection are numeric 'both a numeric test and an IsEmpty test is needed because the 'numeric test will return TRUE if a cell is empty.
Dim cell As Range
For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then 'actions to do if true
End If Next End Sub
Sub InfoExample4()
'This example returns the text appearance of a cell, exactly as it appears on 'the screen and stores it in a variable called cellText. For example, if the 'actual cell value is 0.123 and the cell is formatted to one decimal, then the 'following would return 0.1
cellText = ActiveCell.Text End Sub
Sub InfoExample5()
'This shows how to determine the row and column number of a cell selected 'by some means. For example by displaying an input box for the user to make 'a selection or by using the Find command.
'code that sets the variable someCell to a cell reference rowNum = someCell.Row
columnNum = someCell.Column End Sub
Sub InfoExample6() Dim someCell As Range
Dim sheetThatContainsTheCellRef As Worksheet Dim workbookThatContainsTheCellRef As Workbook
'This shows how to determine the sheet and workbook of a cell selected by 'some means. For example by displaying an input box for the user to make a 'selection.
'code that sets the variable someCell to a cell reference sheetThatContainsTheCellRef = someCell.Parent
workbookThatContainsTheCellRef = someCell.Parent.Parent End Sub
Sub InfoExample7() Dim someCell As Range
Dim cellFormat As String
Dim cellLeft As Integer, cellTop As Integer
'The following shows how to determine some properties of a cell:
'code that sets the variable someCell to a cell reference If someCell.Locked Then
'code that runs if the cell is protected End If
'this stores the cell's format for later use cellFormat = someCell.NumberFormat
'this stores the cell's position in points. This is useful if one is creating 'buttons or charts on a sheet and want them started at a particular cell 'reference.
cellLeft = someCell.Left cellTop = someCell.Top End Sub
9.10.3 Reading And Writing Cell Values Without Switching Sheets
If you do it something like this it will switch sheets Sub Approach1()
Sheets("Sheet1").Select
AnAmount = ActiveSheet.Cells(1, 1).Value Sheets("Sheet2").Select
ActiveSheet.Cells(1, 1) = AnAmount End Sub
If you do it like this it won't switch sheets, it involves less code, and if you have a lot of such statements, it will run faster.
Sub Approach2()
Sheets("Sheet2").Cells(1, 1).Value = _ Sheets("Sheet1").Cells(1,1).Value End Sub
9.10.4 Determining If A Cell Is Empty And Problems With IsEmpty
The function IsEmpty returns True if a cell is empty and False if is not:
If IsEmpty(ActiveCell) Then
MsgBox "The active cell is empty"
Else
MsgBox "The active cell is not empty"
End If
There are some instances when IsEmpty will not give you the correct results. For example, In cell A1, enter ="". (that's an equal sign and then two double quotes). Then copy and paste special values back into cell A1. The cell is clearly empty. If you run the above code it will tell you that the cell is not empty. An alternate test that overcomes this problem is Len(ActiveCell.Value) = 0:
If Len(ActiveCell.Value) = 0 Then MsgBox "The active cell is empty"
Else
MsgBox "The active cell is NOT empty"
End If
9.10.5 Testing To See If A Cell Is Empty
There are several ways to see if a cell is empty. The following test the active cell. However, you could specify any cell on any worksheet.
If IsEmpty(ActiveCell) Then 'actions to take if the cell is empty End If
If ActiveCell.Value = "" Then 'actions to take if the cell is empty End If
If a cell can contain spaces but this would qualify as an empty cell in your code, you can use the following test:
If Application.Trim(ActiveCell) = "" Then 'actions to take if the cell is empty
End If
If you wanted to test to see if the cell is not empty, then use the Not operator in your If statement:
If Not IsEmpty(ActiveCell) Then 'actions to take if the cell is not empty
End If
9.10.6 Assigning A Value To A Cell
The following code illustrates how to assign a value to a cell, in this case cell A1 of Sheet1 Dim a
'get value of a from your file here A = 5
'put it into cell A1 on Sheet1
Worksheets("Sheet1").Range("A1").Value = a
9.10.7 Using Visual Basic To Extract Data From Cells
In this example, the user has imported data from another computer system, and each cell is an entry like the following
Cell A1:
Smith, Henry (123456)
Where both the name of the individual and the individual's code number is in a cell. The code number begins with a left parentheses, and ends with a right parentheses.
What is desired is the following, which retains the original data and also splits it into the employee code and the name
Cell A1 Cell B1 Cell C1
Smith, Henry (123456) 123456 Smith, Henry
The following is the code that achieves this result. It assumes that the data starts in cell A1 of the worksheet and the first blank defines the end of the data.
Sub Split()
Dim rngCell As Range Dim strName As String Dim OpenParen As Integer Dim CloseParen As Integer
'define a For..Next loop from cell A1 to the last entry in column A
For Each rngCell In Range("A1", Range("A1").End(xlDown))
'store the entry in the rngCell strName = rngCell.Value 'find the position of the parentheses
OpenParen = InStr(1, strName, "(") CloseParen = InStr(1, strName, ")")
'extract the number inside the parentheses and write it to the 'cell to the right
rngCell.Offset(0, 1).Value = Mid(strName, _ OpenParen + 1, CloseParen - OpenParen - 1) 'extract the employee name and write it two cells to the right rngCell.Offset(0, 2).Value = Mid(strName, 1, _ OpenParen - 2)
'loop until done Next rngCell End Sub
9.10.8 Copying Values Without Using PasteSpecial
PasteSpecial is a fairly difficult command to use. Often, the only way to figure it out is to record a macro that uses this command. If all you want is to copy the values in a range, there is a slightly easier way. If you specify code that looks like the following, and the ranges are the same size, then only values will be copied:
destinationCells.Value = SourceCells.Value
The following example illustrates this, and also provides a subroutine that you may want to make part of your library of must have routines:
Sub CopyValuesExample() Dim rangeToCopy As Range Dim destCell As Range On Error Resume Next With Application
'get the input range, exit if no input Set rangeToCopy = .InputBox( _
"Select the range whose values will be copied", _ Default:=Selection.Address, Type:=8)
If rangeToCopy Is Nothing Then Exit Sub
'get the destination cell
Set destCell = .InputBox( _
"Select the destination", Type:=8) If destCell Is Nothing Then Exit Sub End With
On Error GoTo 0
'call the routine that copies the values
CopyCellValues rangeToCopy, destCell End Sub
Sub CopyCellValues(ByVal SourceCells As Range, _ ByVal destCell As Range)
'make certain destination is a single cell Set destCell = destCell.Cells(1)
'resize destination to the same size as the source range With SourceCells
Set destCell = destCell.Resize _ (.Rows.Count, .Columns.Count) End With
'set values to be the same
destCell.Value = SourceCells.Value End Sub
9.10.9 Checking For Division By Zero
The following code illustrates how to check if a cell's value is division by zero:
If IsError(Range("A1").Value) Then
If Range("A1").Value = CVErr(xlErrDiv0) Then MsgBox "#DIV/0! error"
End If End If
Other possible values you can use with the CVErr function are: xlErrNA, xlErrName, xlErrNull, xlErrNum , xlErrRef, xlErrValue
9.10.10 Filling A Range With A Formula
The following procedure fills the cells in column D with a formula that sums the cells in column A, B, and C. For example, the formula in D1 would be =SUM(A1:C1) and the formula in D2 would be =SUM(A2:C2). The actual number of rows vary from use to use.
Sub fillFormula() Dim myRng As Range Dim lastRw As Long
'get the last row with an entry (could have been done from A1 or B1) lastRw = Worksheets("Sheet1").Range("C1").End(xlDown).Row
With Worksheets("Sheet1").Range("D1") .Formula = "=SUM(A1:C1)"
.AutoFill Destination:=Worksheets("Sheet1") _ .Range("D1:D" & lastRw&)
End With End Sub
The following is another approach Sub Approach2()
Dim C As Range
Set c = ActiveSheet.Range("C1") Do While c <> ""
c.Offset(0, 1).FormulaR1C1 = "=Sum(RC[-3]:RC[-1])"
Set c = c.Offset(1, 0) Loop
End Sub
And still another approach:
Sub Approach3()
Range("D1", Cells(Application.CountA( _ ActiveSheet.Columns("C")), "D")) _ .FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
End Sub or
Sub Approach4()
Range("D1", Cells(Application.CountA( _ ActiveSheet.Columns("C")), "D")) _ .Formula = "=SUM(A1:C1)"
End Sub
The different between the third and fourth approach is that Approach4 uses "Formula" and A1 notation instead of "FormulaR1C1 and R1C1 notation
9.10.11 Changing The Value Of Cells In A Range Based On Each Cell's Value
The following example shows how to operated on each of the selected cells in a range using a For..Next loop. It also shows you how to use Select Case instead of If/Then statements.
Sub Chg_all() Dim cell As Range
For Each cell In Selection With cell
Select Case .Value Case 1 To 17
'do this if value between 1 to 17 .Value = .Value + 54
Case 18 To 30
'do this if value between 18 to 30 .Value = .Value + 24
End Select
'note: if the value is between 17 and 18, less than 1 or greater 'than 30, then the value is not changed
End With Next cell
MsgBox "All done!"
End Sub
Note that the With… End With statements are used to avoid typing the word "cell" in front of Value in the above code. Also, there is a period in front of Value to link it back to cell.
9.10.12 Undoing The Last Manual Entry
You can undo the very last manual entry by using the following statement:
Application.Undo
9.10.13 Determining The Number Of Selected Cells
The following procedure displays the number of selected cells. It takes into account that the user could select multiple areas by holding down the control key.
Sub NumberOfCells() Dim number As Long Dim area As Range
For Each area In Selection.Areas number = number + area.Cells.Count
Next
MsgBox number End Sub
If you used the following, then you would get the count of cells only in the first selection. The Count property applies only to the first area in a selection.
Sub NumberOfCells2()
MsgBox Selection.Cells.Count End Sub
9.10.14 How To Determine If A Range Is Empty
One way to determine if a named range on a worksheet is empty would be to use something like the following:
If Application.CountA(Worksheets("Sheet1") _ .Range("NewData")) = 0 Then
MsgBox "The new data section is empty"
Else
MsgBox "There are entries in the new data section."
End If
In the above code, Application.CountA returns the number of cells that have entries.
The following will determine if a named range on the active sheet Is empty:
If IsEmpty(Worksheets("Sheet1").Range("theData")) Then MsgBox "The range is empty"
End If
9.10.15 Determining The Number Of Empty Cells In A Range
The following statement uses CountA to return the number of empty cells from the active cell through the next 199 cells (for a total of 200 cells being checked.
MsgBox Application.CountA(Range(ActiveCell, _ ActiveCell.Offset(199, 0)))
9.10.16 Cell References And Merge Cells
Assume that cells C4 to E7 are merged together. Now, assume you need to refer to the cell 4 cells below the top left cell of the merged cells, which is identified as destCell (for example, destCell for C4:E7 would be C4). The 4th cell below C4 would then be destCell.Offset(4,0) right? Wrong! destCell.Offset(4,0) is C11 to VB! Instead, you have to use destCell.Offset(1,0) to refer to the 4th cell below C4 if C4:C7 is merged. There appears to be no way to refer to cell D8 by use of an offset from C4.
A more logical reference is to use destCell(5,1) to refer to the 4th cell below the first merged cell, which made a little more sense. To refer to D8, use destCell(5,2). Interesting, if one were to use Range("C1").offset(9,0), Visual Basic ignores the merged cells, and returns C10. It appears that the problem only occurs if you use one of the cells in the merged range in an offset reference.
9.10.17 Determining if there are Merged Cells in a Range
The following function will return True if there are merge cells in a range. Just supply it the range to be checked.
Function
bMergedCells(
anyR As Range)
As Boolean Dimsheet_has_merged_cells
As Variant On Error Resume Nextsheet_has_merged_cells = anyR.
MergeCells On Error GoTo 0If IsNull