REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

Description:

In the previous post we have seen, how to read data from excel to VBA. We will see how to write data to Worksheet Cell in Excel VBA.

Write Data to Worksheet Cell in Excel VBA – Solution(s):

Delete Worksheet in Excel VBA It is same as reading the data from Excel to VBA. We can use Cell or Range Object to write into a Cell.

Write Data to Worksheet Cell in Excel VBA – An Example of using Cell Object

The following example will show you how to write the data to Worksheet Cell using Cell Object.

Example Codes

In this example I am writing the data to first Cell of the Worksheet.

Sub sbWriteIntoCellData()

Cells(1, 1)="Hello World"
'Here the first value is Row Value and the second one is column value 
'Cells(1, 1) means first row first column
End Sub

In this example I am writing the data to first row and fourth column of the worksheet.

Sub sbWriteIntoCellData1()

Cells(1, 4)="Hello World"

End Sub

Write Data to Worksheet Cell in Excel VBA – An Example of using Range Object

The following example will show you how to write the data into Worksheet Cell or Range using Range Object.

Example Codes

In this example I am reading the data from first Cell of the worksheet.

Sub sbRangeData()

Range("A1")="Hello World"
'Here you have to specify the Cell Name which you want to read - A is the Column and 1 is the Row

End Sub

Write Data to Worksheet Cell in Excel VBA – Specifying the Parent Objects

When you are writing the data using Cell or Range object, it will write the data into Active Sheet. If you want to write the data to another sheet, you have to mention the sheet name while writing the data.

The below example is reading the data from Range A5 of Sheet2:

Sub sbRangeData1()

Sheets("Sheet2").Range("A5")="Hello World"
'Here the left side part is sheets to refer and the right side part is the range to read.

End Sub

In the same way you can mention the workbook name, if you are writing the data to different workbooks.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

29 Comments

  1. Tamil February 4, 2015 at 4:42 PM

    Hi Madam,

    the way of tutor is awesome for Beginners. Thanks a lot

    Edward Tamil

  2. Waris March 27, 2015 at 11:09 AM

    Hi Team,

    I am just starting learning ABC of VBA macro, I hope it is very useful site for such learner like me….

    Keep it…

    Waris

  3. JAVAID IQBAL June 8, 2015 at 4:39 AM

    Hi Madam,
    the way of tutor is awesome for Beginners.

    Thanks a lot

  4. JAVAID IQBAL June 8, 2015 at 4:40 AM

    Hi Madam,

    Awesome for Beginners.

    Thanks a lot

  5. PNRao June 8, 2015 at 9:39 AM

    Very nice feedback! Thanks Javid!
    Thanks-PNRao!

  6. Ekram July 31, 2015 at 2:20 AM

    I have been trying to learn Macro for sometime. And this is the first site that has been very helpful. I would definitely recommend this site for anyone to understand what to command and how. We need more ppl like you. Thank you for being such a awesome person.

  7. Yogesh Kumar August 30, 2015 at 6:53 PM

    Hi,

    I am wondering for a VBA code to read entire data from active excel sheet and print line by line in immediate window.
    I searched a lot but unfortunately I did not get any code. Can you help me ? Thanks in advance…

    Yogi

  8. PNRao August 30, 2015 at 11:31 PM

    Hi Yogesh,
    You can loop through each cell in the used range of a worksheet and print in the immediate window, the below code will check the each cell, and it will print if the value is not blank.

    Sub sbReadEachCellPrintinImmediateWindow()
    For Each cell In ActiveSheet.UsedRange.Cells
        If Trim(cell.Value) <> " Then Debug.Print cell.Value
    Next
    End Sub
    

    Hope this helps!
    Thanks-PNRao!

  9. Yogesh Kumar September 1, 2015 at 12:00 PM

    Hi PNRao,

    Thanks a lot for your help, this code worked. May I know , how to print excel row data line by line ?
    I want to print a row data in one line and next row data should be print in next line in immediate window or Is there any way to print entire excel sheet data in tabular form in immediate window. Please let me know if this is possible. Thank you in advance.

    Thanks
    Yogi

  10. Yogesh Kumar September 2, 2015 at 11:56 AM

    Sub show()

    Dim Arr() As Variant
    Arr = Range(“A1:I12”)
    Dim R As Long
    Dim C As Long
    For R = 1 To UBound(Arr, 1)
    For C = 1 To UBound(Arr, 2)
    Debug.Print Arr(R, C)
    Next C
    Next R

    End Sub

    This code prints a range as column in immediate window. Can any one tell me how to print data line by line ? I want to print one row data in one line and next data from next row should be print in next line in immediate window. Please help. Thanks in advance.

    Yogi

  11. PNRao September 2, 2015 at 11:12 PM

    Hi Yogesh,
    You need a small change in your code, see the below code to print each row in one line. In this example, we are storing all the data in a variable and printing for each record:

    Sub show()
    
    Dim Arr() As Variant
    Arr = Range("A1:I12")
    Dim R As Long
    Dim C As Long
    For R = 1 To UBound(Arr, 1)
    strnewRow = "
    For C = 1 To UBound(Arr, 2)
    strnewRow = strnewRow & " " & Arr(R, C)
    Next C
    Debug.Print strnewRow
    
    Next R
    End Sub
    

    Thanks-PNRao!

  12. Yogesh Kumar September 3, 2015 at 1:31 PM

    Thank you very much PNRao. This code prints data exactly as per my need.
    You are genius ! Hats off to you. Thanks a lot for your help.

  13. PNRao September 3, 2015 at 3:59 PM

    You are most welcome Yogesh! I am glad you found this useful.
    Thanks-PNRao!

  14. Yogesh Kumar September 6, 2015 at 2:30 PM

    Hi

    Sub show()

    Dim Arr() As Variant
    Arr = Range(“A1:I12″)
    Dim R As Long
    Dim C As Long
    For R = 1 To UBound(Arr, 1)
    strnewRow = ”
    For C = 1 To UBound(Arr, 2)
    strnewRow = strnewRow & ” ” & Arr(R, C)
    Next C
    Debug.Print strnewRow

    Next R
    End Sub

    In this code I have to do some modifications that this code can read only even columns. Please help me.
    Thanks in advance.

  15. driqbal October 16, 2015 at 11:31 PM

    Yogesh Kumar September 6, 2015 at 2:30 PM
    Reply
    you need very little modification
    Sub show()

    Dim Arr() As Variant
    Arr = Range(“A1:I12”)
    Dim R As Long
    Dim C As Long
    For R = 1 To UBound(Arr, 1)
    strnewRow = “”
    For C = 2 To UBound(Arr, 2) step 2
    strnewRow = strnewRow & ” ” & Arr(R, C)
    Next C
    Debug.Print strnewRow

    Next R
    End Sub

    This code will read only even columns.

  16. driqbal October 16, 2015 at 11:33 PM

    Sub show()

    Dim Arr() As Variant
    Arr = Range(“A1:I12”)
    Dim R As Long
    Dim C As Long
    For R = 1 To UBound(Arr, 1)
    strnewRow = “”
    For C = 1 To UBound(Arr, 2)
    strnewRow = strnewRow & ” ” & Arr(R, C)
    Next C
    Debug.Print strnewRow

    Next R
    End Sub

  17. Jon November 6, 2015 at 3:26 PM

    I am a V basic user of excel but have created a time sheet at work, i would like to take the information from these sheets completed by multiple people and bring the m together on one sheet is this possible, is it reasonably simple?

    Any help would be great.

  18. PNRao November 7, 2015 at 11:21 AM

    Hi Jon,
    We can use VBA to combine the data from different sheets and workbooks. It can be done in couple of hours based on the requirement. Please let us know if you want us to do this for you.

    Thanks-PNRao!

  19. Abhishek November 9, 2015 at 10:44 AM

    Hi,

    I need one big help . Basically, i have 3 worksheets wherein one is the main template ,other one is mapping sheet and third one is the actual scattered data . Now i have to create a macro and run the same for multiple sheets with multiple formats. So, how we can write the vba code to read the scattered data from the 3rd worksheet and through mapping worksheet we can have the data entered into the main template . As the header available in in main template for eg . description can be different like description-value in the other worksheet having scattered data . So we have to use the mapping sheet to get the correct value .Kindly help

  20. PNRao November 9, 2015 at 3:39 PM

    Hi Abhishek,
    We can use VBA to read the scattered data from the defined worksheet. It will take 2-3 hours based on the requirement. Please let us know if you want us to do this for you.

    Thanks-PNRao!

  21. David January 13, 2016 at 11:45 PM

    I have a set of data A1:L171 that needs to be merged to multiple Excel templates. However, I only need B,F,G,H (Client, Date, Time AM, Time PM). So basically what I am looking for is a mail merge but through Excel. Is this possible?

  22. Surendra March 14, 2016 at 9:23 PM

    I want to create a function which can help me to find if any particular cell has special character or not. can anyone help me out. thanks

  23. Desk Tyrant June 24, 2016 at 11:36 AM

    Hey,

    thanks for the super quick tutorial. This stuff is pretty complicated at times and it’s hard to find solutions on the internet, but this helped me a bit so thanks again.

  24. Claire July 7, 2016 at 9:35 AM

    Hi,
    I am trying to change the output from a macro that I have designed which is a series of combo boxes – currently the output is across the sheet, with each combo box selection being input into a consecutive column. I’d like them to be input into consecutive rows (so the text appears in a single column).
    The current script that I’m using is:

    Private Sub CommandButton1_Click()
    If Me.ComboBox1.Value = “Select” Then
    MsgBox “Please select a wildlife health option”, vbExclamation, “Wildlife Health”
    Me.ComboBox1.SetFocus
    Exit Sub
    End If
    If Me.ComboBox2.Value = “Select” Then
    MsgBox “Please select an option for normal ecology”, vbExclamation, “Normal ecology”
    Me.ComboBox2.SetFocus
    Exit Sub
    End If
    If Me.ComboBox3.Value = “Select” Then
    MsgBox “Please select an option for disease”, vbExclamation, “Disease”
    Me.ComboBox3.SetFocus
    Exit Sub
    End If

    RowCount = Sheets(“Sheet2”).Range(“A1”).CurrentRegion.Rows.Count
    With Sheets(“Sheet2”).Range(“A1”)
    .Offset(RowCount, 1).Value = Me.ComboBox1.Value
    .Offset(RowCount, 2).Value = Me.ComboBox2.Value
    .Offset(RowCount, 3).Value = Me.ComboBox3.Value
    End With
    End Sub

    Any help appreciated.

    Regards,
    Claire

  25. Ratish September 29, 2016 at 7:27 PM

    Hi,

    I am new to excel, My requirement is if I click a button on excel it should update the highlighted cell with the name of the person who clicked and with the time stamp

  26. Man October 5, 2016 at 12:41 PM

    Nice Tips

  27. Ram July 8, 2017 at 3:23 PM

    Hi, Can some one help me on below request.

    i have created excel sheet with data from columns A to E (inputs: column A,B,C,D and output: E)

    how to read output (E column) data if i give input data from column A- D though macros.

  28. PNRao July 17, 2017 at 1:56 PM

    Example:

    Range("E1")=Range("A1")+Range("B1")+Range("C1")+Range("D1")
    If Range("E1")>70 Then
    MsgBox "Good"
    Else
    MsgBox "Try Again"
    End If
    
    

    Hope this helps!

  29. Zach October 19, 2019 at 12:37 AM

    Hello,

    Thanks for the cell tip, I was only aware of using Range(“A1”) = “text” to print to excel. Is there a way to print text to excel starting at a specified cell but not stating every other following text cells location.

Leave A Comment