Write Data to Worksheet Cell in Excel VBA

Home/Excel VBA/Write Data to Worksheet Cell in Excel VBA

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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.

LIMITED TIME OFFER
By |May 15th, 2013|Excel VBA|28 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

28 Comments

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

    Hi Madam,

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

    Edward Tamil

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

    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 - Reply

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

    Thanks a lot

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

    Hi Madam,

    Awesome for Beginners.

    Thanks a lot

    • PNRao June 8, 2015 at 9:39 AM - Reply

      Very nice feedback! Thanks Javid!
      Thanks-PNRao!

  5. Ekram July 31, 2015 at 2:20 AM - Reply

    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.

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

    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

    • PNRao August 30, 2015 at 11:31 PM - Reply

      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!

  7. Yogesh Kumar September 1, 2015 at 12:00 PM - Reply

    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

  8. Yogesh Kumar September 2, 2015 at 11:56 AM - Reply

    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

    • PNRao September 2, 2015 at 11:12 PM - Reply

      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!

      • Yogesh Kumar September 3, 2015 at 1:31 PM - Reply

        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.

        • PNRao September 3, 2015 at 3:59 PM - Reply

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

          • 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.

        • driqbal October 16, 2015 at 11:33 PM - Reply

          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

  9. driqbal October 16, 2015 at 11:31 PM - Reply

    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.

  10. Jon November 6, 2015 at 3:26 PM - Reply

    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.

    • PNRao November 7, 2015 at 11:21 AM - Reply

      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!

  11. Abhishek November 9, 2015 at 10:44 AM - Reply

    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

    • PNRao November 9, 2015 at 3:39 PM - Reply

      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!

  12. David January 13, 2016 at 11:45 PM - Reply

    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?

  13. Surendra March 14, 2016 at 9:23 PM - Reply

    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

  14. Desk Tyrant June 24, 2016 at 11:36 AM - Reply

    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.

  15. Claire July 7, 2016 at 9:35 AM - Reply

    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

  16. Ratish September 29, 2016 at 7:27 PM - Reply

    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

  17. Man October 5, 2016 at 12:41 PM - Reply

    Nice Tips

  18. Ram July 8, 2017 at 3:23 PM - Reply

    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.

    • PNRao July 17, 2017 at 1:56 PM - Reply

      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!

Leave A Comment


Related pages


select sheet excel vbaexcel vba while loophow to make a dropdown list in excel 2010excel vba advanced tutorialmacro excel tutorialwhat is dml in sqldelete empty cells excelvba hide sheethow to remove blank row in excelexcel vba end subexcel programming tutorialselection interior colorindexexcel function sumifhow to create drop down list in excel 2007hr interview questions indiabixexcel vba application formulacountif in excel 2007excel vba form controlshow to merge all sheets in excel into onedata analysis add in excel 2007excel vba left functionhow to insert combobox in excelvba filedialogunshare excel workbookexcel descending orderconcatenate in vbaproject scope template excelunprotect excel workbook 2013what is the formula for adding a column in excelprotecting excel spreadsheethlook up excelvba get file extensionhlook up excelexcel insert a columnuserform in excel 2007create userformexcel activate vbabypass excel passwordusing arrays in vbaadding password to excelnumber of rows in excel 2007excel vba textboxexcel vba unprotect sheet with passwordexcel vba powerpointexcel interactive calendarusing concatenate in excelapplication screenupdating trueword userform examplesinsert function vbashortcut key in excelsave workbook in excelvba file dialogexcel 2010 merge cellsexcel formula countifshow to unprotect excel workbook 2013vba sql connectionremove blank excel rowsabsolute reference excel shortcutinsert row in excel shortcutbasic interview questions for freshershow to do drop down list in excel 2007workbook excel vbavba range autofilterdelete column vbaadvanced filter in excel 2013where is the pivot table in excel 2010unprotect excel sheetmacro to unlock password protected workbookvba range objectprovider msdasqlunhide columns excelwhile loop in vbapie chart template excelexcel pie chart templatesmicrosoft excel adding columnsexcel worksheet vs workbookhow to delete a sheet in excelexcel vba sendmailexcel vba merge cellssql equality operatorsvba save excel