Hi!

Sub MacroForSue()


Dim NumCols As Integer

Dim MyFactorOne As String

Dim MyFactorTwo As String

NumCols = 0

MyFactorOne = "RC" & "[" & -NumCols - 4 & "]"

MyFactorTwo = "RC" & "[" & -NumCols + 1 & "]"

ActiveCell.Offset(0, (NumCols + 4)).FormulaR1C1 = "" & "=Round((" &
MyFactorOne & "*" & MyFactorTwo & "/" & 100 & "),2)"


End Sub


M Varnendra
"Sue" wrote:

I want to use VBA to write a formula for a cell.
I can establish where the formula needs to be put by knowing the number of
columns.
There are always 6 reference columns plus two data columns per year.
The cursor is at the start of the row.
So in the test data Numcols (an integer variable) is presently 29.
The formulae are wanted in the current row in the last two columns.
The following does put the correct formula in the correct cell.
ActiveCell.Offset(0, (NumCols + 4)).FormulaR1C1 = _
"=Round((RC[-33]*RC[-28]/100),2)"
However I always get an error when I put NumCols in the RC section €“ and I
must have NumCols because the number of columns changes but can always be
calculated.
What I want (but working!) is as below:
ActiveCell.Offset(0, (NumCols + 4)).FormulaR1C1 = _
"=Round((RC[-NumCols-4]*RC[-NumCols+1]/100),2)"
I feel this is probably a syntax issue but please can someone help?