Writing Values in VBA

Writing a value means placing data into an Excel cell using VBA.

Range("A1").Value = "Hello"

This writes Hello into cell A1.

The basic syntax is:

Range("A1").Value = "Data"

The left side identifies the cell and the right side contains the value that will be written into the cell.

Text should normally be placed inside quotation marks.

Range("A1").Value = "Rahul"

The word Rahul is written into A1.

Numbers do not need quotation marks.

Range("B1").Value = 100

This writes the number 100 into B1.

Range("B2").Value = 85.5

This writes 85.5 into B2.

A date can also be written into a worksheet.

Range("C1").Value = Date

This writes the current system date into C1.

You can also use a Date variable.

Dim admissionDate As Date

admissionDate = Date

Range("C2").Value = admissionDate

The Cells property can also be used to write values.

Cells(1, 1).Value = "Student ID"

This writes Student ID into A1.

Cells(2, 3).Value = 85

This writes 85 into C2.

You can write values into several cells.

Range("A1").Value = "Student ID"
Range("B1").Value = "Student Name"
Range("C1").Value = "Marks"

This creates three headings in the first row.

A variable can be used as the source of the value.

Dim studentName As String

studentName = "Rahul"

Range("A2").Value = studentName

The value stored in studentName is written into A2.

Dim marks As Double

marks = 85

Range("B2").Value = marks

The value 85 is written into B2.

Using variables makes the program more flexible.

You can specify the worksheet when writing data.

Worksheets("Students").Range("A2").Value = "ST101"

This writes ST101 into A2 of the Students worksheet.

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Students")

ws.Range("A2").Value = "ST101"
ws.Range("B2").Value = "Rahul"

A worksheet variable is especially useful in larger VBA programs.

Row and column numbers can also be stored in variables.

Dim rowNumber As Long
Dim columnNumber As Long

rowNumber = 5
columnNumber = 2

Cells(rowNumber, columnNumber).Value = "ADCA"

The value ADCA is written into B5.

Loops are useful when you need to write data into many rows.

Dim i As Long

For i = 1 To 10

    Cells(i, 1).Value = i

Next i

This writes numbers 1 to 10 into column A.

Dim i As Long

For i = 1 To 5

    Cells(i, 1).Value = "Student"

Next i

The word Student is written into A1 through A5.

You can use VBA to write complete student records.

Cells(2, 1).Value = "ST101"
Cells(2, 2).Value = "Rahul"
Cells(2, 3).Value = "ADCA"
Cells(2, 4).Value = 85

This writes a student ID, name, course, and marks into row 2.

VBA can calculate a value and write the result into a cell.

Dim total As Double

total = 80 + 90 + 70

Range("A1").Value = total

The calculated total is written into A1.

You can write an Excel formula using the Formula property.

Range("C2").Formula = "=A2+B2"

Excel places the formula into C2 and calculates the result.

A VBA program can also write a percentage formula.

Range("E2").Formula = "=D2/500*100"

This places a percentage calculation into E2.

A common data-entry requirement is to find the next available row.

Dim nextRow As Long

nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Cells(nextRow, 1).Value = "ST101"
Cells(nextRow, 2).Value = "Rahul"

The data is written into the next empty row in the worksheet.

InputBox can be used to collect information from the user and write it into a cell.

Dim studentName As String

studentName = InputBox("Enter Student Name")

Range("A2").Value = studentName

The entered student name is stored in A2.

Dim studentName As String
Dim course As String

studentName = InputBox("Enter Student Name")
course = InputBox("Enter Course")

Range("A2").Value = studentName
Range("B2").Value = course

This allows a simple interactive data-entry process.

A value can be written based on a condition.

If Range("B2").Value >= 40 Then

    Range("C2").Value = "Pass"

Else

    Range("C2").Value = "Fail"

End If

The result is written into C2.

A loop can be used to write values across columns.

Dim i As Long

For i = 1 To 5

    Cells(1, i).Value = "Column " & i

Next i

This writes Column 1, Column 2, and so on into the first row.

The same VBA program can write data to different worksheets.

Worksheets("Students").Range("A1").Value = "Student Records"

Worksheets("Reports").Range("A1").Value = "Student Report"

Each value is written to a different worksheet.

The following program takes student information and stores it in the next available row.

Sub AddStudent()

    Dim ws As Worksheet
    Dim nextRow As Long
    Dim studentID As String
    Dim studentName As String
    Dim course As String

    Set ws = ThisWorkbook.Worksheets("Students")

    studentID = InputBox("Enter Student ID")
    studentName = InputBox("Enter Student Name")
    course = InputBox("Enter Course")

    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = studentID
    ws.Cells(nextRow, 2).Value = studentName
    ws.Cells(nextRow, 3).Value = course

    MsgBox "Student Added Successfully"

End Sub

• Writing to the wrong cell.
• Using the wrong row or column number.
• Forgetting quotation marks around text.
• Overwriting existing data accidentally.
• Using the wrong worksheet.
• Using incorrect loop limits.
• Writing a formula with incorrect syntax.
• Not checking the next available row before inserting data.

The following example reads marks and writes Pass or Fail into the result column.

Sub GenerateResult()

    Dim i As Long

    For i = 2 To 10

        If Cells(i, 3).Value >= 40 Then

            Cells(i, 4).Value = "Pass"

        Else

            Cells(i, 4).Value = "Fail"

        End If

    Next i

    MsgBox "Result Generated Successfully"

End Sub

Here, column C contains marks and column D receives the result.

• Always identify the correct worksheet.
• Use meaningful variables for important values.
• Check the target cell before overwriting existing data.
• Use Cells when row and column numbers are dynamic.
• Use Range when fixed cell addresses are easier to understand.
• Use Formula when you need to write an Excel formula.
• Use loops for repetitive data entry.
• Validate user input before writing important information.

A typical VBA data-writing process is:

1. Identify the worksheet.
2. Identify the target cell or row.
3. Prepare the value to be written.
4. Use Range or Cells to reference the target.
5. Assign the value using the Value property.
6. Check the result.

Dim ws As Worksheet
Dim nextRow As Long

Set ws = ThisWorkbook.Worksheets("Students")

nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

ws.Cells(nextRow, 1).Value = "ST101"
ws.Cells(nextRow, 2).Value = "Rahul"
ws.Cells(nextRow, 3).Value = "ADCA"

Writing values into Excel cells is one of the most important skills in VBA. It allows programs to automatically create and update worksheet data.

The simplest example is:

Range("A1").Value = "Hello"

Using Cells:

Cells(1, 1).Value = "Hello"

Using a worksheet:

Worksheets("Students").Range("A1").Value = "Student ID"

Using variables:

Dim studentName As String

studentName = "Rahul"

Range("A2").Value = studentName

Using a loop:

Dim i As Long

For i = 2 To 10

    Cells(i, 1).Value = "Student " & i

Next i

Once you understand how to write values, you can create automated data-entry systems, student result systems, reports, invoices, attendance systems, and many other Excel VBA projects.