Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft VBA Training and help » vba training london - FormulaR1C1
|
|
|
Bookmark this page|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
vba training london - FormulaR1C1
Resolved · Low Priority · Version Standard
Reply
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
For upcoming training course dates see: Pricing & availability
RE: FormulaR1C1
MartinSorry 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
MartinIf you want to send the document, email it to
forum AT microsofttraining.net
Carlos
RE: FormulaR1C1
MartinIts 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
|
 |
|
» Forum post: Macros |
How do Macro's work
