I am trying to reference a named range in a formulaR1C1 but receive an error each time. the code is as follows:

Sub Percentages()

Dim Total As Range
Dim Counter As Integer
Dim Mycell As Range

Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("A2:F2").Select
Range("F2").Activate
Selection.Interior.ColorIndex = xlNone
Range("F2").Select


Set Total = ActiveCell
Set Mycell = ActiveCell.Offset(0, 1)

Counter = 2
Cells(Counter, 6).Activate

Do
If Cells(Counter, 6) = "" Then
Total = ActiveCell
ActiveCell.Offset(0, 1) = Mycell
Mycell.Activate

ActiveCell.FormulaR1C1 = "=sum(RC[-1]/"& Total &")"
Else
ActiveCell.Offset(0, 1) = Mycell
Mycell.Activate
ActiveCell.FormulaR1C1 = "=sum(RC[-1]/"& Total &")"
Cells(Counter, 6).Activate
Counter = Counter + 1
End If

Loop Until Cells(Counter, 6) = "end"


End Sub

The purpose of the formula is to calculate a percentage of a total that will go into the blank cell, and there will be lots of totals at irregular intervals.

Any help greatly appreciated.

Many thanks
Martin

Martin

Sorry for the delay.

I looked at your code and tried to run it on a Worksheet I set up.

In my test the system is loading a blank value into the Total variable which when trying to do the Formula is triggering an error.

I went through it using F8 and by moving row 2 down you are looking at a blank "F2" cell qand loading its value into Total.

Apart from the above, I would need to see this working on the original workbook to see where else it is going wrong.

Carlos

Martin

If you want to send the document, email it to

forum AT microsofttraining.net

Carlos

Martin

Its as I said before. The way you have the code presently you are moving the data down One line and then loading the value of a blank cell into Total which means you are dividing by it in the equation.

Run the code through using F8 (Step through) and keep flicking back tgo the Spreadsheet and watch how the data behaves.

If the moving down of the data is required then you need to rewrite the code so that the computer picks the value from any other cell except "F2" or cells (Counter, 6).

Carlos