VBA Cell Value – Set, Get or Change in Excel Using VBA

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.