Solved

# Code required for simple macro

Posted on 2005-05-13
218 Views
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
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"

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"

Matt
0

Author Comment

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

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

Set r = Columns("E")

Matt
0

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

Regards
Dave
0

## Featured Post

### Suggested Solutions

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 (http://www.experts-exchange.com/Q_27402310.html) 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…

#### Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!