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.

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:

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

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

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!

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").FormulaR1

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