How To Reference A Cell In Excel
The Range Method:
This method allows you to refer to a Cell in VBA by using its address. I.e. Range (“A1”) This is simple and useful in many situations.
The Cells Method: This method allows you to select a Cell by defining its numeric coordinates (row, column). I.e. Cells (1, 1) would refer to the cell in the first row of the first column, or range A1 In the following example we will use cell reference (by both the range method and cells method) to enter text in the cells.
Follow these steps:
Right click anywhere on the ribbon and select “Customize the Ribbon” from the drop down menu.
An option dialog box will open. Click on the DEVELOPER check box (it is under “Customize the Ribbon Main Tabs”) and press ok.
The developer tap is now visible and is present next to the view tab on the top menu bar. Click on Developer tab and select “View Code”
A new window (Visual Basic Editor) will open which would have a dialog box in the center. You will write the code in the dialog box.
Write the following line of code in the dialog box.
Sub test () Range ("A1") = "HELLO" Cells (2, 2) = "HOW" Range ("C3") = "ARE" Cells (4, 4) = "YOU" Range ("E5") = "DOING" Cells (6, 6) = "?" Range ("B12").Select End Sub
After writing the code close the window by clicking on the cross(x) icon on the upper right side of the screen. Don’t worry. Excel won’t close.
Explanation: The code enter text into the cells using both the range method and the cells method. The second line of code (Range (“A1”) =”Hello) causes the text “HELLO” to be entered in cell A1 using the range method. The third line of code enters the text “HOW” in cell B2 using the cells method.
This will be the result. That’s it you have now successfully refer to a cell using VBA