Martin has attended:
Excel VBA Intro Intermediate course
FormulaR1C1
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
RE: FormulaR1C1
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
RE: FormulaR1C1
Martin
If you want to send the document, email it to
forum AT stl-training.co.uk
Carlos
RE: FormulaR1C1
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
