asked on

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

ASKER CERTIFIED SOLUTION

mvidas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Somtech

ASKER

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

mvidas

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

Somtech

ASKER

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