• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Code required for simple macro

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.

Regards

Dave
0
Somtech
Asked:
Somtech
  • 2
  • 2
1 Solution
 
mvidasCommented:
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"

BUT
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!
Matt
0
 
SomtechAuthor Commented:
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

Thanks
Dave
0
 
mvidasCommented:
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!
Matt
0
 
SomtechAuthor Commented:
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

Regards
Dave
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now