March 1, 2014

Access Excel Worksheet Cells Using VBA

How to Access Excel Worksheet Cells with a Macro Using the Range Property
Before you can start performing calculations with your Excel data, you obviously have to enter data into the worksheet. Now if you wish to enter data with the help of a macro you need to know how to access a worksheet cell like A1 or a range of cells like A1:C1. The ‘range’ property helps you to access a single cell or a range of cells. Later you can define whether a cell can contain text, number, currency, formulas or functions.

  • Click on the ‘Developer’ Tab
  • From the ‘Code’ group select Visual Basic
  • The Microsoft Visual Basic window pops up
  • Click on ‘Insert’ and select ‘Module’.
  • In the white workspace area type ‘Sub macroname()’. Select a macro-name that is short and easy to remember and describes well what the macro will do
  • Press enter and you will notice that the application automatically adds ‘End Sub’
  • Between the ‘Sub macroname ()’ and the ‘End Sub’ you will write the code that will execute what the macro is supposed to do

We enter the following code for example:
Sub range_propert_to_access_worksheet_cells()
Range(“A3”) = “Name”
Range(“B3”) = “Salary”
End Sub

When we click on the run arrow-like button (>) the macro executes the code and after minimizing the MS-Visual Basic Window we can observe that the text ‘Name’ and ‘Salary’ have been placed in the appropriate worksheet cells ‘A1’ and ‘B1’.
Let’s have a look at the MS Excel VBA training video.

17 thoughts on “Access Excel Worksheet Cells Using VBA

  1. sir i have one question i want pull data from i.e into excel through filter table date enter in excel sheet and run is it possible to do such task in excel vba….

    1. dit :Le partie Nazi c’est présenter comme un partie de travailleur, naasnotl-socialiite, amis des syndicats et tous.Mais après qu’il ai pris le pouvoir il a organisé une fête avec les syndicats pour les remercier et en a profiter pour tous les mettre en prison.Comme les communistes…

    2. This is what “ING” has come to?!…. Flame is freaking right now. If that isn’t the epitome of Hipster, beanie, skinny jean wearing fashionistas. Where was Kolohe? Has “ING” hung its hat on Dion or what? Some good photos by Jimmy Wilson but this has the look of Chas Smith all over it. Is GLOBE still in business and what do they sell? Shoes and retro-70′s skateboards in 80′s neon colors. This looks like a really mixed message.

  2. How can I enable macro for a excel sheet, because I’ve created a macro, to click run, it displays a message box indication for enable to macro for this excel sheet.

  3. I am a professor of finance. I am Chartered Fin. Analyst as well as FInancial Risk Manager.

    I want to purchase the DVDs of your videos for VBA and excel.

    Can you give me your mobile number?

  4. dear sirg,
    pls. fully excel vba billing softwore tutorial in hindi
    send by me link side

  5. Dear Sir,
    First of all thank you so much for your patience to describe so easily. You are a great person to help with such great effort. I am a guy with willing and keen to learn so i keep searching on internet. Infact i saw you video, that solved most of my search. I have a query kindly explain how to solve it.

    I want to create a form which will save data in sheet 2 but macro button should display in sheet 1. If i click on macro button it should open in sheet 1 and after filling data it should go to sheet 2 worksheet.
    Though i have created the form which is easy to open in sheet 2, however whenever i want to connect with sheet 1 not able to do so.
    please guide me….

    Thank you

    Vikash ROy

    1. Felices vacaciones Fede!!! A preparase muy bien para el 20332#8&10;. Urgente un psicólogo, un buen preparador físico (empezó muy lento físicamente el 2012, y lo terminó de la misma manera), un buen entrenador, un buen programa de partidos.

  6. Dear Sir,

    I have create excel sheet for Computer Management. My first sheet is Computer course content and second sheet is student data. so i have entered data in sheet second then it show me a simple design form so how can make. so please help me..

    thanks & regards,

    1. En los términos más llanos: es imposible una literatura que sin la postura política que nombra Lola (cómo ella misma dice) siga explorando todas sus pobsiilidades. Ignorar dicha postura, que preferiría llamar perspectiva, equivale a negarnos parte de esa literatura; en términos visuales, seria negarnos a ver la pintura completa, quedarnos en un solo nivel.Para que existan escritores ambiciosos, como Panero (ambicioso en el mejor sentido) también deben existir lectores de esa misma naturaleza.

  7. Dear Sir

    When I’am Close my Worksheet and open later that worksheet, vb codes was not there
    Please Help me to do …
    ms office excel 2010
    windows 7

    1. Hi Sanjaya,

  8. This is a good fucntion

    Option Explicit
    Sub CopyNTimesFinal()

    Dim Z As Integer
    Dim LastRow As Integer

    Dim Y As Integer
    Dim M As Integer
    Dim D As Integer
    Dim A As Double


    LastRow = Cells(Rows.Count, 6).End(xlUp).Row
    Range(“A” & LastRow).Select

    Z = ActiveCell.Offset(0, 5).Value – 1

    If Z = 0 Then
    Exit Sub
    End If

    Y = Year(ActiveCell.Offset(0, 3).Value)
    M = Month(ActiveCell.Offset(0, 3).Value)
    D = Day(ActiveCell.Offset(0, 3).Value)

    With ActiveCell
    .Offset(0, 7).Value = DateSerial(Y + 1, M, D – 1)
    End With

    Do While ActiveCell.Offset(0, 5).Value > 1
    Y = Y + 1
    ActiveCell.Offset(1, 0).Select
    Range(“A” & ActiveCell.Row – 1 & “:G” & ActiveCell.Row).FillDown
    A = ActiveCell.Offset(0, 4).Value

    With ActiveCell
    .Offset(0, 3).Value = DateSerial(Y, M, D)
    .Offset(0, 4).Value = 0 ‘Int(Round(A * 1.02, 2))
    .Offset(0, 5).Value = Z
    .Offset(0, 7).Value = DateSerial(Y + 1, M, D – 1)
    End With
    Z = Z – 1

    End Sub

Comments are closed.