Code required for simple macro

Posted on 2005-05-13
Last Modified: 2008-02-01
I am trying to write a simple macro that will select a column in a spread sheet and insert a sum from row2 untill the end of the data.  The data is a spread sheet that is provided by someone else and varies in the number of rows.

My code looks like this
 I need to select column E

Set r = Range("E")
        For n = 2 To r.Rows.Count
            ActiveCell.FormulaR1C1(n,5) = "=SUM(RC[1]*1.2)*100"
        Next n

When I run this I get an error 1004 Method Range of Object _Global failed.

I am not a computer programmer so please keep your answers simple. I would guess from all the help files I have read I am missing something but was unable to understand what.


Question by:Somtech
    LVL 35

    Accepted Solution

    Hi Dave,

    For not being a programmer, I'm glad to see you getting into VBA!  Takes a little bit to get the hang of it, but once you do you'll be glad you did.

    The specific error you're getting is because you have       Range("E")       , and you would need to either use      Columns("E")    or     Range("E:E")      .. but there will be more errors later so I will go over a quick explanation of what else you could do.

    Another error you would get is the (n,5) after FormulaR1C1.  To get it to work the way you have it, you would change the line in your loop to:
                Cells(n, 5).FormulaR1C1 = "=SUM(RC[1]*1.2)*100"

    For something like this you do not need to loop through the rows, luckily (it takes a long time to run, and isn't necessary here).  Besides, your loop is entering that formula into the entire column E, from E2:E65536, something you shouldn't have to do!

    The .FormulaR1C1 can work on an entire range of cells, so if you wanted your formula to be entered into E2:E100, you would use:

     Range("E2:E100").FormulaR1C1 = "=SUM(RC[1]*1.2)*100"

    However, you only want to use that formula on the rows that contain data.  Using the following snippet of code will do just that.  I've heavily commented this so you can understand how it works.

     Dim r As Range
     Set r = Range("E") 'Set the column to enter the formula for
     'Sets the 'r' variable to be only the used cells in that column starting in row 2.
     ' The Intersect method takes different ranges, and returns only the areas that overlap.
     ' This intersect statement is intersecting 3 things:
     '  1) The entire column defined above
     '  2) The used portion of the active tab
     '  3) Every row but the first row
     ' Intersecting only the Column and the Usedrange is a good thing to do usually, but since
     '  you do not want your formula in row 1, we add the 2:65536 part to filter out row 1
     Set r = Intersect(r, ActiveSheet.UsedRange, Rows("2:65536"))
     ' After we re-define the 'r' variable, we compare it to the keyword 'Nothing', which
     '  would return a TRUE value if there was no intersection.  In that case, the macro exits
     If r Is Nothing Then Exit Sub
     'Apply the formula to the newly defined range
     r.FormulaR1C1 = "=SUM(RC[1]*1.2)*100"

    Of course if you have ANY questions about this, don't hesitate to ask!

    Author Comment

    Thanks for your reply Matt

    I think I have understood correctly and redone my code as follows

    Sub sumforetest()
     Dim r As Range
     Set r = Range("E")
     Set r = Intersect(r, ActiveSheet.UsedRange, Rows("2:65536"))
     If r Is Nothing Then Exit Sub
     r.FormulaR1C1 = "=SUM(RC[1]*1.2)*100"

    End Sub

    The problem is I still get an error 1004 Method Range of Object _Global failed. That breaks at Set r = Range("E").
    Damn frustrating this computer programming

    LVL 35

    Expert Comment

    Hi Dave,

    I'm very sorry for carrying over your error like that!  Especially after my second line above says:

    The specific error you're getting is because you have       Range("E")       , and you would need to either use      Columns("E")    or     Range("E:E")      

    I'm very sorry about that! Use this instead at that line:
     Set r = Columns("E")

    Again, sorry about that!

    Author Comment

    Hi Matt

    Thanks very much for your assistance that has worked a treat.  I will close this question now and award the points thanks.

    If possible could I pick your brains further.  The spreadaheet that I have been working on contains a column called acccode which contains a code ( or shoul I say 1 of 150 differnt codes ) these relate to a top level category a second level category and a base level category.  ie

    Acccode = sysacc top level= Systems, second level = Computers, Base Level = Accounts Software

    I have started to create a piece of code to
    1. Create three blank columns to the left of sysacc column
    2. Name each column
    3. Search colum J for sysacc and fill out  g h i with the correct text

    Is there an easy way to do this, because they all look difficult to me.  I have investigated While loops and for loops and have considered using a case statement

    Please tell me what would be your preference and i will at least have a starting point


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    create a utf8 text file using vba 2 74
    DIR issue 7 38
    Windows 10 start screen issues 9 39
    Saving history changes to sub form 4 17
    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now