Writing and Reading Excel Worksheet Cells Ranges in VBA

Home/Excel VBA/Writing and Reading Excel Worksheet Cells Ranges in VBA

Description:

While working with Excel it is common task to writing and reading the Workrksheet. For example, we may have a Input data sheet to enter data, based on the data in Input sheet we can perform some calculations and show the results to the user.

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


Writing and Reading Excel Worksheet Cells Ranges in VBA – Solution(s):

Delete Worksheet in Excel VBA We can use Cell or Range Object as discussed in the previous topics to read and write the using VBA. The following example will show you how write and read the data using Excel VBA.

Example1 : Reading and Writing the data using Cell Object

In this example I am reading the data from Range B3 and Writing the data into C5 using Cell Object.

Sub sbReadWriteCellExample1()
'Using Cell Object
Cells(5, 3) = Cells(3, 2)
MsgBox Cells(5, 3)
End Sub
Example2 : Reading and Writing the data using Range Object

In this example I am reading the data from Range B3 and Writing the data into C5 using Range Object.

Sub sbReadWriteCellExample2()
'Using Range Object
Range("C5") = Range("B3")
MsgBox Range("C5")
End Sub
Example3 : Reading and Writing the data using Cell and Range Object

Even we can use combination of Cell and Range. In this example I am reading the data from Range B3 and Writing the data into C5 using Cell and Range Object.

Sub sbReadWriteCellExample3()
'Using Cell and Range Object
Cells(5,3) = Range("B3")
MsgBox Cells(5,3)
'OR
'Range("C5")=Cells(3,2) 
'MsgBox Range("C5")
End Sub
Example4 : Reading and Writing the data – From different Worksheets

We can mention the Sheet name while reading the data from another sheet.

Sub sbReadWriteCellExample3()
'Using Cell and Range Object
Cells(5,3) = Sheets("Sheet5").Range("B3")
MsgBox Cells(5,3)
End Sub
Example4 : Reading and Writing the data – Write to different Worksheets

We can mention the Sheet name while Writing the data to another sheet.

Sub sbReadWriteCellExample3()
'Using Cell and Range Object
Sheets("Sheet5").Cells(5,3) = Range("B3")
MsgBox Sheets("Sheet5").Cells(5,3)
End Sub

Similarly, we can use the workbook names, if you want to write into different workbooks.

LIMITED TIME OFFER
By |February 9th, 2013|Excel VBA|6 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.

6 Comments

  1. Paudel October 19, 2014 at 1:08 PM - Reply

    helloo i am new to vba
    how can i pick numbers form certain cell and add text to it. eg i have 2014 5 6 in different cells i want vba programme to select each cells and write in another one cell 2014 year 5 month 6 days

    • PNRao October 20, 2014 at 6:43 PM - Reply

      Hi Paudel,
      Assuming that you have data at Range A1(2014), B1(5), C1(6). You can combine and print this into D1 as follows:

      Range(“D1”)=Range(“A1″)&”year ” &Range(“B1″) &”month ” &Range(“B1″) &”days”

      Hope this helps!
      Thanks-PNRao!

  2. Patti November 20, 2014 at 9:24 PM - Reply

    I am trying to remove the first 4 characters from a list of file names. Can someone help me create a code to move down a list removing the first 4 characters from the data in each cell? Recording my actions did not give me the results I was hoping for.

    • Jimmy December 19, 2014 at 3:44 AM - Reply

      Just use right(“A1”,len(“A1”)-4) Assuming it’s for A1, for the sake of this exercise.

  3. Ravichandran Rajagopal January 23, 2016 at 8:19 AM - Reply

    Help assistance required.
    I have two sheets.
    In Sheet 1, A1 I have todays date.
    In sheet 2, from A1 to A30 I have the dates of the month. B1…. Z30 I have various other data.

    I want to write a macro so that A32….. Z30 gets filled with data pertaining to the date that is in Sheet 1, A1. Kindly help.

    • Sandeep November 1, 2016 at 4:07 PM - Reply

      This is a simple task that can be done with simply using VLOOKUP(). Why would you want to have a macro for this when you can simply get it done by using a single formula?

Leave A Comment


Related pages


vba yes no boxhow to filter duplicate rows in excelvbscript fileexistsaccess vba run macrosave vba excelsas programming tutorial for beginnersneat excel tricksoffset vba excelexcel vba macroinsert row macro excelexcel programming for beginnershlookup in excel examplehow to do sumif in excelexcel dedupproject tracking excel spreadsheetpie chart template excelvlookup 2 conditionsquery excel with sqladd developer tab excel 2013excel tutorial examplesexcel vba rename filecase function in vbawhat is hlookup in excel with exampleprotect a worksheetexcel template dashboardnested if statements in excelsas coding basicswhere is advanced filter in excel 2010how to make drop down list in excel 2007visual basic comboboxvba write functionproject estimation template excelvba excel to wordexcel vba examplevba formsms excel shortcut keysvba excel examplehow to add option button in excelhow to create dashboard in excel 2007unprotect excel worksheetexcel with vba programmingvlookup function exampleexcel vba left functionexcel remove empty cellscreate bar chart in excel 2010sql basic questions and answersconnection string vbadbms interview questions and answers pdf free downloadadvance excel tipsms excel timeline templateenter vbaaverage function excel 2007sas beginnersvba columnlookup command in excelexcel 2007 name managervba insert blank rowcreate new workbook vbahow to use count if function in excelyes no box vbainsert worksheetremove duplicates in excel 2007vba to open excel file from folderexample of hlookupexample of a vlookup formulavba range selectionvba application inputboxexcel cell rangesvba join arrayvba loop statementmacros basicsexcel if isblank thenexcel vba templatearia charts 2010excel macro close excelworking with macros in excel 2007definition of vlookup in excel 2007excel averageifexcel vba save as pdf