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

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