You probably already know that the VBA (Visual Basic for Application) is one of the greatest tools at our disposal for automating and manipulating our data.
It can be used for multiple purposes and for various reasons. In the example below, we will show how can we use VBA to input a text in a certain cell, and how to change the values in these cells.
Input Text in Cell Using Vba
There are different ways to input text in a cell with VBA. The first thing we need to do is to open the VBA. To do that, we will press a combination of ALT + F11 on our keyboard and then we will right-click anywhere on the left side of our window, and choose Insert >> Module:
When we click on it, a window will appear on the right side, on which we can insert our code. We will use the following code to populate range A2:A5:
|
1 2 3 4 5 6 7 8 9 |
Sub InsertTextInCells() Dim c As String Range("A2").Value = "First cell" Cells(3, 1).Value = "Second cell" c = "Third cell" Range("A4").Value = c Range("A5").Select ActiveCell.FormulaR1C1 = "Fourth cell" End Sub |
This is what our code looks like in the module:
We only have one variable- that is variable c, and its type is String because we want to put in some text.
The first way that we used for writing the text in our cells was that we used Range, and we assigned a value to the cell A2:
|
1 |
Range("A2").Value = "First cell" |
The second way is to use the Cells option, which has two parameters (in brackets): row number and column number:
|
1 |
Cells(3, 1).Value = "Second cell" |
For the third option, we used our variable c. We set the value of this cell to be „Third cell“. After that, we used Range to assign this value to a certain cell:
|
1 2 |
c = "Third cell" Range("A4").Value = c |
The final option that we used is the one for which we positioned ourselves in cell A5, and then we inserted the text using the R1C1 option.
|
1 2 |
Range("A5").Select ActiveCell.FormulaR1C1 = "Fourth cell" |
When we execute the code by pressing F5 while in the module, this is what we end up with:
Change Text in Cell Using VBA
The same tricks that were used to insert values in cells above, can be used again to change the values. But for convenience, there is another formula that can be used to replace the text in cells.
We will insert the following formula in our module:
|
1 2 3 4 5 6 7 |
Sub ChangeTextInCells() Dim OriginalValue As String Dim NewValue As String OriginalValue = Range("A4").Value NewValue = Replace(OriginalValue, "cell", "value") Range("A4").Value = NewValue End Sub |
This is what it looks like in the module:
In this code, we first declare two variables: OriginalValue and NewValue. We set OriginalValue to be equal to the text in cell A4, which is “Third cell”. Then we use Replace formula to set the new value. It has three parameters: Expression (as String), Find (as String), and Replace (as String):
|
1 |
NewValue = Replace(OriginalValue, "cell", "value") |
And in the module:
There are also other parameters that we could set, but they are all optional.
Now we have our new value defined, which is the “Third value”, and we need to insert it somewhere. We will put it in cell A4:
|
1 |
Range("A4").Value = NewValue |
When we execute the code by pressing F5, the value in cell A4 will be changed:
