Use a Variable in a formula

[Sue]

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?

 

[Bob Phillips]

Try

ActiveCell.Offset(0, (NumCols + 4)).FormulaR1C1 = _
"=Round((RC[-" & NumCols & "-4]*RC[-[-" & NumCols &
"+1]/100),2)"

HTH

Bob

 

[Varne]

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]

I am still getting run-time error ‘1004’. Application defined or object
defined error.
I have tried substituting [-29-4] for [-33] and interestingly this also
produces the same error message.
I have checked that NumCols is evaluated as 29. It is and it makes the
correct placement for the formula but the RC section still does not work.
[-33] works but I can not get anything which evaluates to [-33] to work.

Try

ActiveCell.Offset(0, (NumCols + 4)).FormulaR1C1 = _
"=Round((RC[-" & NumCols & "-4]*RC[-[-" & NumCols &
"+1]/100),2)"

HTH

Bob

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?

.

 

[Bob Phillips]

Is it because you your activecell doesn't have 33 columns to the left?

HTH

Bob

I am still getting run-time error '1004'. Application defined or object
defined error.
I have tried substituting [-29-4] for [-33] and interestingly this also
produces the same error message.
I have checked that NumCols is evaluated as 29. It is and it makes the
correct placement for the formula but the RC section still does not work.
[-33] works but I can not get anything which evaluates to [-33] to work.

Try

ActiveCell.Offset(0, (NumCols + 4)).FormulaR1C1 = _
"=Round((RC[-" & NumCols & "-4]*RC[-[-" & NumCols &
"+1]/100),2)"

HTH

Bob

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?

.