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):
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.
Hi Madam,
the way of tutor is awesome for Beginners. Thanks a lot
Edward Tamil
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
Hi Madam,
the way of tutor is awesome for Beginners.
Thanks a lot
Hi Madam,
Awesome for Beginners.
Thanks a lot
Very nice feedback! Thanks Javid!
Thanks-PNRao!
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.
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
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.
Hope this helps!
Thanks-PNRao!
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
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
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:
Thanks-PNRao!
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.
You are most welcome Yogesh! I am glad you found this useful.
Thanks-PNRao!
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.
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.
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
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.
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!
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
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!
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?
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
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.
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
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
Nice Tips
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.
Example:
Hope this helps!
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.