Entering Data


Entering data on the active worksheet

If you want to enter data onto the active worksheet then you can use any of the following.

ActiveSheet.Range("D3").Value = "some text" 
ActiveSheet.Cells(3,4).Value = 200
Range("D3").Value = "some text"
Range("A2").Value = Range("B4").Value + 20
Cells(7,2).Value = 25

This enters the number 60 into all the cells in the range "A1:B10".

Range("A1:B10").Value = 60 

This enters the number 18 into the four cells "A1", "B1", "C1" and "D1".

Range("A1,B1,C1,D1").Value = 18 

This enters the number 5 in all the cells that are the intersection of the two ranges.

Range("A1:B10 A1:D10").Value = 5 

Entering data on a different worksheet

It is good practice to always qualify the worksheet, whether it is the active worksheet or a different worksheet.

Worksheets("Sheet1").Range("A2").Value = 20 
Sheets("Sheet1").Range("B2").Value = 30
Sheets(2).Cells(3,3).Text = "some text"

Remember the difference between "Worksheets" and the "Sheets" collection.
The "Sheets" collection includes all sheets, including Chart sheets.


ActiveCell

The ActiveCell is always a single cell and is the cell that contains the cell pointer.
This property always refers to the active worksheet or window.
If you select several cells on a worksheet, then these cells are condisered a selection. One of these cells is the active cell.
A cell does not have to be active in order for it to be edited.
Trying to obtain a reference to the ActiveCell will fail if a Chart Sheet is currently active.

Dim objCell As Range 

Set objCell = Application.ActiveCell
Set objCell = ActiveWindow.ActiveCell
Set objCell = ActiveCell

If a range of cells is selected, the active cell will be in one of the corners and will depend on how the range was selected.
ActiveCell returns Nothing if no worksheet is displayed in the active window.


Selection

The Selection property will references whatever is currently selected in the active workbook.
This could be a Range, Shape, Chart, anything

Application.Selection 
Selection

RangeSelection

This refers to the selected cells on the worksheet in the specified window, even when a graphic object is selected.
This property applies to a Window object.

ActiveWindow.RangeSelection 

This can be useful when you want to refer to the range that was selected before a non-range object was selected.


Using the Cells collection

A common way to refer to particular cells on a worksheet is to use the Cells property.
This enters the number 10 into cell "B2" on the active worksheet.

ActiveSheet.Cells(rowindex, columnindex).value = 10 
ActiveSheet.Cells(2, 2).Value = 10

You can also use the Cells property to refer to a particular cell on a worksheet or in a range
Cells are numbered starting at the left and continuing to the right and then down to the next row.

ActiveSheet.Cells(500).Value = 10 

Removing Data

Using the ClearContents method, removes just the value from a cell and not the cell formatting.

Sheets("Sheet1").Cells.ClearContents 
Range("C1:E6").ClearContents


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext