Table of Contents
Introduction/Problem Statement
In this blog, we will learn about how to be Set, Get or Change VBA Cell Value through VBA. First, we will talk about how to set cell values.
Solution:
VBA Cell Values
In order to set a cell’s value, we will use the Value property of range or cells.
Set cell value using cell object
This code will set the range of A4’s value =4. Cell object refer a cell by its row number and column number
Sub KDataScience_SetValueUsingCell()
Worksheets("sheet1").Cells(4, 1).Value = 4
End Sub
Set Cell Value using Range Object
This code will set range A4’s value=4
Sub KDataScience_SetValueUsingRange()
Worksheets("sheet1").Range("A4").Value = 4
End Sub
Set Multiple Cells’ Values at Once
This code will set all A2:D10 cells value to ‘Good’.
Sub KDataScience_SetMultipleRange()
Worksheets("Sheet1").Range("A2:D10").Value = "Good"
End Sub
Set Cell Value – Variable
We can set cell values equal to a variable. This code will set the range A2 value to ‘Test Set Value’.
Sub KDataScience_SetRangeValueByVariable()
'Decalre a variable str as string type
Dim str As String
'To Assign value to variable
str = "Set Range Value"
'To set range A2 value
Worksheets("Sheet1").Range("A2").Value = str
End Sub
Get Cell Value
We can get cell value by using value property.
Get Active Cell Value
This code will show active cell value in a message box.
Sub KDataScience_GetActiveCellValue()
MsgBox ActiveCell.Value
End Sub
Assign Cell Value to Variable
This code will get the cell value of range A2 and assign it to a variable.
Sub KDataScience_AssignCellValueToVariable()
Dim var As Variant
var = Worksheets("sheet1").Range("a2").Value
End Sub
Conclusion:
As we have discussed above various points on how to set, get or change VBA Cell Value. All the points are important and easy that one should be aware of.
